Subreport in detail section of a continuous report?

[DKO]

Is it possible to imbed a subreport into the detail section of a continuous report and enforce a one to many relationship?

I defined the relationship between the query that the main (continuous) report is based on and the query that the subreport is based on as one to many - then I embedded the subreport as described above.

The problem is that it's showing duplicate records on the main report - one for each corresponding record in the subreport.

Is there any way to get around this? A way to define the relationship between the report and subreport?

The report should show one record per employee. (must be continuous)
The subreport should show family member data for each employee - (name, relationship, date of birth and age for each dependant)

I know an easy fix would be to utilize the grouping of the report to move the employee data to a new header section and put the family member data in the detail section so each dependant would have his/her own line (which they should) - but my boss insists that it must all stay on "one line"... :banghead:

The only other way I can think of would be to store all the dependant data for each employee in one memo field which makes me sick.

 

[AccessMSSQL]

So how is the report supposed to look? Like an excel spreadsheet with no header section? Just create one query that links the employee table to the dependents table. If everything must stay on one line just create a simple report based on the query??

You would have something like:
EmployeeName1 DependentName Age Relationship DateOfBirth
EmployeeName1 DependentName2 Age Relationship DateOfBirth

An employee with multiple dependents would have multiple lines. What is it you are trying to store in the header section of the report??

 

[DKO]

The "one line per dependent" is what I'm trying to avoid. I'm looking for a way to list more than one dependent per line - only displaying the rest of the employee data once, not duplicated on multiple lines. To the user, it should basically appear as a text box that lists all the dependents.
Just imagine a normal form or report that displays one record per page, with a subform/report showing multiple records that are related. I want to do the same thing on a continuous report.
I'm pretty sure it's not possible, I was just hoping someone had a work-around.

 

[AccessMSSQL]

First off, forms and reports are completely different animals, so you are referring to the two as if they were the same. You can have a form that is a single form (displaying one record) , datasheet mode, or continous. A Report doesn't have a continous mode or single record mode, etc.

In any event if you want one row of data per employee listing all dependents are you looking for a cross tab type report??

Employee Dependent1 Dependent2 Dependent3
John Doe Jane Doe Tom Doe Jan Doe

Or something like this:

Employee Dependent(s)
John Doe *Jane Doe
********Tom Doe
******** Jan Doe

(ignore asteriks - just put those in for formatting so you can see that Tom, Jan belong under the Dependent(s) column)

 

[DKO]

First off, forms and reports are completely different animals, so you are referring to the two as if they were the same. You can have a form that is a single form (displaying one record) , datasheet mode, or continous. A Report doesn't have a continous mode or single record mode, etc.

In any event if you want one row of data per employee listing all dependents are you looking for a cross tab type report??

Employee Dependent1 Dependent2 Dependent3
John Doe Jane Doe Tom Doe Jan Doe

Or something like this:

Employee Dependent(s)
John Doe *Jane Doe
********Tom Doe
******** Jan Doe

(ignore asteriks - just put those in for formatting so you can see that Tom, Jan belong under the Dependent(s) column)

Something more like the second example. Thanks for the help.

 

[AccessMSSQL]

You can do this easily. You are still going to have one row per dependent but you just won't show the employee data on subsequent rows, correct? In the report go to the properties window of any Employee field you don't want to duplicate down the page. There is a property called Hide Duplicates. Choose Yes for any employee related fields you don't want duplicated down the page.