MERGING RECORDS

[MedicSkip]

I have a table that lists name, address, city, zip. In this table are duplicate addresses with different names (they are family members).

Present table
Name
Address
City
Zip

I want to append this table to another that has fields for each resident at an address.

Append table
Name
Address
City
Zip
Household Member1
Household Member2
.
.
Household Member7

Is the a query or code that will a) keep the first name associated with an address in the Append Table - Name field and b) put any any additional associated names into the Household Member fields?

Thanks in advance for any help or guidance.

 

[Golom]

I'm a bit fuzzy on exactly what you want.

First of all ... Append in SQL means "add records to a table". That means that you need a table to add the records to (presumably the "Append Table") and a source for the data to be added. I'm not sure what the source is in your example.

Second ... I'm not sure what you mean by "... keep the first name associated with an address ...". An Append does not change existing records. It only adds new ones.

Third ... You have an un-normalized table (Append Table). It has repeating fields (i.e. Household Member1, Household Member 2, etc.). It is difficult with standard SQL to deal with tables like that because a new "Household Member" for some specific address may be the first one on one record, the third on another, unable to insert because all fields are used on still another. You should have a seperate table something like this

[tt]tblHouseHoldMembers
Address HouseHoldMember
123 Bill
123 Bob
123 Mary
444 Willie
999 Larry
etc.
[/tt]
Change as required to add whatever fields are necessary to uniquely identify the "HouseHold". In this example I have assumed that "Address" is sufficient.

 

[Darrylles]

Hi,

For what you have described, this is the table structure you need:
[tt]
tblAddress tblFamily
pk --------- pk
surname | forename
address |---->> address_fk
city
zip
[/tt]
The pk in each table is an autonumber Primary Key, the address_fk is the Foreign Key link from tblFamily to tblAddress.
So you have a unique address, with the surname of the family in the address table.
For each member of the family, you create a record in tblFamily, copying the relevant tblAddress pk value to the tblFamily address_fk field.

So you have a unique address record with a unique surname (which is duplicated in your structure).

This is what Golom was talking about re: normalization.

It's not good to have fields such as 'Household Member1, Member2' etc because you don't know how many members there will be. This means that realistically, you'd need to put at least 8 'MemberX' fields in the table to be sure (and even then - who knows - some may have 9 family members?)
AND, 9 times out of 10 - you'll never use more than 4 of these fields, which wastes space.

This way, you only create a new tblFamily record - if you need one.

NB IF there's a possibility that there may be 'family' members within the same address with DIFFERENT surnames (unlces/aunts etc) then move the surname field to the family table.

Kind regards,

Darrylle







Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@hotmail.com