sub-summary report from multiple tables

[nelsonkh]

I need some help with setting up a sub-summary report, which is pulling data from different tables. I know (after my last problem with this) that the sub-summary fields have to be from the table that you create the layout in. My problem is that I have a bunch of different tables that I would like to use data from to create my report, and I'm not sure if it is possible or if I did something terribly wrong when setting up.

I have 4 tables - Customer, Contract, Labor, Material. They are all linked by Customer ID. Contract, Labor & Material all have fields for type of work (so we can assign costs for material & labor to profit centers and line items from contracts as revenue to profit centers). I would like to be able to create a report that tells me what our GP was PER TRADE (profit center / type of work) but cannot figure out how to do this.

I've attached a file - this is basically how I have my file set-up. The "GP Report by Job" is what I'm trying to accomplish, but you can see that it's not working out. It assigns all of the job costs for that job to every contract line item, I'm not sure how to properly use my "type of work" fields in the Material & Labor tables to create the data I'm looking for.

Any help is much appreciated - I've spent too much time thinking about this - I'm sure that it's something simple (I hope, at least)!

Edited September 23, 2008 by Guest