Merging 2 tables

[GrandmaT]

Hello,
I'm pretty new to Access and need some help merging two tables. I have 2 tables with some basic information: id (autonumber), lname, fname, address, city, st, zip. Many of the names are duplicated. Using the "Find Unmatched query wizard" I created an append query to append records from tbla to tblb. But when the names are same, I run into problems. Do I need to string the lname and fname together somehow? Thanks so much!

GrandmaT

 

[Ian Mac]

Hi,

You could use a UNION Query to pull the information together,
as long as the Field Headings are the same and he data is the same type, i.e. Number/Number etc.

You will need to do this SQL view as the Query builder doesn't do UNION's,
open a new Query and change from Design to SQL in the top left hand of the screen.

Something like:

SELECT lname, fname, address, city, st, zip FROM Table1
UNION SELECT lname, fname, address, city, st, zip FROM Table2;

This will create a query with unique records.

However!!!!! if the address, city, st and zip are different you will have 2 records.

I think you should also think about changing the way your data is stored.

Have 1 table with lname, fname, DOB etc.
And 1 with address, city, st, zip

Your tables should be something like:

Details:
ID - Primary Key, Autonumber
lname - Text
fname - Text

Addresses:
AddressID - PK, Autonumber
ID - Foreign Key (Link to Details Table)
address1
address2
city
st
zip

This will be a better way to organise your data as it will keep a history of Address if someone Changes.

 

[GrandmaT]

Thanks for the suggestions - I see your point of separating the addresses. Now I'll tackle the union query.

grandmat