Query with many-to-many relationship

[jhollingsh615]

I'm fairly new to A2007 and still have difficulty with joins. I have the following tables in a many-to-many relationship:


Companies: PK-CompanyID
Contacts: PK-Contact ID
CompanyContacts: FK-CompanyID, FK-ContactID and PK-ContactsCompanies

It works fine in my main form where I need to show each of multiple companies at which a single contact works. Master-child tables set up fine.

But, now I need to also provide a way to do the reverse. I need to make a query and report in which every single company is listed, along with each of the multiple contacts attached to that company. I'm assuming I need a left outer join. But, I'm at a loss how to do it. Hoping it's nothing more than Monday mental fuzziness and someone can point me toward an explanation suitable for a newbie.....Thanks in advance.

[NTC]

What happens when you make a query that has the Companies table joined to the CompanyContacts table?

[jhollingsh615]

A query with just tblCompanies and tblCompanyContacts gives me what I guess is called the cartesian results. Every single company is listed, repeated for each record that is found in tblContacts. If there are 4 contacts at a company, the company's listing appears 4 times.

What happens when you make a query that has the Companies table joined to the CompanyContacts table?

[NTC]

well - its not clear to me whether this is a bad thing. assuming the join is correct you want to see every contact by company...and this displays that...yes? is it returning invalid results? I am thinking not......

in terms of display you may not want to display that same company info over and over - and that is easily removed at the query level by going to an AggregateQuery with a group-on.....or you can group using Reports....

actually you probably can get what you want with a report/form object simply by making the main form/report based on Companies table and inserting a sub form/report based on the other tables....another approach...

[jhollingsh615]

Actually, NTC, I am getting weird data results when I connect them. When do a form/subform with companies/contacts, not all of the contacts appear in the subform. For some companies, all is fine. For other companies, some contacts are missing. For example, one company has 4 contacts listed in the junction table. But, only two of those contacts are appearing in the subform. It had me fooled for a second until I noticed the missing contacts.

I have tried basing the forms on both forms and directly on the tables. The same missing results. Would that mean I have a problem in the relationships or where might it be screwing up? Thanks for the help......

well - its not clear to me whether this is a bad thing. assuming the join is correct you want to see every contact by company...and this displays that...yes? is it returning invalid results? I am thinking not......

in terms of display you may not want to display that same company info over and over - and that is easily removed at the query level by going to an AggregateQuery with a group-on.....or you can group using Reports....

actually you probably can get what you want with a report/form object simply by making the main form/report based on Companies table and inserting a sub form/report based on the other tables....another approach...