finding info in a master table

[Harag]

Hi all

I've not used Access 2k for a few years and can't remember how to get
information from one table that is not in several other tables.

I have 4 Tables in my DB

tbl 1 - TblMaster - 220k rows <<< note size
tbl 2 - tblA - 10k rows
tbl 3 - tblB - 8k rows
tbl 4 - tblC - 3k rows

The master table has a column with an ID number - I was told this is
unique but have found dupicate numbers. (its not an auto number)

Tables A, B, C, contain information (different from the Master table)
except for one field. the "unique" id number

I need to create a table D from table MASTER - that contains all the
records/rows that are *NOT* in tables A, B, C

I have looked at something like this:

SELECT tblmaster.* (i'll put field names here), INTO tblD
FROM
tblMaster
WHERE tblMaster.id NOT IN (SELECT id FROM tblA)
AND tblMaster.id NOT IN (SELECT id FROM tblB)
AND tblMaster.id NOT IN (SELECT id FROM tblC);
Is this the correct/best way to pull out all the rows from master tbl
that are not in A, B, C and store that info into tblD ???

Any other way that might be better. I did the above and it was still
working after 20 mins (had to leave work so stopped it)

Thanks for any help.

Alan

[pietlinden]

If possible, use outer joins instead of NOT IN.

SELECT A.fld1, A.fld2, B.fld1,B.fld2
FROM A LEFT JOIN B ON A.fld1=B.fld2
WHERE B.fld2 IS NULL;

Then just do a bunch of left joins (A to B, A to C...)
This will return a group of records. Once you get this right, turn the
query into an Append Query and add the records to a new table. (and
you might want to outer join to that to eliminate all the records that
are already in the destination table).