How to find differences across two tables?

  • Thread starter Thread starter Pharoh
  • Start date Start date
P

Pharoh

I'm using Access 2000 and it's been ages since I used it for anything
like this.

I have two tables that have identicle fields. One has the proper
records and one is missing some records. How can I find out which
records from Table B are not included in Table A?

I'm attempting to use a Left Join but I'm stuck trying to set it up.

THanks so much!
 
Let Access do the work for you. Start a new query and choose Find
Unmatched Query Wizard.
 
its not working for me. Firstly it won't even let me see all the fields
in one of the tables....including the one I actually need to compare.

this is the SQL I'm working with at the moment...

SELECT CRS_EHB_DIN.DIN, CRS_EHB_DIN.DINSHORTDESCRIPTION,
CRS_EHB_DIN.DINLONGTDESCRIPTION, CRS_EHB_DIN.DINTHERAPEUTICCODE
FROM CRS_EHB_DIN LEFT JOIN [Extract] ON CRS_EHB_DIN.DIN = Extract.DIN;
 
I assume that you want to see records in CRS_EHB_BIN that don't exist
in Extract. In that case, your join is correct, but you need to add:

WHERE Extract.DIN Is Null

HTH,
Barry
 
Thanks Barry! the funny thing is that I need the opposite. I need to
see records in Extract that don't exist in CRS_EHB_BIN. So I'm thinking
my join is backwards.
 
If the join is backwards, then you also need to change the Where
statement to look at CRS_EHB_DIN.DIN
 
I think this will work

SELECT [Extract].[BCI DIN]
FROM Extract LEFT JOIN [CRS_EHB_DIN] ON [Extract].[BCI DIN]=
[CRS_EHB_DIN].[DIN]
WHERE ([CRS_EHB_DIN].[DIN] Is Null);
 
Back
Top