Linking multiple tables to a query

[Guest]

Please help!

I have a database that consists of physicians and their medical specialties.
They can have as many as 3 (Spec1, Spec2, Spec3).

I need to link multiple tables to a query so that the fields (Spec1, Spec2,
Spec3) can all be spelled out in one field (Internal Medicine instead of code
"IM"). Ideally, I would like the end result to be (example): Internal
Medicine, Anesthesiology, Cardiology

When I link the 3 tables to the query and insert the formula or expression,
my query only displays those physicians who only have all 3 specialties,
instead of including the physicians who only have 1 or 2 specialties. It will
not display physicians with any empty specialty fields.

Any suggestions??

Please e-mail me at: morrow.danielle at gmail.com

 

[Tom Ellison]

Dear Danielle:

I feel I would be doing a disservice if I did not point out your table
construction is faulty. Given a separate table of physicians and of
specialties, a third table should be used which associates each physician
with his specialties, one physician/specialty combination at a time.

For your immediate needs, change the type of JOINs in your query to be LEFT
JOINs instead of an INNER JOINs. This assumes the first table in the FROM
clause is the one with the Physician and the 3 specialties. However, you
will then see ALL the physicians, even those with no specialty. If you want
to get rid of them, then you can filter them out. If "no specialty" is
specified with a NULL in Spec1, Spec2, and Spec3 then this is done with:

WHERE Spec1 IS NOT NULL OR Spec2 IS NOT NULL OR Spec3 IS NOT NULL

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison