Finding difference between 2 tables in Access 2003

[Adakak]

I know just enough Access to be dangerous.

I currently have a database that shows me the rows that each table has in
common but I need it to do just the opposite and I am lost.

Both tables are over 300,000 records and I have a short amount of time to
pull out the differences.

 

[Golfinray]

When you hit the new query button, there are two additional types you should
look at. One is finding unmatched records, one for finding duplicate records.

 

[John W. Vinson]

I know just enough Access to be dangerous.

I currently have a database that shows me the rows that each table has in
common but I need it to do just the opposite and I am lost.

Both tables are over 300,000 records and I have a short amount of time to
pull out the differences.

Different in what sense... do you have a unique primary key in each table? Are
you looking for records which exist in TableA but do not exist at all in
TableB? Or vice versa? or both?

Or are you looking for records which exist in both tables but contain data in
other fields that does not match?

All of these are possible... post back and indicate more about the nature of
the problem.

 

[Troy]

You could create an unmatched query and that will pull out any records which
do not match. It is one of the options when you click new query.

 

[a a r o n _ k e m p f]

you use the CHECKSUM function in order to get a simple list of a hash
from all of the columns in the table

Select * FROM
(
Select MyKey, Checksum(*) as CsAll
From MyTable
) derived1,
(
Select MyKey, CheckSum(*) as CsAll
From MySecondTable
) derived2
where derived1.mykey = derived2.mykey
and derived1.csall <> derived2.csall

there-- now don't you wish that checksum was supported in Jet?

It's time to move to SQL Server kid, Jet is obsolete and it has been
for a decade