Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Report returning fewer results than underlying query

Discussion in 'Information Technology' started by Seth C. Triggs, Nov 8, 2005.

  1. Seth C. Triggs

    Seth C. Triggs
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    UK British Sikh Report-2016 Breaking News Mar 24, 2016
    1984 The Story I Did Not Report In ’84 History of Sikhism Jun 28, 2015
    Opinion Top Green Beret Officer Forced to Resign Over Affair With WaPo Reporter Breaking News Jun 26, 2014
    United Sikhs New Report Shows Increasing Hate Attacks on Sikhs Sikh Organisations May 21, 2014
    Legal ACLU reports: Judge orders Sikh man, "Remove that rag, or Go to Jail! Breaking News Sep 27, 2013

  3. tina

    tina
    Expand Collapse
    Guest

    > 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" <seth.triggs@verizon.net> 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.
    >
    > 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
     
  4. Seth C. Triggs

    Seth C. Triggs
    Expand Collapse
    Guest

    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
    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
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <seth.triggs@verizon.net> 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
    > 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
     
  6. Seth C. Triggs

    Seth C. Triggs
    Expand Collapse
    Guest

    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"

    > 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
    >
    > 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
     
  7. Seth C. Triggs

    Seth C. Triggs
    Expand Collapse
    Guest

    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



    --
    ______

    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
     

Share This Page