Guidance required for report design

[Green Biro]

I have a report for which the recordsource is my main table

The report contains three subreports, which are all lists of records from
tables that have a one to many relationship with my main table. All very
easy, just linking the main_id to the foreign key for each subreport.

My question is, "How can I get my report to skip the main record whenever
NONE of the subreports have related records?"

I'm thinking that maybe an approach would be to change the recordset of the
main report to a query that includes the subTables. I haven't done this yet
for two reasons:
1) It seems a bit inefficient to query the subTables at the top level when
they are being queried again for each subReport
2) I'm not exactly sure how to set up a query that checks for the existence
of records in any of three subTables.


Please can someone give me some guidance on the best way forward.

Thanks

GB

 

[Larry Linson]

Create a Totals Query for each of the related tables, Group By the foreign
key to your main table, for a Count of records. Now create the Query that
you'll use as RecordSource for the report, with your main table, joined on
its primary key to each of the three Totals Queries. Along with the data
from the main table, pull down the counts and use them to create a
calculated field with the total of counts, and in the criteria line of that
calculated field, use >0. That will eliminate those records for which there
are no related records. You should then, be able to simply replace the
Table with your new Query as RecordSource for the Report.

Larry Linson
Microsoft Office Access MVP

 

[Green Biro]

Thank you. That very nearly did it. The subreports no longer show but the
main report records still show.
How can I get it to skip the whole record entirely when there is no data
from the subreports to show?

GB

It can be done quite easily in the report itself. In the Detail section's
Format event procedure put:

Cancel = Not Me.[FirstSubReport].Report.Hasdata _
And Not Me.[SecondSubReport].Report.Hasdata _
And Not Me.[ThirdSubReport].Report.Hasdata

Note that FirstSubReport etc are the names of the subreport *controls* in
the
parent report, i.e. the controls housing each subreport, not the names of
their underlying report objects. Unless the names of the controls and
their
source objects are the same of course.

Ken Sheridan
Stafford, England

I have a report for which the recordsource is my main table

The report contains three subreports, which are all lists of records from
tables that have a one to many relationship with my main table. All very
easy, just linking the main_id to the foreign key for each subreport.

My question is, "How can I get my report to skip the main record whenever
NONE of the subreports have related records?"

I'm thinking that maybe an approach would be to change the recordset of
the
main report to a query that includes the subTables. I haven't done this
yet
for two reasons:
1) It seems a bit inefficient to query the subTables at the top level when
they are being queried again for each subReport
2) I'm not exactly sure how to set up a query that checks for the
existence
of records in any of three subTables.

Please can someone give me some guidance on the best way forward.

Thanks

GB

 

[Green Biro]

Just looked into it a bit further.

The problem is that I have a detail header and footer. That is what is
still printing.

I tried to put your code in the format events of these two sections as well
but that didn't work.

Hopefully it's just something simple that has to be changed to acomplish
what I want.


GB

Thank you. That very nearly did it. The subreports no longer show but
the main report records still show.
How can I get it to skip the whole record entirely when there is no data
from the subreports to show?

GB

It can be done quite easily in the report itself. In the Detail
section's
Format event procedure put:

Cancel = Not Me.[FirstSubReport].Report.Hasdata _
And Not Me.[SecondSubReport].Report.Hasdata _
And Not Me.[ThirdSubReport].Report.Hasdata

Note that FirstSubReport etc are the names of the subreport *controls* in
the
parent report, i.e. the controls housing each subreport, not the names of
their underlying report objects. Unless the names of the controls and
their
source objects are the same of course.

Ken Sheridan
Stafford, England

I have a report for which the recordsource is my main table

The report contains three subreports, which are all lists of records from
tables that have a one to many relationship with my main table. All very
easy, just linking the main_id to the foreign key for each subreport.

My question is, "How can I get my report to skip the main record whenever
NONE of the subreports have related records?"

I'm thinking that maybe an approach would be to change the recordset of
the
main report to a query that includes the subTables. I haven't done this
yet
for two reasons:
1) It seems a bit inefficient to query the subTables at the top level
when
they are being queried again for each subReport
2) I'm not exactly sure how to set up a query that checks for the
existence
of records in any of three subTables.

Please can someone give me some guidance on the best way forward.

Thanks

GB

 

[Green Biro]

That's it. Thank you.

As you say, there was no need for a group header and footer where there was
only one record in the group. I simply moved everyting I had there into the
detail section and now your code does exactly what I want it to.

Thanks again.

GB

You'll have to explain. You can only have group or report headers and
footers, not detail headers and footers. If you mean you are grouping on
a
field of distinct values returned in the detail section, then that's fine
for
sorting the report, but there is no point giving the group level a header
or
footer as each group can contain only one record from the parent report's
underlying recordset, so any controls to precede or follow the controls
in
the section can simply be placed in the detail section itself, at top and
bottom. The code to conditionally cancel the section then goes in the
detail section's Format event procedure.

In any event you can't cancel a header on the basis of a subreport in the
detail section not having data as the relevant instance of the subreport
has
not yet been invoked. The code would in fact examine the previous
instance
of the subreport. To complicate matters further, if you are referencing
the
Pages property somewhere (usually in a page footer) this forces two
iterations through the report, so code in the first instance of the header
would, bizarre as this may seem, actually reference the last instance of
the
subreport!

Ken Sheridan
Stafford, England

Just looked into it a bit further.

The problem is that I have a detail header and footer. That is what is
still printing.

I tried to put your code in the format events of these two sections as
well
but that didn't work.

Hopefully it's just something simple that has to be changed to acomplish
what I want.

GB

Thank you. That very nearly did it. The subreports no longer show but
the main report records still show.

[quoted text clipped - 47 lines]

GB