Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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"?
 
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.
Click to expand...

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.
Click to expand...

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]
 
Back
Top