Multiple page subreport cuts off in report header

[wjfraser]

Hello,
I have a report with a subreport in its header. When I run the subreport by itself, it shows multiple pages, correctly. When I view the main report, the subreport prints to the bottom of the first page and does not go on to display the rest of its records - the records of the main report begin on the second page. I have tried setting the CanGrow property of everything to "yes" and experimented with lots of the other properties. Does anyone know what might be causing this?

Thanks,
Whitney

[MMcCarthy]

Hello,
I have a report with a subreport in its header. When I run the subreport by itself, it shows multiple pages, correctly. When I view the main report, the subreport prints to the bottom of the first page and does not go on to display the rest of its records - the records of the main report begin on the second page. I have tried setting the CanGrow property of everything to "yes" and experimented with lots of the other properties. Does anyone know what might be causing this?

Thanks,
Whitney

Why is the subreport in the report header

What field is in common between the header section and the subreport?

[wjfraser]

Why is the subreport in the report header

What field is in common between the header section and the subreport?

The report is a list of projects. The subreport is a list of bios for all staff who have worked on the projects, so it needs to occur just once, at the beginning of the report. After a bit more experimentation , it looks like the subreport is only displaying the first record (no matter how short or long the bio is - so, even if there is space left on the page, it won't display any more records BUT if the first record is more than a page long, the one record runs quite nicely onto the next page).

The flow of this project is:
User selects one or more staff members, and some key words.
Report displays all projects that the staff member has worked on by filtering the keyword query by the "ProjectSta ff" field (this part works just fine).
There is a SELECT DISTINCT query on [ProjectStaff] that feeds into the subreport.

Thanks for any help.

[MMcCarthy]

There is a SELECT DISTINCT query on [ProjectStaff] that feeds into the subreport.

What is the master/child or filter relationship between the Group Header which I assume is based on ProjectStaff and the subreport?

[wjfraser]

What is the master/child or filter relationship between the Group Header which I assume is based on ProjectStaff and the subreport?

Yes, the Group Header is based on ProjectStaff, but I don't think the grouping has a direct relationship with the subreport.

When I look at the subform properties, I see
Link Child Fields......Pro jectStaff
Link Master Fields...Projec tStaff

Is there another place to look for master/child relationships?

[MMcCarthy]

Yes, the Group Header is based on ProjectStaff, but I don't think the grouping has a direct relationship with the subreport.

When I look at the subform properties, I see
Link Child Fields......Pro jectStaff
Link Master Fields...Projec tStaff

Is there another place to look for master/child relationships?

In this case the subform is displaying only the record that has a direct relationship based on ProjectStaff being equal. As this subform is based on a DISTINCT query it will only return one record. If you remove the DISTINCT it will return all bios for this relationship.

[wjfraser]

In this case the subform is displaying only the record that has a direct relationship based on ProjectStaff being equal. As this subform is based on a DISTINCT query it will only return one record. If you remove the DISTINCT it will return all bios for this relationship.

I left out some of the process because I thought it would confuse things...but I think I need to lay it all out. The first time I tried to make this subreport, Access truncated the bios because they were involved with the SELECT DISTINCT query. So now I run the staff list (one or more staff members listed for each project) through a SELECT DISTINCT query first, then match the resulting list with the bios from a master staff list. Like this:

qryDistinctStaf f:

SELECT DISTINCT [qryReport].[ProjectStaff]
FROM qryReport;
 

qryStaffBioRepo rt (source of the subreport):

SELECT [qryDistinctStaff].[ProjectStaff], [tblMasterStaffList].[StaffBio]
FROM qryDistinctStaff INNER JOIN tblMasterStaffList ON [qryDistinctStaff].[ProjectStaff]=[tblMasterStaffList].[ProjectStaff];
 

Taking the "distinct" out of the first query does result in multiple records being displayed, with an interesting side effect. There are the correct NUMBER of records, but they are all identical (they pull the first person's name and bio and repeat it). I will play with it a bit more but if you have input that would be great.

Thanks.

[MMcCarthy]

OK to clarify

A group header based on projectstaff will display only one projectstaff value.

The subreport, no matter what the query will only display the record or records where the projectstaff value is the same as in current value in the group header.

Therefore, the first instance of "John Doe" will show bio for "John Doe" only in subreport.

The second instance of "Mary Black" will show bio for "Mary Black" only in subreport.

Does this make sense?

Try this query instead, should give similar results to your two queries:

qryStaffBioRepo rt (source of the subreport):

 
SELECT [qryReport].[ProjectStaff], [tblMasterStaffList].[StaffBio]
FROM qryReport INNER JOIN tblMasterStaffList 
ON [qryReport].[ProjectStaff]=[tblMasterStaffList].[ProjectStaff]
GROUP BY [qryReport].[ProjectStaff], [tblMasterStaffList].[StaffBio];
 
 

[MMcCarthy]

One further thing...

If there is more than one bio for each staff member amend query as below:

qryStaffBioRepo rt (source of the subreport):

 
SELECT [qryReport].[ProjectStaff], [tblMasterStaffList].[StaffBio]
FROM qryReport RIGHTJOIN tblMasterStaffList 
ON [qryReport].[ProjectStaff]=[tblMasterStaffList].[ProjectStaff]
GROUP BY [qryReport].[ProjectStaff], [tblMasterStaffList].[StaffBio];
 
 

This will return all bios for each projectstaff member.

[wjfraser]

A group header based on projectstaff will display only one projectstaff value.

That does make sense. But the subreport is in the report header, not the group header.

Try this query instead, should give similar results to your two queries:

qryStaffBioRepo rt (source of the subreport):

 
SELECT [qryReport].[ProjectStaff], [tblMasterStaffList].[StaffBio]
FROM qryReport INNER JOIN tblMasterStaffList 
ON [qryReport].[ProjectStaff]=[tblMasterStaffList].[ProjectStaff]
GROUP BY [qryReport].[ProjectStaff], [tblMasterStaffList].[StaffBio];
 
 

This did not work - it stopped printing the bio altogether.