MS Access SQL: Troubles combining UNION ALL with a LEFT JOIN

Question

I have created a query in MS Access to simulate a FULL OUTER JOIN and combine the results that looks something like the following:

SELECT NZ(estimates.employee_id, actuals.employee_id) AS employee_id
, NZ(estimates.a_date, actuals.a_date) AS a_date
, estimates.estimated_hours
, actuals.actual_hours
FROM (SELECT *
      FROM estimates
      LEFT JOIN actuals ON estimates.employee_id = actuals.employee_id
         AND estimates.a_date = actuals.a_date
      UNION ALL
      SELECT *
      FROM estimates
      RIGHT JOIN actuals ON estimates.employee_id = actuals.employee_id
         AND estimates.a_date = actuals.a_date
      WHERE estimates.employee_id IS NULL
         OR estimates.a_date IS NULL) AS qFullJoinEstimatesActuals

I have saved this query as an object (let's call it qEstimatesAndActuals). My objective is to LEFT JOIN qEstimatesAndActuals with another table. Something like the following:

SELECT *
FROM qJoinedTable
LEFT JOIN (SELECT *
           FROM labor_rates) AS rates
ON qJoinedTable.employee_id = rates.employee_id
   AND qJoinedTable.a_date BETWEEN rates.begin_date AND rates.end_date

MS Access accepts the syntax and runs the query, but it omits results that are clearly within the result set. Wondering if the date format was somehow lost, I placed a FORMAT around the begin_date and end_date to force them to be interpreted as Short Dates. Oddly, this produced a different result set, but it still omitted result that it shouldn't have.

I am wondering if the queries are performed in such a way that you can't LEFT JOIN the result set of a UNION ALL. Does anyone have any thoughts/ideas on this? Is there a better way of accomplishing the end goal?

Paul Keister Paul Keister · Accepted Answer · 2010-12-20T22:32:12

I would try breaking each part of the query into its own access query object, e.g.

SELECT *
  FROM estimates
  LEFT JOIN actuals ON estimates.employee_id = actuals.employee_id
     AND estimates.a_date = actuals.a_date

Would be qryOne

SELECT *
  FROM estimates
  RIGHT JOIN actuals ON estimates.employee_id = actuals.employee_id
     AND estimates.a_date = actuals.a_date
  WHERE estimates.employee_id IS NULL
     OR estimates.a_date IS NULL

Would be qryTwo

SELECT * FROM qryOne
UNION ALL
SELECT * FROM qryTwo

Would be qryFullJoinEstimatesActuals, and finally

SELECT NZ(estimates.employee_id, actuals.employee_id) AS employee_id
, NZ(estimates.a_date, actuals.a_date) AS a_date
, estimates.estimated_hours
, actuals.actual_hours
FROM qryFullJoinEstimatesActuals

I've found that constructs that don't work in complex Access SQL statements often do work properly if they are broken down into individual query objects and reassembled step-by-step. Additionally, you can test each part of the query individually. This will help you find a workaround if one proves to be necessary.

MS Access SQL: Troubles combining UNION ALL with a LEFT JOIN

3 Answers