Merging 2 tables

[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 (10,400 records).
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

 

[Rick B]

Please do not post your question more than once. Be patient!

 

[JohnFol]

There are no fields in common, so is there a linking table that relates the
data in these 2?

 

[Malcolm WTII]

The field 'id' is the link between the two tables.

 

[JohnFol]

Hi Malcolm,
Within the Make Table you must be combining the 2 tables somehow. I'll
guess you do it with a simple join between the two tables on the ID field. I
will also guess the problem is this:

Names:
ID 1 Fred Bloggs
ID 2 Joe Smith

Addresses:
NameID 1 "1 The High street"
NameID 2 "2 The Mall"
NameID 1 "3 The Strand"

When you join them you get

NameID 1 Fred Bloggs, "1 The High street"
NameID 2 Joe Smith, "2 The Mall"
NameID 1 Fred Bloggs, "3 The Strand"

And you want

NameID 1 Fred Bloggs, "1 The High street"
NameID 2 Joe Smith, "2 The Mall"

To do this you need another query or 2. First, you need to get a list of
PK's from address for the first ocurrence of each ID, something like

Select Address.PK, Min(NameID) from Address group by Address.pk

(Save this as Q1)

Then you get the adderss details based on this

Select Address.* from Address where Address.pk in (select Address.PK from
Q1)
(save this as Q2)

Now, in your Make Table, use Q2 and link it with table Names

 

[Malcolm WTII]

Close Using your example, what I want is:
NameID 1 Fred Bloggs, "1 The High street", "3 The Strand"
NameID 2 Joe Smith, "2 The Mall"

It's really more complicated than I stated, because there are phone numbers,
addresses, and another small reference table. I was able to do what I want,
but it required many small queries. Using only addresses as the example,
this is pretty much what I did:
1) Create new fields in my primary Names table to contain the extra address
info
2) Create a new addresses table that contains all the old addresses table
info, plus one new column, RefNum
SELECT Addresses.Reference, Addresses.Address1, Addresses.Address2,
Addresses.Address3, Addresses.City, Addresses.State, Addresses.Zip,
Addresses.Country, Addresses.ID, Addresses.primarykey, [RefNumx] AS RefNum
INTO AddressesNew
FROM Addresses
ORDER BY Addresses.ID;
3) Create a new table that contains the number of Duplicates from the new
address table (rinse and repeat 3 times, that's the max number of dupes)
SELECT First(AddressesNew.ID) AS [AID Field], Count(AddressesNew.ID) AS
NumOfDups INTO AddressDupes1of3
FROM AddressesNew
GROUP BY AddressesNew.ID
HAVING (((Count(AddressesNew.ID))=1))
ORDER BY Count(AddressesNew.ID);
4) Update the RefNum field in the new addresses table to 1, 2, or 3. I used
3 queries to achieve this.
UPDATE AddressesNew INNER JOIN AddressDupes1of3 ON AddressesNew.ID =
AddressDupes1of3.[AID Field] SET AddressesNew.RefNum = '1';
5) Update the Names table for the extra addresses. I used 3 queries for
this, the one below updates the 'BusinessAddress' if RefNum = 1.
UPDATE NamesNew INNER JOIN AddressesNew ON NamesNew.ID=AddressesNew.ID SET
NamesNew.BusinessReference = addressesnew.reference,
NamesNew.BusinessAddress1 = addressesnew.address1, NamesNew.BusinessAddress2
= addressesnew.address2, NamesNew.BusinessAddress3 = addressesnew.address3,
NamesNew.BusinessCity = addressesnew.city, NamesNew.BusinessState =
addressesnew.state, NamesNew.BusinessZip = addressesnew.zip,
NamesNew.BusinessCountry = addressesnew.country
WHERE AddressesNew.RefNum='1';

And that's it. Rinse and repeat the process for phone numbers or whatever.

M

 

[Malcolm WTII]

Actually, this is a little off. The result is that it doesn't put 1, 2, 3
into the 3 addresses, it puts 3 into each of the addresses, which results in
only 1 address getting written into the Names table. Gotta work on that
M

Close Using your example, what I want is:
NameID 1 Fred Bloggs, "1 The High street", "3 The Strand"
NameID 2 Joe Smith, "2 The Mall"

It's really more complicated than I stated, because there are phone
numbers, addresses, and another small reference table. I was able to do
what I want, but it required many small queries. Using only addresses as
the example, this is pretty much what I did:
1) Create new fields in my primary Names table to contain the extra
address info
2) Create a new addresses table that contains all the old addresses table
info, plus one new column, RefNum
SELECT Addresses.Reference, Addresses.Address1, Addresses.Address2,
Addresses.Address3, Addresses.City, Addresses.State, Addresses.Zip,
Addresses.Country, Addresses.ID, Addresses.primarykey, [RefNumx] AS RefNum
INTO AddressesNew
FROM Addresses
ORDER BY Addresses.ID;
3) Create a new table that contains the number of Duplicates from the new
address table (rinse and repeat 3 times, that's the max number of dupes)
SELECT First(AddressesNew.ID) AS [AID Field], Count(AddressesNew.ID) AS
NumOfDups INTO AddressDupes1of3
FROM AddressesNew
GROUP BY AddressesNew.ID
HAVING (((Count(AddressesNew.ID))=1))
ORDER BY Count(AddressesNew.ID);
4) Update the RefNum field in the new addresses table to 1, 2, or 3. I
used 3 queries to achieve this.
UPDATE AddressesNew INNER JOIN AddressDupes1of3 ON AddressesNew.ID =
AddressDupes1of3.[AID Field] SET AddressesNew.RefNum = '1';
5) Update the Names table for the extra addresses. I used 3 queries for
this, the one below updates the 'BusinessAddress' if RefNum = 1.
UPDATE NamesNew INNER JOIN AddressesNew ON NamesNew.ID=AddressesNew.ID SET
NamesNew.BusinessReference = addressesnew.reference,
NamesNew.BusinessAddress1 = addressesnew.address1,
NamesNew.BusinessAddress2 = addressesnew.address2,
NamesNew.BusinessAddress3 = addressesnew.address3, NamesNew.BusinessCity =
addressesnew.city, NamesNew.BusinessState = addressesnew.state,
NamesNew.BusinessZip = addressesnew.zip, NamesNew.BusinessCountry =
addressesnew.country
WHERE AddressesNew.RefNum='1';

And that's it. Rinse and repeat the process for phone numbers or
whatever.

M

Hi Malcolm,
Within the Make Table you must be combining the 2 tables somehow. I'll
guess you do it with a simple join between the two tables on the ID
field. I will also guess the problem is this:

Names:
ID 1 Fred Bloggs
ID 2 Joe Smith

Addresses:
NameID 1 "1 The High street"
NameID 2 "2 The Mall"
NameID 1 "3 The Strand"

When you join them you get

NameID 1 Fred Bloggs, "1 The High street"
NameID 2 Joe Smith, "2 The Mall"
NameID 1 Fred Bloggs, "3 The Strand"

And you want

NameID 1 Fred Bloggs, "1 The High street"
NameID 2 Joe Smith, "2 The Mall"

To do this you need another query or 2. First, you need to get a list of
PK's from address for the first ocurrence of each ID, something like

Select Address.PK, Min(NameID) from Address group by Address.pk

(Save this as Q1)

Then you get the adderss details based on this

Select Address.* from Address where Address.pk in (select Address.PK
from Q1)
(save this as Q2)

Now, in your Make Table, use Q2 and link it with table Names