Merging Duplicates "householding"

[IATAILG8ER]

Hi, I'm new to Access and have a problem I was hoping that someone could help me out with. I am working with a mailing list that has a Name field, Address Field, City Field, State Field and a Zip Code Field. What I would like to do is combine records that have the same address and zip. Example:

Start with these records:

RECORD NUMBER, NAME, ADDRESS, CITY, STATE, ZIP CODE
1. Jack Jones, 123 Main St, Chicago, IL, 60015
2. Sarah Jones, 123 Main St, Chicago, IL, 60015
3. Alice Jones, 432 Main St, Chicago, IL, 60015
4. Steve Smith, 123 Main St, Chicago, IL, 60015

and end up with these records:

RECORD NUMBER, NAME1, NAME2, NAME3, ADDRESS, CITY STATE, ZIPCODE
1. Jack Jones, Sarah Jones, Steve Smith, 123 Main St, Chicago, IL, 60015
2. Alice Jones, 432 Main St, Chicago, IL, 60015

Basically I want to "household" my list with no limit on the number of names that could be in one record. I don't know if this is easy or not but I am sure someone has done it in the past. Thanks for any help you can give me. Chuck

 

[79ssecca]

In order to accomplish what you want you need to understand normalization. Normalization is the breaking up of data into separate units of like data which is then stored in separate tables. Fully normalized table sin you scenario would consist of three tables

Tables:
Names Table - Holds all the names of the clients or entities
Address Table - Holds Address line information only
Zip Table - Holds City and Zip Code Information

NOTE:
(Some people might include city and zip into the address table)

Now create the appropriate foreign key relationships. The address table needs a one to many relation ship to the names table so that one address can be linked to many address. Do this by including the Primary Key value from the address table into the name table individual record.

I.E.

Name Table
recID Lname Fname AddID
1 West Adam 1
2 West Mae 1
3 Smith John 2


Address Table
AddID Address1 Address2 State ZipID
1 Wild West Road Wy 3
2 Suite 5 62 Main St. NY 2



Zip Table
zipID City ZipCode
1 Wasington 20200
2 New York 11111
3 Laredo 34567
4 New York 22222


Once you have a normalized environment everything else flows easy.

Good Luck
79ssecca

 

[IATAILG8ER]

Thanks 79ssecca for your help,

I just now was able to try your suggestion, but am still having a problem. I created two tables one a name table and one a address table. The address table has a one to many relationship with the name table. How did you assign the AddID field to the tables? Please be patient with me, as this is probably something easy to do but I still don't understand what you are telling me. Thanks Chuck