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

 

[Joseph Meehan]

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

Let me get this straight. You have two tables one with names and one
with addresses where there may be up to four addresses for each name and you
want to combine them into a single table with the name and four fields each
containing one of the possible addresses that were properly stored in a
second table?

About the only thing I can think of would be to create a new field in
the address table. use a query to populate the field with one of four
characters or numbers based on the first, second, third, or forth address.
Then write a little code to run through the possibilities one at a time per
record resetting each time the name changes.

Then create the new fields for addresses in your names table and use an
update query to populate each address filed based on the four characters or
numbers one field at a time.

Seems like a lot of work to screw up a properly normalized table. Are
you sure you really want to do this?

 

[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