Complicated Query

[Grumm]

Hi, I have a database for tracking costs of construction project. I have 3 tables that hold the contract estimate information. I used to hold the measured works and prelims on the same table but split them out as they are different entities with different information.

tblContract
ContractID
Name
StartDate
Weeks
Desc
CustometID

tblPrelims
ContractPrelimID
ContractID
Item Number
Description
Units
UnitCost

tblMeasuredWorks
ContractMeasuredWorkID
ContractID
Item Number
Description
Area
UnitCost
Bracket%
Panel%
Finishing

The Contract table is related to the other tables by ContractID.

What I want to do is build a query that will take the following from each table so I can create a Contract Valuation report with all information:

Item Number
Description
Area/Units
UnitCost
TotalCost (units * ItemCost)

I am really struggling with the joins.

Should I be creating a temp table, running one query with an subsequest appent query?

What is the best method to achieve this. Untimatly I want the report to show totals for measured works and prelims and then a grand total.

Any help would be greate.

Cheers

 

[shannonp]

tblContract one <--to--> many tblPrelims
tblContract one <--to--> many tblMeasuredWorks

Are your tables related correctly? If they were your relationships would have been created automatically when you added them to a query.

 

[shannonp]

Forgot to add.

Also you may want to change your join properties

tblContract one <--to--> many tblPrelims

Include ALL records from 'tblContract' and only those records from 'tblPrelims' where the joined fields are equal


tblContract one <--to--> many tblMeasuredWorks

Include ALL records from 'tblContract' and only those records from 'tblMeasuredWorks' where the joined fields are equal

Let me know if want I have posted doesn't make sense.