Merge 2 tables, one is 1-1, other is 1-many

[Malcolm WTII]

Hi,
I need to merge two tables together. Let's call them Names and
Addresses. Names has one record per contact and about 10,000 records total.
Addresses has up to 4 addresses per contact and about 400 records total.
Names Table fields: (PK)id, firstname, lastname, etc.
Addresses Table structure: (PK)primarykey, id, street1, street2, street3,
city, state, zip, country, reference

I can do a simple Make Table query to combine them, but this results in
multiple rows for each of the records from the Names table. I would like to
only have one record per contact. (in effect, only 10,000 records).

I realize this is somewhat backwards from "good practice", the goal is to
make conversion into another system easier.

Thanks in Advance!
M

 

[Malcolm WTII]

Yeah, I'm sure There are a few problems. First, the DB is supposed to
contain Contacts, but the client has confused this concept with Accounts.
So the Names table contains records that -could- be contacts, or -could- be
accounts, or -could- be both. Worse, there might be 5 records that contain
the same firstname, lastname, or account name. This also means that phone
numbers and addresses are undoubtably repeated or wrong between records.
The goal is to produce 2 spreadsheets or .csv files. File 1 contains unique
account information, File 2 contains unique contact information. The
contacts will be imported into Outlook, and the accounts into another
system. And the DB goes into archive.

Thanks for the response. This was my original thought, and I was hoping
someone had an easier method. I typically work with SQL server more so, and
the subtle differences in syntax for Access SQL throws me off when I do work
with it.

Regards,
M