Report returning fewer results than underlying query

S

Seth C. Triggs

Third try sending this message.

I have a report that is returning only 2890 results, while its
underlying query returns 16,590. Is there anything in the Report
settings that can cause this sort of thing to happen? This is a customer
database, so each page in the report has in its header the customer info
and the detail section has the customer's service history.

I've searched for record limiting options and things like that and can't
figure out what's causing this. Any ideas out there?


Thanks in advance,


Seth C Triggs
--
______

Seth C. Triggs The World According to Lefty
P.O. Box 616 http://www.svamcentral.org
Buffalo NY 14205 AOL IM- SethTriggs • ICQ- 8365635

"Don't drink and park, accidents cause people." — Unknown
 
T

tina

I have a report that is returning only 2890 results, while its
underlying query returns 16,590.
Click to expand...

what do you mean by "2890 results"? do you mean 2890 *pages* in the report?
are there 2890 customers, with multiple service records that have a combined
total 16,590?
 
S

Seth C. Triggs

tina said:
what do you mean by "2890 results"? do you mean 2890 *pages* in the report?
are there 2890 customers, with multiple service records that have a combined
total 16,590?
Click to expand...

The previous tries to send the message vanished.

There are two tables that are joined to create the "List all Customers"
report: Customers and Service Details.

There are 11,351 records in the Customers table. Each one is an
individual, unique customer. There are 16,577 records in the Service
Details table. Each record is a unique service appointment, keyed to the
customer's street address. (Not my schema!)

The underlying query that combines them correctly gives me 16,564
records (this discrepancy occurs because of blank address fields for
some service records, as well as misspellings of a handful of names)

What is supposed to happen is that all 11,351 customers should have a
page on the List All Customers report. However, this is not happening,
there are only 2,890 displayed. I don't know what sort of interaction
happens between the query and the Report that can cause the Report to
display radically different results.

By the way, the report is set up where the page header has the record
from the Customer table, and the Details section has all of the records
matching that customer from the Service Details table. Each customer
should only have one page. I have verified that most of the customers
are missing. But I am not sure why.

Thank you,


Seth
--
______

Seth C. Triggs The World According to Lefty
P.O. Box 616 http://www.svamcentral.org
Buffalo NY 14205 AOL IM- SethTriggs • ICQ- 8365635

"Don't drink and park, accidents cause people." — Unknown
 
A

Allen Browne

Seth, *what* records are missing?

Does the report go as far as (say) D, and then all the remaining customers
are missing?

Is it the customers who have never had an entry in the Service Details that
are missing?

Are any criteria being applied in the query?

Is the report being opened programmatically with a WhereCondition?

A corrupt index can cause what you describe (generally fixed by Tools |
Dataase Utilities | Compact/Repair), but I suspect there is something much
more basic going on here. A couple of common causes are discussed in this
article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

If these tables are joined just on address matches and without referential
integrity, just about anything could go wrong.
 
S

Seth C. Triggs

Allen said:
Seth, *what* records are missing?

Does the report go as far as (say) D, and then all the remaining customers
are missing?
Click to expand...

No, it does return an A-Z condition (though for example the last
customer in the Customers table does not appear in the "list all" report.
Is it the customers who have never had an entry in the Service Details that
are missing?
Click to expand...

No, everyone has a Service Detail. It contains records over numerous years.
Are any criteria being applied in the query?
Click to expand...

No criteria in the list all. It's simply just "list all"
Is the report being opened programmatically with a WhereCondition?
Click to expand...

No, just a simple open.
A corrupt index can cause what you describe (generally fixed by Tools |
Dataase Utilities | Compact/Repair), but I suspect there is something much
more basic going on here. A couple of common causes are discussed in this
article:
Click to expand...

I'll take a look at the index. I had run JetComp before and that removed
some particularly annoying errors about the tables. I will try doing the
Compact/Repair too maybe. But first I will look at your page.

The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

If these tables are joined just on address matches and without referential
integrity, just about anything could go wrong.
Click to expand...

That's what I was thinking. But I'm not sure. Will have a look at your
page! Thank you!

-Seth

--
______

Seth C. Triggs The World According to Lefty
P.O. Box 616 http://www.svamcentral.org
Buffalo NY 14205 AOL IM- SethTriggs • ICQ- 8365635

"Don't drink and park, accidents cause people." — Unknown
 
S

Seth C. Triggs

Seth C. Triggs wrote:

I was able to increase the number of records displayed to about 7,571 by
moving the customer details to the "Last Name Header", which seems to be
built into this particular report. Perhaps it's from one of the groups
in the database. But 7,571 is still far less than what I am looking for.
The names are beginning to match up a little more with what's seen in
the Customer Table, however, which is good. And the report is at least
giving the proper behavior.

-Seth
There are two tables that are joined to create the "List all Customers"
report: Customers and Service Details.

There are 11,351 records in the Customers table. Each one is an
individual, unique customer. There are 16,577 records in the Service
Details table. Each record is a unique service appointment, keyed to the
customer's street address. (Not my schema!)

The underlying query that combines them correctly gives me 16,564
records (this discrepancy occurs because of blank address fields for
some service records, as well as misspellings of a handful of names)

What is supposed to happen is that all 11,351 customers should have a
page on the List All Customers report. However, this is not happening,
there are only 2,890 displayed. I don't know what sort of interaction
happens between the query and the Report that can cause the Report to
display radically different results.

By the way, the report is set up where the page header has the record
from the Customer table, and the Details section has all of the records
matching that customer from the Service Details table. Each customer
should only have one page. I have verified that most of the customers
are missing. But I am not sure why.

Thank you,


Seth
Click to expand...


--
______

Seth C. Triggs The World According to Lefty
P.O. Box 616 http://www.svamcentral.org
Buffalo NY 14205 AOL IM- SethTriggs • ICQ- 8365635

"Don't drink and park, accidents cause people." — Unknown
 
Top