Problem w/ Query

[illini]

I have a report which lists contacts by company. The report is based off of three tables:

1. Table 1 contains all the CompanyIDs which subscribe to Service A.
2. Table 2 contains all the CompanyIDs with associated Company name & state.
3. Table 3 contains all contacts by CompanyID. This table also shows which Service type a contact is associated with.

Therefore, if a CompanyID is listed on Table 1, then the company name and state are pulled from Table 2, and all contacts (associated with that company) are pulled from Table 3 if they are indicated as being associated with Service A. So far, everything is working great.

The problem arises when I have a company on Table 1, which does not have a contact associated with Service A. In this case, the Query simply does not list that company. If I remove the condition to only pull contacts associated with Service A, then the Query pulls all the contacts (no matter what service).

How can I specify the Query to list ALL Companies on Table 1 and any contacts associated with Service A on Table 3? This would produce a report listing all the companies subscribed to Service A and their contacts (if any).
-illini

 

[grtammi]

First, I think you should put your contacts from table 3 into table 2 - but that's my humble opinion.

Second, using the method you currently have, create the correct relationships between tables 1, 2, and 3. In your query design grid (query design grid? I thought we were in the Access Modules forum??), show all 3 tables, and then add the appropriate fields that you need. Access will create the joins for you, and you should have what you're looking for.

Greg
If you don't understand, just nod and smile ...

 

[Hasse]

Illini,

as rtammer suggested, this isn't really the appropriate forum for such question... Take care of this! Tek-tips is good structured - we must try to keep it that way. But I hope you learn ;-). So let's see...

First of all, at least if I understood rtammer correctly, I have to agree with him that there might be something rambling in the table structure. (By the way: it's good to keep your contacts in a seperate table, as there might be multiple contacts for one company.) But let's assume it's irrelevant & focus on your question...

Did you try OUTER JOINS yet?

How to?
Double click the relationship line which appeared / you've drawn between the tables 2 (companies) & 3 (contacts) in query design view. You can choose between three options:
(a) default: only show records of which the common field (company ID) appears in both tables
=> only show companies with contacts
(b) show all records of which common field appears in companies table
=> shows all companies, and leaves the query's contact fields blank (null) in the records of those having no contacts. THIS IS THE ONE YOU NEED
(c) vice versa

Drop a little post if it worked... So we know you solved the problem.
Hasse