Merging records that have a unique link between the two records but in different fields

[MSommer]

Is it possible to combine or merge records where in the same table and row, there is a unique ID for the record then also a unique relationship ID in the record which matches another record in the table. I have an urgent need to combine like household records into one record. See table data below reflecting the matching unique IDs. The Individual ID in the first column can match the Relation Individual ID in the sixth column on a different record. I need to concatenate the first names from each matching record as well.

[Micron]

Don't think I'd do that to the table itself. Perhaps create a query and drag that table into it 2x. Put an equal join between 1st and 6th fields. Use a calculated query field to do your concatenation (with what I don't know). Include any other fields that you need in the query.

[Welshgasman]

Wouldn't Allen Brownes Concat do that, or theDBGuys?

http://allenbrowne.com/func-concat.html
http://www.accessmvp.com/thedbguy/co...itle=simplecsv

[MSommer]

Agreed. I did exactly that but don't have a criteria calculation to combine the records. Concatenating the name is the easy part. Doing this manually for hundreds of records would be labor intensive.

[Welshgasman]

Agreed. I did exactly that but don't have a criteria calculation to combine the records. Concatenating the name is the easy part. Doing this manually for hundreds of records would be labor intensive.

Was that addressed to my post?
Your example is pretty poor, as it seems the next record is linked to the prior record, and vice versa?

If the first key was a family and the relation key the family members, then you would get all those members for that particular family?
You show only one?

[June7]

Provide mockup sample of what you want as output. Can use Excel.

A simple self-join will relate the records, however, each pair will be duplicated because of the way your data is structured.

SELECT Table1.[Individual ID], [Table1].[First Name] & " " & [Table1].[Last Name] AS Frst, [Table1_1].[First Name] & " " & [Table1_1].[Last Name] AS Scnd
FROM Table1 AS Table1_1 INNER JOIN Table1 ON Table1_1.[Individual ID] = Table1.[Relation Individual];

Table1.Individual ID	Frst	Table1_1.Individual ID	Scnd
40337523	B Aders	40337503	A Aders
40337503	A Aders	40337523	B Aders
39666988	B Allred	39666918	A Allred
39666918	A Allred	39666988	B Allred
38918338	B Anderson	38918290	A Anderson
38918290	A Anderson	38918338	B Anderson
38531509	B Barker	38531774	A Barker
38531774	A Barker	38531509	B Barker

SELECT Table1.[Individual ID], Table1_1.[Individual ID], Table1.[Last Name], [Table1].[First Name] & " & " & [Table1_1].[First Name] AS Frst
FROM Table1 AS Table1_1 INNER JOIN Table1 ON Table1_1.[Individual ID] = Table1.[Relation Individual];

Table1.Individual ID	Table1_1.Individual ID	Last Name	Frst
40337523	40337503	Aders	B & A
40337503	40337523	Aders	A & B
39666988	39666918	Allred	B & A
39666918	39666988	Allred	A & B
38918338	38918290	Anderson	B & A
38918290	38918338	Anderson	A & B
38531509	38531774	Barker	B & A
38531774	38531509	Barker	A & B

Family members don't always have same last name.