Complex Query...Maybe

[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

 

[Brian]

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

It's logically impossible to do this with a single query: the "duplication"
you have observed is unavoidable.

You mentioned wanting a report. What you could do is create a report based
on customers, with sub-reports for payments and projects. The projects
sub-report would in turn have sub-reports for materials and contractors.