Many to Many (toooooo flippin many!!!)

[Guest]

I have a table (customers). For each of these customers, I need to know *who* at their organization fulfills a certain role (roles table). I have created table that lists the names of contacts (contacts), and a fourth table that does this

Contact ID, Role ID, CustomerID- and joins em all together. Without this little table I'd basically have three free-floating tables that aren't related to each other

Here's the problem:
I want to run a query that shows for each customer, a list of contacts currently known (and what role they're playin)- I figured this one out, it's 800 records.

I want to run a query that shows for each customer, showing a 1-1 relationship between the name of each role, and the customer ID, which should give me a list of 2200 records. Then I want to pull in the contacts table and show the names of the people (where known) beside the appropriate customer/role rows

I try this, and I get something like 3000044444 bazillion records.

I'm going nuts. Help

 

[[MVP] S.Clark]

The problem is probably that you don't have the links correct between
one/some/all of the tables, and it results in a Cross Join, thus the
"bazillion" records.

It may behoove you to try not to accomplish everything in just one query.
Break it down to several queries, which you can link together in another.

HTH

--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/

I have a table (customers). For each of these customers, I need to know

*who* at their organization fulfills a certain role (roles table). I have
created table that lists the names of contacts (contacts), and a fourth
table that does this:

Contact ID, Role ID, CustomerID- and joins em all together. Without this

little table I'd basically have three free-floating tables that aren't
related to each other.

Here's the problem:
I want to run a query that shows for each customer, a list of contacts

currently known (and what role they're playin)- I figured this one out,
it's 800 records.

I want to run a query that shows for each customer, showing a 1-1

relationship between the name of each role, and the customer ID, which
should give me a list of 2200 records. Then I want to pull in the contacts
table and show the names of the people (where known) beside the appropriate
customer/role rows.