Need to find the unmatched records in unrelated tables

[Kylor]

I have two tables of information on employees that are not related because they come from two different sources. I need to find which employees are not on both tables. I will need to also show which ones are on both tables. Thanks.

 

[Pat Hartman]

To determine if a record is in one table but not in another, you need to match on some field. Do the two tables have the same EmployeeID or do you have to match on name?

You need to do what is called a full outer join which is not directly supported by Access.

Create two queries that join the two tables and select all the relevant columns. The first query should use a join type of Left and the second query should use a join type of Right. A third query will pull the recordsets together and eliminate dupes.

Select * from query1
Union Select * from query2;

There will be three types of records in the resulting recordset. Those with data in the columns from tblA and from tblB, those with data only in the columns from tblA, and those with data only in the columns from tblB.

 

[Kylor]

full outer join

Sounds good. I'll try it when I get back into the office. Thanks so much!

 

[Kylor]

Select * from query1
Union Select * from query2;

Sorry, I tried perhaps I am missing a step? I am using the Soc # on both tables as the related field. When I tried your suggestion I ended up with 23 Million records. The original tables had 1100 and 2100.

I did the left and right join queries then used those the two in order to create a third. What do you mean by "union select"? Maybe that is what I am missing?

 

[Pat Hartman]

The third query (the only sample I posted) is supposed to be a union query. It says to return all the rows from query1 (the left join) followed by all the rows from query2 (the right join) and eliminate duplicate rows. I couldn't post samples for query1 and query2 because you had not supplied enough information. I just told you in words how to build them.

Sounds like your third query tried to join the two other queries without specifying a join type. The default is a cross join which is also called a cartesian product and matches every row in tblA with every row in tblB. The resulting number of rows is A * B which explains why you got back millions of rows. The union query will produce a recordset with A + B rows.

 

[Kylor]

Got It!

The third query (the only sample I posted) is supposed to be a union query.

This is where I went wrong. There was no relation between the tables in the third query. When joined the tables I got back 1049 records. Thanks.

 

[Pat Hartman]

There is NO join at all in the third query! It is a UNION query.