Compare two tabels

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 tables. The tables are an exact match of the other. Data and structure. I need to compare both tabels to get the data that is not in the first table. The first table is smaller and the second table is bigger. ofcourse the second table has more records. I want these extra records that were'nt generated the first time. For example, if table 1 initially had 3000 records and when I run the process again and get 3050 records that I store in a second table, I want 50 records returned.
My datasource is a separate database, which explains the data.

Help,

p.s - The unmatched wizard isn't working because it uses one field to compare. I need to create a muti-field key to create a unique record.

Thanks in advance
 
Neil-

You can start with the Unmatched wizard and then modify the query it
creates. Simply add the additional relationship line(s) for the extra
field(s) you need to define a unique record. Be sure to double-click the
relationship line to open the Join Properties and choose "all records" from
the bigger table and "any matching" from the smaller table. That should
give you an arrow on the join line pointing in the same direction as the one
the wizard created.

In SQL View, the result will look something like:

SELECT TableA.*
FROM TableA LEFT JOIN TableB
ON (TableA.Key1 = TableB.Key1
AND TableA.Key2 = TableB.Key2)
WHERE TableB.Key1 IS NULL;

... where TableA is the "big" table and TableB is the smaller one.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
neil said:
I have 2 tables. The tables are an exact match of the other. Data and
Click to expand...
structure. I need to compare both tabels to get the data that is not in the
first table. The first table is smaller and the second table is bigger.
ofcourse the second table has more records. I want these extra records that
were'nt generated the first time. For example, if table 1 initially had
3000 records and when I run the process again and get 3050 records that I
store in a second table, I want 50 records returned.
My datasource is a separate database, which explains the data.

Help,

p.s - The unmatched wizard isn't working because it uses one field to
Click to expand...
compare. I need to create a muti-field key to create a unique record.
 
I have the same kind of question.
is there any way we can get all unmatched records from
both table?
Thanks,
Jim.
 
Not in Access with one query. You would probably need two or three queries to
do it.

One query to get the unmatched in each of the two tables and then possibly a
UNION query to combine the results.
 
Back
Top