Complex Query... I think!

[Guest]

Hi all,

I have a chain of linked tables in one to many relationship as such:
Customers Table is the main table with these tables linked to it:
1. Payments
2. Projects

Projects Table has two tables linked to it:
1. Materials
2. Contractors

I would like to get a query that shows:
1. Customer info
2. Payments info
3. Projects info
4. Materials info
5. Contractors info

All these tables have money amounts with the exception of the Project table.
I want to show all amounts and payments on one report.

When I create a query with all these fields, I will get repeated info. For
example, I will have a Materials Cost field repeated many times to match the
rows of the Contractor Cost field since the Contractors cost has more records
than the Materials cost.

I would appreciate some help please. Thanks

 

[Dale Fye]

How do you want to use this (report or form). I think your best bet is to
use subforms, or subreports to see the details for a particular project.
Or, If all you want is to compute the amounts and payments, create
subqueries of those tables with totals queries, based on customer ID, then
join them to the customers table.