Query doesn’t pull up all records (2002)

Topic

New Reply

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??

Reply | Quote

Replies

Hi cat,

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote