Report returning fewer results than underlying query

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6897-report-returning-fewer-results-than-underlying.html
figure out what's causing this. Any ideas out there?


Thanks in advance,


Seth C Triggs
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
--
______

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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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

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?


"Seth C. Triggs" wrote in message
news:QbVbf.1879$Y97.1499@trndny05...
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
>
> 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

tina wrote:

>>I have a report that is returning only 2890 results, while its
>>underlying query returns 16,590.
>
>
> 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?

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
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
--
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
______

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Seth C. Triggs" wrote in message
news:4DVbf.577$PZ6.435@trndny07...
> tina wrote:
>
>>>I have a report that is returning only 2890 results, while its
>>>underlying query returns 16,590.
>>
>>
>> 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?
>
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
> 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

Allen Browne wrote:

> Seth, *what* records are missing?
>
> Does the report go as far as (say) D, and then all the remaining customers
> are missing?

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?

No, everyone has a Service Detail. It contains records over numerous years.

> Are any criteria being applied in the query?

No criteria in the list all. It's simply just "list all"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897

> Is the report being opened programmatically with a WhereCondition?

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:

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
>
> If these tables are joined just on address matches and without referential
> integrity, just about anything could go wrong.

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

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
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"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6897
> 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