newbie question on how to 1

[paladin256]

Let me start by saying I have no database experience. I have two different databases containing client info. What I would like to do is compair the client names and account numbers in both databases and if they do not match get the client name and the account number in the old and new database. I am unsure how to do this. Should I use a join or a union or something entirly?

Thank You

 

[lameid]

Assuming you have the tables linked in one database, you would use an outer join query to compare the two tables to determine what is in one table and not the other. You would then need a second query to do the same for the other direction. Lastly you could combine both using a Union.

As for more specifics, you can look in help or if you post more details about your tables, fieldnames and datatypes, and table names; someone can likely help you further.

 

[PWise]

i would do it this way have all reltive tables linked in one database

create a union query with all client names and account numbers
to give you distinct client names and account numbers

Select client_name and account_number
From dbOneTable
Union
Select client_name and account_number
From dbTwoTable

Save As Qnames

create a query left joining
dbOneTable
dbTwoTable
Select only relative fields

Select Qnames.*,dbOneTable.*,dbTwoTable.*
From Qnames
left join dbOneTable
on Qnames.client_name = dbOneTable.client_name
and Qnames.account_number =dbOneTable.account_number
left join dbTwoTable
on Qnames.client_name = dbTwoTable.client_name
and Qnames.account_number =dbTwoTable.account_number

 

[Golom]

Let us suppose that T1 is the table of interest in database 1 and T2 is the corresponding table in database 2. Further, let us also suppose that you have linked T2 into database 1 so that both T1 and T2 can be referenced locally. Then

SELECT 'T1' As [Table], T1.Client_Name, T1.Account_Number
FROM T1 LEFT JOIN T2 
     ON  T1.Client_Name = T2.Client_Name 
     AND T1.Account_Number = T2.Account_Number
WHERE T2.Client_Name IS NULL

UNION

SELECT 'T2' As [Table], T2.Client_Name, T2.Account_Number
FROM T2 LEFT JOIN T1 
     ON  T2.Client_Name = T1.Client_Name 
     AND T2.Account_Number = T1.Account_Number
WHERE T1.Client_Name IS NULL

This query finds, before the UNION, records in T1 that are not in T2 and, after the UNION, records that are in T2 but not in T1. The UNION just combines the two results.

 

[lameid]

FWIW Golom's solution is what I suggested generically.

PWISE's solution does not limit the results to those that are not in the other system but instead shows you everything.