SQL IIf? union? subqery? query question

[Chip R.]

I'm using an OBDC link to import Timberline database info into
access.. I am now trying to build a query, and having much difficulty
doing it.

I've got a lot of fields I'm trying to bring in:

Quote Number
Date
Salesperson
Estimator
Value
Contact
Company
ProjectName
Score
Job Number

All of the above is in a number of different databases and I'm using
inner joins to get them.

Anyway, there is more than one contact stored with each job, and those
contacts have different "Roles" that they're stored as. They might be
the architect involved, the general contractor, the owner... And
herein lies the problem. I only want the Contact whose Role is 'GC
(primary)'. And if there is no 'GC (Primary)', I want the 'Owner
(Primary)'. However, sometimes one particular job has both of those
types of contacts, and I only want one, the 'GC (Primary)'. I don't
know how to do that. I can get all of the info for one particular
role, I can get all the info for both roles, but I can't get the info
for one or the other.

Is there a way to do this?

I've thought about using a UNION statement to pull both, and then the
fact that UNION is distinct it would get rid of the duplicates that I
don't want. However, this doesn't work, and I get some "non unique
table reference" error. Someone recommended using a subquery, but I
don't know how I would use it to get what I want. An IIf statement
seems like an interesting idea, but there's a possiblity that in the
end I'd want to use SQL to pull the Timberline data straight into
Excel without any Access use at all, and it seems like IIf is only an
Access command.

If someone could help me, even just by pointing me in the right place
or direction so I can figure out how to do this myself, I'd appreciate
it a lot. I am very new to SQL and access in general.

Thanks,
Chip

(Please respond to the message board, not by e-mail)

 

[Tom Ellison]

Dear Chip:

For the contacts, make two LEFT JOINs to bring in both GC and Owner,
aliasing the two instances of the table C1 and C2. Then, in the
SELECT list, use a calculated column:

Nz(C1.Contact, C2.Contact) AS Contact

If there is a GC contact, that's the one that will show. If not, the
Owner contact will show. If neither, it ends up NULL.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Chip R.]

Thanks, Tom!

However, I still seem to require some assistance.

The following tables contain the following fields:

master_abm_contact:
contact_name (contact's name)
contact_id (unique contact id)

master_pjm_job_contact_list:
job_index (unique job index)
contact_id (same as above)
role (string, possibly null, 'owner', 'GC', 'architect',
etc...)

note: there are numerous contact_id's stored with each job_index, all
with different roles for each.

master_pjm_job:
job_index (same as above)
job_number (4 digit "name")

I don't really understand where I would make two left joins in this
situation. What is the left table in this case? And the right? I
know how to alias something, but what would I alias here, any of the
above three tables? Or would I alias something else? Are C1 and C2
different aliases for the same thing? Or two different aliases? I'm
fairly confident that I understand the Nz function.

Anyway, thanks for the help, I'll keep plugging away at it.

-Chip

 

[Tom Ellison]

Dear Chip:

The left joins and aliases would be something like:

LEFT JOIN master_pjm_job_contact_list MPJCL1
ON MPJCL1.job_index = MPJ.job_index
AND MPJCL1.role = 'GC'
LEFT JOIN master_pjm_job_contact_list MPJCL2
ON MPJCL2.job_index = MPJ.job_index
AND MPJCL2.role = 'Owner'

You can then reference MPJCL1.contact_id in the select list and see if
there is a contact_id for GC. Reference MPJCL2 similarly for the
Owner contact. From this, you can implement the Nz function as I
showed.

If your working in Jet, you'll need to nest the JOINs in parens. What
a pain!

Now, I used MPJCL1 and MPJCL2 instead of C1 and C2. These are aliases
for two different instances of the master_pjm_job_contact_list table.
They are joined differently, and can represent completely different
rows (and the absense of these rows) independently according to the
context of what is in MPJ.job_index. (I invented the alias for
master_pjm_job here.)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts