Compare Trables in database

[Paul123]

Being a relative newbie to queries I need assistance coding queries to compare various fields within several tables contained in a single database. Each table is different in structure in that they do not have the same columns. However they do have similar information (e.g. table 1 has CustomerID and CustStatus and table 2 has Client ID and ClientStatus).

I need to create a query that will:
(1) identify if table 1 has a customerID that is not contained in table 2,
(2) identify if table 2 has a client ID that is not contained in table 1, and
(3) identify differences between the CustStatus and ClientStatus fields.

I've tried sample code from the web but have failed miserably. You help is greatly appreciated.

Thanks,
Paul123

[pks00]

U could try the NOT IN. Now I dont use the design view of queries, I prefer the SQL view, so u could try changing this and pasting it into your sql
Im assuming CustomerID and ClientID are the same

1)
SELECT * FROM Table1
WHERE CustomerID NOT IN (SELECT DISTINCT ClientID From table2)

2) reverse of above

SELECT * FROM Table2
WHERE ClientID NOT IN (SELECT DISTINCT CustomerID From table1)

3) identify differences between the CustStatus and ClientStatus fields.
Now this may work, might be a bit slow though.

SELECT table1.*, table2.*
FROM table1, table2
WHERE table1.CustomerID = table2.ClientID
AND table1.CustStatus <> table2.ClientStatus

[NeoPa]

Using subqueries (as shown by pks00) is very powerful and flexible. You could probably get away with a simple JOINed query here using a LEFT join and selecting items where the linked field Is Null.