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

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.est开发者_JAVA技巧imated_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?

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.

You can find exactly how to do this here.

You're missing an INNER JOIN.... UNION ALL step.

Consistent with the odd behavior surrounding the dates, this issue turned out to be related to the use of NZ to select a date from qFullJoinEstimatesActuals. The use of NZ appears to make the data type ambiguous. As such, the following line from the example in my post caused the error:

, NZ(estimates.a_date, actuals.a_date) AS a_date

The ambiguous data type of a_date caused the BETWEEN operator to produce erroneous results when comparing a_date to rates.begin_date and rates.end_date in the LEFT JOIN. The issue was resolved by type casting the result of the NZ function, as follows:

, CDate(NZ(estimates.a_date, actuals.a_date)) AS a_date