Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Query doesn't pull up all records (2002)

    I have 3 tables I need to get data from but I know I'm doing this wrong. Table 1 has the list of all my contacts (ID, name, address, etc). Table 2 has records for contacts that had transactions in 2000 only (ID, name, total). Table 3 has records for contacts that had transactions in 2001 only (ID, name, total). I want to create a query/report that lists ALL contacts who had transactions in either 2000 or 2001, but the simple query I made is only picking up contacts who had transactions in both years. The three tables are linked by an ID field. HELP please??

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query doesn't pull up all records (2002)

    Hi cat,

    Just taking a stab.
    Have you considered table relationships and joins?

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query doesn't pull up all records (2002)

    Create a union query with Table2 and Table3, then create a select query with Table1 and the union query.
    Francois

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query doesn't pull up all records (2002)

    Cat,

    Here's how you can do this. Design a new query. Add Table 1 to the QBE grid. Then add Table 2 and Table 3. Join the tables using ID.

    Now, doubl;e-click on the join lines. When the dialog box comes up, select either 2 or 3. Which one? The one that makes an arrow going from Table 1 to Table 2 (and to Table 3 for the other).

    Bring the ID field from Table 1 to the bottom half of the QBE grid. Do the same for the ID field of Table 2 and Table 3.

    Click on the Totals button (Greek Sigma, or funny looking capital E). Under Table 1's ID field, leave Group By as Group By. Under Table 2's ID, change Group By to Where. Under Table 3's ID, change Group By to Where.

    In the Condition cell under Table 2's ID, type in Is Not Null. For Table 3's ID, go over one and down one and type in Is Not Null. These conditions must be on separate rows to get the correct results.

    Run the query. Your results should be contacts - OK, their ID's - who had transactions in 2000 (Table 2) or 2001 (Table 3) or both years.

    HTH,

    Tom

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query doesn't pull up all records (2002)

    What Tom suggests will show those which had transations in 2000 or 2001 or both years.
    If you want to show just those which have transactions in 2000 or 2001 (but not both) then go to Tom's query and put "Is Null" under "Is Not Null" under Table 2's ID field and put "Is Null" above the "Is Not Null" under Table 3's Id field.
    HTH
    Pat

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query doesn't pull up all records (2002)

    I finally got a chance to try this out. Tom's solution worked like a charm. I didn't know that you could put criteria on two different lines. That makes all the difference in the world. THANKS!

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query doesn't pull up all records (2002)

    Criteria entered on two different lines represent an OR condition. Criteria entered on the same line represent an AND condition.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •