Merging fields in one row into another row

[kb3zq]

I have a set of data listing Employee SSN, Employee Name, Dependent SSN, Dependent Name.
There is a separate row for each dependent an employee has.
I need to identify each duplicate Employee SSN and put all Dependent information in just one row.

Example:
The tables I am using are set up in this manner.
Employee SSN l Employee Name l Dependent SSN l Dependent Name
123456789 l John Smith l 456456456 l Jane Smith
123456789 l John Smith l 789789789 l Greg Smith

I need to know how to set up a query that will give the following results:
Employee SSN l Employee Name l Dependent1 SSN l Dependent1 Name l Dependent2 SSN l Dependent2 Name
123456789 l John Smith l 456456456 l Jane Smith l 789789789 l Greg Smith

Thanks.

[CJ_London]

suggest you take a look at the concatrelated function and adapt to your needs
http://allenbrowne.com/func-concat.html

alternatively you need an additional column to indicate whether the dependent is 1st, 2nd, 3rd etc

and use two crosstab queries joined together

[orange]

kb3zq,

Why is this output format required?

Since you are dealing with Employees, your application may need more info about each Employee.
If that is the case, you may want to consider this table structure.

Employee------>Dependent

where an Employee may have 0,1, or many dependents.