CODEWITHSUNDEEP
ms-access
Chris Ray
Chris Ray

Reputation: 51

Full Outer Joins in MS Access

My situation: I have a central PI table (patient information) which stores every single patient in the database. Then I have a few other tables (ex. vital signs, self report scores, etc.). The problem is, not every patient completes measures in every table. I.e., a patient might have an entry in the vital signs table but not in the self report table. How can I run a query where it shows me ALL patients in the PI table and their corresponding vitals and SR data if it exists instead of restricting the query to only show me patients that have records in ALL three of those tables?

Upvotes: 0

Views: 2209

Answers (2)

PaulFrancis
PaulFrancis

Reputation: 5819

Access (unfortunately) does not support OUTER JOIN. What it does is INNER, LEFT and RIGHT JOINS.

  • INNER JOIN - Gives you only the information common to two tables.
  • RIGHT JOIN - Gives all information that are common to the two table and information that are not matched form the table that is on the RIGHT to the JOIN statement.
  • LEFT JOIN - Gives all information that are common to the two table and information that are not matched form the table that is on the LEFT to the JOIN statement.
  • OUTER JOIN - is the UNION of RIGHT JOIN and LEFT JOIN.

So as expected, you just have to perform two Queries that perform a JOIN in both directions and then marry them using a UNION. This is a long process, but is the only way !

SELECT * FROM 
(SELECT table1.FieldName1, table2.FieldName2
FROM table1 RIGHT JOIN table2 ON table1.ID = table2.ID 
UNION
SELECT table1.FieldName1, table2.FieldName2
FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID)

Upvotes: 1

HansUp
HansUp

Reputation: 97131

I'm unclear why you want a FULL OUTER JOIN.

You have a PI table which includes a row for every patient. You have another table like SR data which contains the self-reported data for those patients. Assuming they have a common key ... perhaps patient_id ... a LEFT JOIN will give you a row for each patient regardless of whether there is any SR Data available for that patient:

SELECT *
FROM
    [PI table] AS pi
    LEFT OUTER JOIN [SR Data] AS sr
    ON pi.patient_id = sr.patient_id;

Note Access will be happy with either LEFT OUTER JOIN or just LEFT JOIN there. OUTER is not needed, but Access won't object if you include it.

So I think you only need a LEFT JOIN. You would only need (the equivalent of) a FULL OUTER JOIN if the SR Data includes rows which don't match to any of the existing patients in the PI table and you want those unmatched rows included in the query result. But you didn't indicate that applies to your question, so I'm ruling it out unless you tell us otherwise.

You can LEFT JOIN another table to the first query ...

SELECT *
FROM
    ([PI table] AS pi
    LEFT OUTER JOIN [SR Data] AS sr
    ON pi.patient_id = sr.patient_id)
    LEFT JOIN [Vital Signs] AS v
    ON pi.patient_id = v.patient_id;

... and extend from there for additional tables as needed. If you use the Access query designer to set up your joins, it will add in parentheses correctly as the db engine demands for any query which includes more than one join.

Upvotes: 2

Related Questions