Comparing Query Results

[Elaine J.]

I am not entirely sure this is in the right forum. I'm not sure if this a
query question or a VBA question. But it is my understanding that I should
only post the question once.

I have two tables in Excel that are linked to Access.

I have built select queries for each table and when I run the two queries,
the number of records should match.

Table1 has ALL the records
Table2 should have all the records (but does not always have all of them)

I need to know which records are not in Table2. I only need to compare one
field to determine what is missing.

How can I compare the results of the two queries and return results for the
records that do not match (ie. are not in Table2).

I do need to be able to compare the queries and not the tables. (because
table1 has many records that are not included in the query). Or should I
consider using Make Table Queries and comparing those two tables? That seems
like the long way around to me, and I'm hoping for a simpler solution.

I know this can be done, but I'm not sure if would be done with another
query, or through VBA, or some other option. If possible I would like a
complete walk through of the process, so I can understand what I am doing and
apply it in other areas.

Thanks so much for any help.

 

[pietlinden]

I need to know which records are not in Table2.  I only need to compareone

field to determine what is missing.

How can I compare the results of the two queries and return results for the
records that do not match (ie. are not in Table2).  

I do need to be able to compare the queries and not the tables.  (because
table1 has many records that are not included in the query).  Or shouldI
consider using Make Table Queries and comparing those two tables?  Thatseems
like the long way around to me, and I'm hoping for a simpler solution.

No, make table queries are a bad idea here. Not necessary. You can
just use an outer join instead of an inner join.

Create a new query, and add both Table1 and Table2 to the QBE grid.
join them on the matching field(s).
Right-click on the line joining the two tables. You should get a
dialog asking about the join - which table contains all the records/
matching records. Make the table with the greater number of records
the "left" table. (Show all records from that table). Then in the
join key from "Table2", set the criteria to IS NULL.

HTH,
Pieter

 

[Elaine J.]

THAT is so cool. Thank you so much. It is exactly what I was looking for.

Elaine