Merging Records Having Common Field

[aman]

Hi All

I have many records in a query in ms access where there is a contact at address x in one record and then I have another contact that is also at address x (spouse) in a different record. I want to merge the two contacts and create one new record that has contact1firstname, contact1lastname, contact2firstname, contact2lastname, address1, city, state, zip.

Please find enclosed the attachment that contains the exact data but actually i want coding in access as my table is in access exactly.

Is this possible to solve it using mail merge or VBA coding is required for this.

Can anyone help shed some light on this? I am intermediate with access but can't figure it out. Thanks in advance for any assitance

 

[Starman]

You are thinking like a spreadsheet when you need to be thinking like a database. You don't want the spouses to be on the same record. You want each person to have their own record and then you can use their key to link them.

 

[Mutdogus]

What Starman is saying is you need to understand a term called "NORMALIZATION".

You need 3 tables, one for your "Employee" ? One for your employee's dependents, and one as a relational table for your employee and his dependants, unless you are sure you will only want information about his spouse. Then you need 2 tables and a field in your Employee's table to match up with your employee's spouses primary key.

 

[aman]

Hi Starman and Mutdogus

Thanks for your reply. But please let me explain what I want exactly.I have to send letters to the families every month. If more than one member of the same family is in the database then many letters are sent to the same family .But what I want is " To send one letter to one family in the following format:

Mr and Mrs Smith
22,Conygre Grove
Bristol
Uk.

Could you please put me in the right direction how can i solve this.
My database is in MS Access and 2 or more records of the same address are in the database.

 

[Mutdogus]

If you use the model I described, this could be accomplished easily.

I am assuming you are tracking employees?

tblEmployee
  EmpoyeeID
  FirstName
  MiddleName
  LastName
  AddressID

Dependant
  DependantID
  FirstName
  MiddleName
  LastName
  AddressID

tblAddress
  AddressID
  StreetAddress
  ZipCode

tblEmployeeDependants
   RecordID
   EmployeeID
   DependantID

Now you see you will still have all the information you need about their spouse in the Dependant table and you will only have one record in the Employee table. Unless of course you have husband and wife that work there then you need to use a flag or come up with some other method. I would put the flag on the address table and set and clear it after the letter was printed with VBA.

Or you could just include only the LastName from the tblEmployee and group the results, which will give you a 99% solution unless you have a lot of people that live at the same address, are in the same family, and have different last names.