query to find id's with duplicate data across multiple tables

[bbbeng]

I have 6 tables with the same design but the data is different because they are pulling data from 6 different places (it is an ODBC connection to other databases).



I want to create a report that will show me the id for each row.

The table design is: employeeID, SSN, Name ....

So, I want a report that will show me every SSN and for each SSN, I want to see the employeeID (if it exists) for each table.

The columns for the report would be: SSN, employeeID1, employeeID2, employeeID3, employeeID4, employeeID5, employeeID6

Any ideas? Thanks!

[bbbeng]

Just realized I should have been a little more clear about why this is a challenge. An inner join will not return all of the SSN's, an left or right join will only return the SSN's on one table. I need to be able to get a list of all SSN's then find the employeeID for the SSN on each table. (I dont know if I just made it more clear or muddier! Thanks for any ideas.

I am thinking I need to create a big master list of SSN's then run the queries against that.