Report From Mulitple Child Tables

[bhakta.ram]

Hi -
I'm wondering if there's a way to include, on one report, records from
more than one child table.

Example: I have a Shipment table, which is related (many-to-many) to a
Product table through a join table. The Shipment table is also related
(again, many-to-many) to a CartonLabel table through a join table. I
want to create a report, summarized by shipment, that includes both
products and carton labels.

If I only had the Shipment - Product relationship, I could create the
report from the join. But I have five child tables that need to be
summarized. Is it possible to report both products and carton labels,
summarized by shipment, at once?

Thanks!

 

[Ken Sheridan]

Base the parent report on the Shipments table, or a query on it, and then
include subreports in the detail section, each subreport based on one of the
child tables, or in the case of a many-to-many relationship on a query which
joins the two relevant tables. Link each subreport to the parent report by
setting the LinkMasterFields and LinkChildFields properties to ShipmentID or
whatever the keys are named.

You can of course lay out each subreport independently, e.g. you might want
some of them to be multi-columned. However, if you do this, note that there
has been a bug in Access since Access 97 (and not yet fixed AFAIK) which
prevents 'down then across' columned reports working as subreports. 'Across
then down' works fine though.

Ken Sheridan
Stafford, England