table comparison in Acccess

I have 2 tables (A and B) with the same structure, what is the easiest way to obtain a list of combined record without double counting the row with the same record in a chosen field?
Who is Participating?
peter57rConnect With a Mentor Commented:
In that case use your equivalent of...

Select * from tableA
Select * from TableB as B where  not exists (Select Lastname from tableA where TableA.Lastname = B.Lastname)
Helen FeddemaCommented:
A union query should do it:

FROM tblContacts
UNION SELECT * FROM tblMoreContacts;

Open in new window

fitalianoAuthor Commented:
How do I ensure that Last Names are not double-counted?
Union will eliminate duplicates but the duplication is based on all selected fields.
If you want to base duplication just on one field then you will have to come up with some rules about how you decide which record to keep and which to remove.  You will probably then need a second process to get the final list.
fitalianoAuthor Commented:
Thanks Peter,

can you be more specific?

This is exactly what I would like to do:

I have [Address] and [Last Name] in both table A and B.

I want to obtain a list that combines both tables where the same [Last Name] in A and B is counted only once.  If there is a match between [Last Name] I want table A to win

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.