Whats wrong with my union query? Its not very long... Getting error, "The expression is typed incorrectly, or is too complex to be evaluated..."

Hello,

This first example works and does not give any errors, but the second one gives the error denoted in the question title.

It's the addition of the last (bottom most) query in the 2nd example that is generating the error. I can run the last query by itself and it runs without error, but when I tack it on to the bottom of the 1rst example, I get the error.

THANK YOU!!!
JA67

****************************************************************************
1rst EXAMPLE:
**************************************
 
SELECT DeptName, sum(ThisMonthRec) AS ReceivedThisMonth, sum(RollingAvg) AS RollingAverage, sum(YearToDatTtl) AS YearToDateRec
FROM 
 
(SELECT
request_dept_nm AS DeptName, 
Count(request_dept_nm) AS ThisMonthRec,
NULL AS RollingAvg,
NULL AS YearToDatTtl
FROM request_fact
WHERE requested_dt BETWEEN Forms!frmRSS_TtlsByFrq!cmbMonthBeg AND Forms!frmRSS_TtlsByFrq!cmbMonthEnd
GROUP BY request_dept_nm
 
UNION ALL
 
SELECT
request_dept_nm AS DeptName, 
NULL AS ThisMonthRec,
Round((Count(request_dept_nm))/12) As RollingAvg,
NULL AS YearToDatTtl
FROM request_fact
WHERE requested_dt  BETWEEN  DateDiff("m", -12, Forms!frmRSS_TtlsByFrq!cmbMonthEnd) AND  Forms!frmRSS_TtlsByFrq!cmbMonthEnd
GROUP BY request_dept_nm)
 
GROUP BY DeptName;
 
****************************************************************************
2nd EXAMPLE:
**************************************
SELECT DeptName, sum(ThisMonthRec) AS ReceivedThisMonth, sum(RollingAvg) AS RollingAverage, sum(YearToDatTtl) AS YearToDateRec
FROM 
 
(SELECT
request_dept_nm AS DeptName, 
Count(request_dept_nm) AS ThisMonthRec,
NULL AS RollingAvg,
NULL AS YearToDatTtl
FROM request_fact
WHERE requested_dt BETWEEN Forms!frmRSS_TtlsByFrq!cmbMonthBeg AND Forms!frmRSS_TtlsByFrq!cmbMonthEnd
GROUP BY request_dept_nm
 
UNION ALL
 
SELECT
request_dept_nm AS DeptName, 
NULL AS ThisMonthRec,
Round((Count(request_dept_nm))/12) As RollingAvg,
NULL AS YearToDatTtl
FROM request_fact
WHERE requested_dt  BETWEEN  DateDiff("m", -12, Forms!frmRSS_TtlsByFrq!cmbMonthEnd) AND  Forms!frmRSS_TtlsByFrq!cmbMonthEnd
GROUP BY request_dept_nm
 
 
UNION ALL
 
SELECT
request_dept_nm AS DeptName, 
NULL AS ThisMonthRec,
NULL As RollingAvg,
Count(request_dept_nm) As YearToDatTtl
FROM request_fact
WHERE requested_dt BETWEEN #06/01/2008# AND #06/30/2008#
GROUP BY request_dept_nm)
 
GROUP BY DeptName;

Select allOpen in new window