Add Numbers in Totals Query

[antmorano]

I have a query where I need to total two numbers that are inputted
from two other queries. What I mean is that lets say under 65 has a
12 and over 65 has an 8 then I want a total to show 20. Here is the
SQL of the query. Please can anyone help me.

SELECT SUM([TOTALS]), "H&W UNDER" AS queryNumber
FROM [H&W UNDER AGE 65 RETIREE COUNT]
UNION ALL
SELECT Sum([TOTALS]), "H&W OVER" AS queryNumber
FROM [>65 H&W TOTAL RETIREES]
ORDER BY queryNumber;

-AM

 

[John Spencer]

Try the following

SELECT SUM(Totals) as GrandTotal
FROM (SELECT ([TOTALS]), "H&W UNDER" AS queryNumber
FROM [H&W UNDER AGE 65 RETIREE COUNT]
UNION ALL
SELECT ([TOTALS]), "H&W OVER" AS queryNumber
FROM [>65 H&W TOTAL RETIREES]) AS T

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

I have a query where I need to total two numbers that are inputted
from two other queries. What I mean is that lets say under 65 has a
12 and over 65 has an 8 then I want a total to show 20. Here is the
SQL of the query. Please can anyone help me.

SELECT SUM([TOTALS]), "H&W UNDER" AS queryNumber
FROM [H&W UNDER AGE 65 RETIREE COUNT]
UNION ALL
SELECT Sum([TOTALS]), "H&W OVER" AS queryNumber
FROM [>65 H&W TOTAL RETIREES]
ORDER BY queryNumber;

I think you might have taken a wrong turn back a ways.

Try starting over with a new query based on your base table.
I think this is the kind of thing you are looking for:

Select
Sum(IIf(bdate<=DateAdd("yyyy",-65,Date()),1,0) As HWOver
Sum(IIf(bdate>DateAdd("yyyy",-65,Date()),1,0) As HWUnder
FROM yourtable