Self Join help

[Scott Black]

Hello and thanks in advance.

I have a list of employees with SSN as the primary key and I have a
Dependent Master list with employee's SSN and dependent information (Lname,
Fname, DOB, relation). There are multiple lines for each dependent of an
employee in the Dependent Master yet I want to put them all on one line with
the employee name and SSN. Hope my intentions make sense for everyone.

I am working on a self join but not successful so far. Your help is greatly
appreciated.

Thanks,
Scott

 

[[MVP] S.Clark]

For starters, SSN's are not unique, as they are recycled every hundred years
or so.

Add the same table to the query twice. Link the two by the field. Change
the join properties to be a right outer, such that those parents without
children will also be displayed. Otherwise, you will only get those parents
that have children.

 

[Scott Black]

Thanks for your help Steve. However, this gave me duplicate rows and a
different row for each dependent. How can I get the employee with all
dependents on the same row as the employee?

Any suggestions.

Thanks again,

Scott

 

[John Vinson]

Thanks for your help Steve. However, this gave me duplicate rows and a
different row for each dependent. How can I get the employee with all
dependents on the same row as the employee?

Only with some VBA code. For an example, see

http://www.mvps.org/access/modules/mdl0004.htm