Reports One to Many Relationship

[jtlawn]

I am trying to create a report using three different tables, with a one to many relationship. If I just create the query I get the records repeated a number of times.
What I am looking for: At the top of the report Customer name, address etc... Next I would like to show the labor costs for the job, could be one row could be twenty. Next I would like to show material costs, again could be one or twenty rows. At the bottom I would like to have a grand total line showing Total sale price, total labor, total material, labor and material as a percentage of sale price.
Do I need to create three seperate reports, then create a single report with three subreports?

Thank You
Joe Lawn
jtlawn@hotmail.com

 

[AndyCole]

I assume that the 1 to many relationships exist between the 'Customer job data' table and the 'labour costs data' table and also between the 'Customer job data' table and table 'material costs data' table. In this case tyou could get away with 2 sub-reports (1 for Labour costs and 1 for Materual costs) and then a main report containing the Customer Job data with the 2 sub-reports beneath (don't foget to set the 'can grow' and 'can shrink' properties on the sub-report 'containers' in the main report). The summaries could be part of the main report getting their data from 'hidden' running sum fields on the sub-reports.

Cheers

Andy

 

[nberryman]

If you use the Report Wizard you can Group By up to 4 fields.

Run the Wizard based on your Query and Group By Customer (or Job) then Labour and then Material

The next windows allow you to sort and has a Summary option button where you can specify the % that you want.


Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[jtlawn]

Thank You,

I will try these options and let you know how they work Joe Lawn
joe_lawn@cardinalbuilders.com