Record appears in report that isn't in the query results!? 1

[RobTsintas]

Can anyone think of any reason why a report, which is based on a query, would display a record which does not appear in the query's results? It's got me really confused.

Can supply further info if it's needed, but thought I'd start it off as a general question.

Thanks

 

[JoeMiller]

Supply further info, I've never heard of anything like this... Joe Miller
joe.miller@flotech.net

 

[RobTsintas]

Okay, but this isn't going to be short. I have no idea what's causing the problem, so I'll try to include as much information as possible:

The database is a collection of jobs that we send to our Information Services for various changes to be made. Each job has a set of status codes (ie. For Review, Work in Progress, Closed) stored in a separate table, along with the date the status was applied. Each status type has properties stored in yet another table (such as the Name of the status as above and a closed indicator, which is only "Yes" for the "Closed" status, and "No" for all the others).

That's the background. The report which has the problem should list all the jobs which are open. It is based on a query (part 2 by category), which is based on another query (part 1). The Part 1 query selects the required information from the various linked tables and has the criteria that the closed indicator is "No" ('Total' row in design grid="Last", so that job's previous statuses aren't included). The Part 2 query selects all the data from Part 1, and also limits the output to certain categories. It also has another design grid column selecting the closed indicator output from part 1, with another Criteria of "No" (I don't know why it has been done like this, since I didn't write any of it. I've just been asked to work out why it's not working).

None of this should be relevant to the problem, however, since both of these queries output only jobs whose latest status is not "Closed". I am told that when the report was first written, it worked correctly, but when it has been opened subsequently, jobs appear which are Closed and definately do not appear in either of the query results. If the values of Status and Closed Indicator are added to the report, they show as 1 (For Review) and No respectively. The job has had this status in the past (as have all the other closed jobs which are not appearing), although it appears that the Closed status for all these phantom jobs had been added quite recently. But it is the fact that the jobs do not appear on the query results but are in the report that is confusing. I have checked that the report uses the correct query for it's data source.

Unfortunately, the Database is over 6MB and probably contains some sensitive data anyway, so I can't upload it for anyone to examine.

If you have managed to read and follow all this, I congratulate and thank you, any possible suggestions of reasons for this would just be a bonus!

 

[JoeMiller]

I understand what's going on, but without seeing it I don't think I'll be able to help, is there a way you can send a copy of the db without data, or with minimal data so I could check it out? I don't need extra forms or reports, just the tables, queries, and reports in question.

Joe Miller
joe.miller@flotech.net

 

[VogonPoet]

This is a statement of the obvious, but are you certain the report is running the query you think it is running? Does the report actually reference a query?

 

[RobTsintas]

To VogonPoet: Well if whatever it says in the report's Record Source property is right, then yes, I'm absolutely sure

To JoeMiller: I was trying to reduce the filesize and make sure there was no sensitive data in the database, when I thought I'd stumbled upon the solution. I removed the surplus reports and queries and as I was reducing some of the field sizes in the main ("Gaps&quot table, I realised the file wasn't getting any smaller. I then remembered that it needs to be compacted, and after doing this, presto!, the particular job I'd been looking at which shouldn't have been in the report had gone! Then on closer inspection, I saw that there were several others which had appeared which were also 'Closed'!

Anyway, I got the total database size down to 2MB, so if this isn't too big for you to download, you can pick it up from the Public Folder of my Yahoo Briefcase at

http://uk.briefcase.yahoo.com/qwerty_uk

(if it's still too big, let me know and I'll see if I can make it any smaller).

Before you make any changes follow these instructions to see the problem: (by the way, I'm using Access 97, so I don't know if it'll be the same if you're using 2000)

The report in question is "Open Gaps (by Category) (brief) 170801". Open this first, and note the Gap numbers 1392 and 1382 on the first page (there are others on other pages too). Now if you look in the query on which the report is based, "Rpt: Open Gaps (by Category) (Pt 2)" (order the Gap_ref column to be safe), you will see that these numbers do not appear. Also, if you open the form, "Gaps form", and find the records with these numbers in the Gap ref field (alternatively, use the underlying tables, "Gaps" and "Gap Status records&quot, you can see that the latest status of these gaps is "CLOSED" (code 7).

Any (even minor) changes made to the tables can alter the report's output, and at one point I thought it had output all the correct ones, then noticed that an open job (Gap 112) had disappeared from the report! Because of this, I really need to try to understand what is causing the problem, rather than finding a way to apparrently fix it, as I obviously don't want this to recurr in the future, or to miss any wrong missing/added jobs.

P.S. I know the two queries which drive the report are set up in a slightly strange way, but: a.) this is partly because the (part1) query is also used for other reports. b.) I still see nothing which would pose a problem, or explain the presence of records in the report which aren't in the query results.

Thanks for your efforts.

 

[JaneC]

Hello Rob,

This is very weird, and also a little alarming. The only way I've come up with to get the required result is to make the part 2 query a Make-Table query and then run the report from the table instead of the query.

I think Reports use their own internal queries, but I've never seen anthing remotely like this.

HTH

Jane

 

[JoeMiller]

Rob: I'm running into the same thing as Jane, I can't get it to behave properly either short of redoing the queries altogether. I only worked on it for 15 minutes or so, but I will continue to try. I will say that I see many opportunities for improvement that would make things much easier to manage. The one query with all the joins is HORRID to look at, and I can imagine that something must be going on there.

I've never seen anything like this happen before, so it must be the relationships (both global and in query 1), the right join in query 2 is also questionable. Why is the lookup table in there at all when no fields from it are used. It doesn't make sense.

Jane: The reports don't use their own internal queries unless you specify a SQL select string as the recordsource, which makes this even more alarming! Joe Miller
joe.miller@flotech.net

 

[RobTsintas]

Thanks for trying. I know that the queries are a bit of a mess, but I just can't see how it could be anything to do with the queries when the query output is different from that of the report!

I will recommend to my boss that the whole database needs a good review and tidy up. That'll probably end up being my job anyway - hooray!

 

[JaneC]

Rob,

I don't want to alarm you and could well be wrong on this but, your part one query returns 253 rows, when I run a query of my own I get over 700 records where the latest closed_ind is "no".


Don't know if this sheds any light or not !!

Jane