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

 

[Rick B]

Pleas edon't keep posting the same question. When someone knows the answer,
they will respond. No need to post it multiple times.

 

[Guest]

Rick,

Thanks for the comment, but everytime I try to post, it gives me an error
posting your message, please try again later. So I keep trying! Sorry for the
trouble.

 

[PC Datasheet]

1. Create a query that gives you total material dollars per project
2. Create a query that gives you total payments per project
3. Create a query that includes the queries in 1 and 2, your projects table
and contractors table. Join all the objects on ProjectID. Contractors table
should only be needed to provide the contractor's name for each project.

 

[Duane Hookom]

Why would you want to include all the tables in one query? Your records are
usually displayed in a form or report where you could use subforms or
subreports for related records.

 

[John Vinson]

Rick,

Thanks for the comment, but everytime I try to post, it gives me an error
posting your message, please try again later. So I keep trying! Sorry for the
trouble.

It's a bogus error message; LOTS of people using the (buggy!) web
interface are getting bit by this. The message is in fact posted. Note
also that if you want to post in two newsgroups (rarely necessary)
it's more polite to crosspost than to multipost - put both newsgroup
names in the Newsgroups line. That way people can tell if the question
has been answered in another newsgroup.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps