Take the 2-minute tour ×

I have two Access database tables containing place names.

Table A contains 30,000 places and Table B contains 25,000 places.

How can I end up with Table C that only contains the 5,000 places that are in Table A but not in Table B?

share|improve this question
Does it have to go into a table or will a query do? –  Col Aug 23 '11 at 15:10
add comment

1 Answer

In the SQL world, you'd run a SELECT query on the bigger table that outputs all the place names in table A for which a match cannot be found in B - something like:

SELECT     a.placename
FROM       tablea as a
WHERE      NOT EXISTS (SELECT * FROM tableb as b WHERE b.placename = a.placename)

This would form part of a query that puts the results in table c

Not sure if that will help but I have nothing in reach running Access right now! Hopefully there will be an Access guru along in a moment...

share|improve this answer
NOT EXISTS is not well-optimized in Jet/ACE -- it often won't use the indexes on both sides of the comparison and is thus very slow. Usually a LEFT JOIN will be more efficient, but the result may not be editable. That may not matter here. –  David W. Fenton Aug 24 '11 at 21:43
add comment

Your Answer


