468,161 Members | 1,993 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,161 developers. It's quick & easy.

finding info in a master table

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

Nov 13 '05 #1
1 1651
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).

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by feck | last post: by
1 post views Thread by rdemyan via AccessMonster.com | last post: by
1 post views Thread by gcdp | last post: by
reply views Thread by kamranasdasdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.