Report Duplicates

[fmulvaney via AccessMonster.com]

I'm trying to create a report using 2 tables. I've tried writing queries from
the tables as well but I get the same problem. Tables are set up as follows:

Table 1 has Employee Name (EN) and Employee Social Security Number (ESSN)
like this

Col1 Col2
ESSN EN
xxx-xx-xxxx Joe smith

Table 2 has ESSN and Dependent Name (DN), Dependent Relationship (DR) and
Dependent SSN last four digits (DSSN) like this

Col1 Col2 Col3 Col4 Col5
ESSN EN DN DSSN DR
xxx-xx-xxxx Joe smith Mike Smith xxxx Child
xxx-xx-xxxx Joe smith lisa smith xxxx spouse
xxx-xx-xxxx Emily jones phil jones xxxx child
xxx-xx-xxxx Emily jones alicia jones xxxx child

These 2 tables are linked by ESSN. My main table is the list of employees.
Everything should drive off this list. I'd like to create a report that
shows at the top the employee name and below that each dependent for that
employee. When I try to do this I get each dependent name repeated several
times(dependents repeat the number of times the ESSN is listed in the table)
rather than just once. The report should look like

Joe Smith

Mike smith child
lisa smith spouse

(new page)

Emily Jones

phil jones child
emily jones child

Instead I'm getting

Joe smith

Mike smith child
Mike smith child

Any help is greatly appreciated.

 

[fmulvaney via AccessMonster.com]

Thank you very much!

I'm trying to create a report using 2 tables. I've tried writing queries from
the tables as well but I get the same problem. Tables are set up as follows:

[quoted text clipped - 41 lines]

Mike smith child
Mike smith child

The only way I can think of getting that effect is if Joe
Smith has multiple entries in table1.

You should not have the employee name in table2. The ESSN
field is sufficient.

I think this kind of query should do what you want:
SELECT table1.ESSN, table1.EN, table2.DN, table2.DR
FROM table1 LEFT JOIN table2
ON table1.ESSN = table12.ESSN

Put the report on hold until you get a query to produce the
right dataset.

Please post a Copy/Paste of your query's SQL view if you
have any further questions about it.

 

[Marshall Barton]

I'm trying to create a report using 2 tables. I've tried writing queries from
the tables as well but I get the same problem. Tables are set up as follows:

Table 1 has Employee Name (EN) and Employee Social Security Number (ESSN)
like this

Col1 Col2
ESSN EN
xxx-xx-xxxx Joe smith

Table 2 has ESSN and Dependent Name (DN), Dependent Relationship (DR) and
Dependent SSN last four digits (DSSN) like this

Col1 Col2 Col3 Col4 Col5
ESSN EN DN DSSN DR
xxx-xx-xxxx Joe smith Mike Smith xxxx Child
xxx-xx-xxxx Joe smith lisa smith xxxx spouse
xxx-xx-xxxx Emily jones phil jones xxxx child
xxx-xx-xxxx Emily jones alicia jones xxxx child

These 2 tables are linked by ESSN. My main table is the list of employees.
Everything should drive off this list. I'd like to create a report that
shows at the top the employee name and below that each dependent for that
employee. When I try to do this I get each dependent name repeated several
times(dependents repeat the number of times the ESSN is listed in the table)
rather than just once. The report should look like

Joe Smith

Mike smith child
lisa smith spouse

(new page)

Emily Jones

phil jones child
emily jones child

Instead I'm getting

Joe smith

Mike smith child
Mike smith child

The only way I can think of getting that effect is if Joe
Smith has multiple entries in table1.

You should not have the employee name in table2. The ESSN
field is sufficient.

I think this kind of query should do what you want:
SELECT table1.ESSN, table1.EN, table2.DN, table2.DR
FROM table1 LEFT JOIN table2
ON table1.ESSN = table12.ESSN

Put the report on hold until you get a query to produce the
right dataset.

Please post a Copy/Paste of your query's SQL view if you
have any further questions about it.