Reports with subreports

[Paradigm]

I have a report with staff names in a staffid header. In the detail section
I have a number of subreports (linked via parent and child IDs). One is
employment history, another is work experience, another is qualifications,
etc.
My problem is that, for some staff e.g. secretaries, there may be no data
for these sub reports. I want to print a continuous report for all staff BUT
leave out the staff for which there would be no subreport.
I know I can use the OnNoData event on the subreports but how can I use this
to get the main report to skip the staff names and some other details. I
would have to check all the subreports and only when there is no data in all
of them skip that member of staff.
The report is used to give to prospective clients.
Alex

 

[Guest]

Have your main report based on a query. Use criteria in the query to not
pull records if there is no data for the subreport.

 

[Allen Browne]

You need as series of subqueries in the WHERE clause of the query that feeds
your main report, so that those who have nothing in any of the related
tables never make it into the report.

The query will end up something like this:

SELECT Staff.* FROM Staff WHERE
(EXISTS (SELECT ID FROM EmployHistory WHERE EmployHistory.StaffID =
Staff.StaffID)
OR EXISTS (SELECT ID FROM WorkExperience WHERE WorkExperience.StaffID =
Staff.StaffID)
OR EXISTS (... ));

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Paradigm]

Thanks. I will give that a try.
Alex

You need as series of subqueries in the WHERE clause of the query that feeds
your main report, so that those who have nothing in any of the related
tables never make it into the report.

The query will end up something like this:

SELECT Staff.* FROM Staff WHERE
(EXISTS (SELECT ID FROM EmployHistory WHERE EmployHistory.StaffID =
Staff.StaffID)
OR EXISTS (SELECT ID FROM WorkExperience WHERE WorkExperience.StaffID =
Staff.StaffID)
OR EXISTS (... ));

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066