Query

[Guest]

I have 3 tables (Customers, Contacts, Products) whose relationship is one to
many.

If I want to use query based on all three tables, it returns duplicate rows.

eg If I have one customer with two contacts and 3 products, the query
returns 6 records.

What should I do so that the query returns one customer, 2 contacts and 3
products.

Appreciate your response.

 

[Guest]

Post your SQL so it can be analyzed.

 

[Duane Hookom]

E10,
I think this would be near impossible since we don't know how you would
expect to display the information.
Given your example of "one customer with two contacts and 3 products' how
would you expect them to display?

Major question would be: why do "want to use query based on all three
tables"?

 

[John Vinson]

I have 3 tables (Customers, Contacts, Products) whose relationship is one to
many.

If I want to use query based on all three tables, it returns duplicate rows.

eg If I have one customer with two contacts and 3 products, the query
returns 6 records.

Of course. That's what it's designed to do. Since there is no
relationship between Contacts and Products, and since queries are Set
operations, you'll get all possible combinations.

What should I do so that the query returns one customer, 2 contacts and 3
products.

Which Contact should be in the same row as the first Product? Which
Contact should the third (unmatched) Product be linked with?

YOU CAN'T. Queries simply don't work that way! If you're using the
query datasheet for reporting - don't. That's not what they're
designed to do.

Instead, consider using a Form (or Report) with two Subforms (or
Subreports).

John W. Vinson[MVP]