Table comparison

[Guest]

How do I compare the data between two tables in the same database? Once the
comparison is done, I would like to see the matching and non matching records
in both tables. The reason I need to do this, is that I have some fields in
table A that I need in table B. However, the table A has extra records that
I have excluded already in table B. My table B is the final draft. But its
missing the two fields that I have in table A. Thank you.

 

[Dirk Goldgar]

How do I compare the data between two tables in the same database?
Once the comparison is done, I would like to see the matching and non
matching records in both tables. The reason I need to do this, is
that I have some fields in table A that I need in table B. However,
the table A has extra records that I have excluded already in table
B. My table B is the final draft. But its missing the two fields
that I have in table A. Thank you.

When I need to do this, I usually drag both tables into the table pane
of a query in design view, and draw join lines between all the fields I
want to match on -- sometimes all the fields in the tables. Then I drag
the fields I actually want to see from each table to the field grid.
The default INNER JOIN gives me all the matching records, so if I switch
to datasheet view, that's what I see.

If I want to see only the unmatched records from one table, I flip the
query into SQL view and change the INNER JOIN to a LEFT or RIGHT JOIN
(depending on which table I want to see unmatched records from), then
flip back to design view and put an "Is Null" criterion into the Where:
line for the primary key field of the other table. Then switching to
datasheet view shows me only those records that are not matched.

One caution about this approach: it requires that none of the fields
you join on has a Null value in any record. Otherwise, that record
won't appear in the results. If this is a problem, you can get around
it by first creating a query based on each table, and using the Nz()
function to transform Null values in any field into the string "NULL" or
the number -99999999, or some other marker value that is suitable for
the field type. Then use these queries in place of the original tables
in the joining queries described above.

 

[Guest]

Thank you, but I'm looking for a simpler method.

When I need to do this, I usually drag both tables into the table pane
of a query in design view, and draw join lines between all the fields I
want to match on -- sometimes all the fields in the tables. Then I drag
the fields I actually want to see from each table to the field grid.
The default INNER JOIN gives me all the matching records, so if I switch
to datasheet view, that's what I see.

If I want to see only the unmatched records from one table, I flip the
query into SQL view and change the INNER JOIN to a LEFT or RIGHT JOIN
(depending on which table I want to see unmatched records from), then
flip back to design view and put an "Is Null" criterion into the Where:
line for the primary key field of the other table. Then switching to
datasheet view shows me only those records that are not matched.

One caution about this approach: it requires that none of the fields
you join on has a Null value in any record. Otherwise, that record
won't appear in the results. If this is a problem, you can get around
it by first creating a query based on each table, and using the Nz()
function to transform Null values in any field into the string "NULL" or
the number -99999999, or some other marker value that is suitable for
the field type. Then use these queries in place of the original tables
in the joining queries described above.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thank you, but I'm looking for a simpler method.

There is no built-in method to make the comparison you asked for. In
addition to the query approach I suggested, I also have various versions
of rudimentary VBA procedures, developed purely to help me in debugging
queries, to compare the recordsets resulting from two queries. I'd have
posted one of those, but (a) none of them does exactly what you're
asking for and I don't have the time at the moment to modify them for
that purpose, and (b) they're very rough. If you want, I could post one
for you to tinker with. Let me know.