Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 1

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.