Finding outliers: intersection of two tables

[tyemm]

Only three months and I'm rusty as ...!
I have two tables, and I want to find via a query, the records in one that are not also in the other table.
Neither table has a single unique identifier (I added an autonumberPK afterward), but is unique by virtue of a combination of two fields (i.e. "Map" and "Code" together define uniqueness in each of the two tables tblHB and tblM.

This is simple, right? I'm getting only the opposite of what I want.

 

[hneal98]

What do you have so far?

 

[Golom]

In general

Select tbl1.*

From tbl1 LEFT JOIN tbl2
     ON tbl1.Map = tbl2.Map AND tbl1.[Code] = tbl2.[Code]

Where tbl2.Map IS NULL

Gives you the records in tbl1 that are not in tbl2.

 

[tyemm]

Thank you, Golom, I have translated your suggestion into my
actual table names and fields:

SELECT tblHospital_B.*
FROM tblHospital_B LEFT JOIN tblHospital_032906 ON (tblHospital_B.CODE = tblHospital_032906.CODE) AND (tblHospital_B.Map = tblHospital_032906.Mapped)
WHERE (((tblHospital_032906.Mapped) Is Null));

But I am still not getting the records that I want; actually I am getting too many records, ones I know I want and others that don't belong. When I get this I am going to want to modify it to an Append Query, so that I'll append the records in the first table (tblHospital_B) that don't appear in the second table (tblHospital_032906), into that second table.

 

[Golom]

Try this

SELECT B.*

FROM tblHospital_B B LEFT JOIN tblHospital_032906 H 
     ON (B.CODE = H.CODE) AND (B.Map = H.Mapped)

WHERE H.Mapped Is Null AND H.CODE Is Null

I just introduced some table aliases to make it more readable.

You didn't say exactly which records are appearing that you don't want. Even if you don't make it the primary key, you should probably set (Code, Mapped) as a unique key in tblHospital_032906 and (if it makes sense) require them to be Non-Null.