Merging two tables

  • Thread starter Thread starter Mark G.
  • Start date Start date
M

Mark G.

I'm new to access and trying to merge two tables. One table is a complete
customer list (with first name, last name and e-mail list), the second is a
smaller version of the first list but only has first name and last name
fields. I would like populate the second list with e-mail addresses from
the first list and get rid of any duplicates. Can anyone help me with this?
Thanks, Lisa
 
Mark said:
I'm new to access and trying to merge two tables. One table is a
complete customer list (with first name, last name and e-mail list),
the second is a smaller version of the first list but only has first
name and last name fields. I would like populate the second list
with e-mail addresses from the first list and get rid of any
duplicates. Can anyone help me with this? Thanks, Lisa
Click to expand...

I would start with an "Update" query to add the information you want.

Next you can do a find duplicates query.
 
Hi,

Couple of questions:
Do you want to actually put the email addresses into the second(Smaller)
table?
In which table are the duplicates, or are there some in both tables?
OK, 3 questions:
Why are you maintaining 2 customer lists? Unless this is just a 'tidy up'
excercise, and you plan to delete one or other of them shortly,
it is normally a bad idea to keep the same data in 2 places.

It might be worth considering adding an 'Id' field to your table. If you use
the 'AutoNumber' field type, then Access will automatically generate a new
number each time you add a record.

this would make deleting duplicates a bit simpler.

You could say somthing like

DELETE from customerList cl1
WHERE EXISTS
(
SELECT * FROM customerList cl2
WHERE cl1.firstName = cl2.firstName
AND cl1.secondName = cl2.secondName
AND cl1.email = cl2.email
AND cl1.recId > cl2.recId
)

ChrisM
 
Back
Top