combine output from two child tables in a report

[luke.999]

Hi guys,

Here is my scenario. I have three tables: Cabinets, Doors, Drawers.
Cabinets table is a parent of both Doors and Drawers. All three tables
contain cabinetID which is a primary key in Cabinets and foreign key in
Doors and Drawers.

Each cabinet can have 0 or more Doors and 0 or more Drawers.

Now what I want to print in a report is:

Group by cabinet and then display all doors and drawers side by side
for that cabinet.

Like this:

cabinet1
door1 drawer1
door2
door3

cabinet2
drawer1
drawer2

cabinet3
door1 drawer1
door2 drawer2
drawer3

etc.

How would I go about creating a report like that? I'm totally confused
about how to join the tables to achieve this. Can I somehow use two
queries in a report maybe?

I'm desperate, please help

Luke

 

[Guest]

hi...the key is to create a Query ...and then base your Report on this.

in new Query design mode....add all 3 tables to the design...and add the
fields from each table to the Query.

bottom line is that when you run the Query with ! that it returns the raw
results that you want.....of course this is pure records without the format
design you want but that is the Report function to make it look the way you
want...

then create the report based on this query...in Report Wizard you will be
prompted for grouping which will allow you to establish the layout you want...

 

[John Vinson]

Can I somehow use two
queries in a report maybe?

I'd suggest using a Report based on Cabinets, with two side-by-side
Subreports based on Doors and Keys respectively.

Since there is no relationship between the two subtables, it's hard to
come up with a single query to get the layout you want, but Subreports
will do so easily.

John W. Vinson[MVP]

 

[luke.999]

I'd suggest using a Report based on Cabinets, with two side-by-side
Subreports based on Doors and Keys respectively.

Since there is no relationship between the two subtables, it's hard to
come up with a single query to get the layout you want, but Subreports
will do so easily.

John W. Vinson[MVP]

Thanks John, that worked perfect.