Circular Reference

[antmorano]

Hi I get a circular reference in the query from [H&W TOTAL OVER AND
UNDER]. I am trying to total the [TOTALS] from "H&W UNDER" and "H&W
OVER". Can anyone help me. I am having a really hard time figuring
this out. Here is my SQL:

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

-Anthony Morano

 

[John Spencer]

I would guess that you are referring to the query [H&W TOTAL OVER AND UNDER]
in the UNION query with the same name.

This may take the use of stacked queries to get the desired result.

You will need a separate query to get the grand total and then you can
include that separate query along with your under and over queries.


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

 

[antmorano]

John I appreciate the response. I was able to get a result but it is
a negative 8. It should be like 331. Here is the new SQL:

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

 

[John Spencer]

Try using Plus instead of "and". Although I don't think that will give you
the result you want either.

Query named qUnion1
SELECT SUM([TOTALS]) As TOTAL, "H&W UNDER" AS queryNumber
FROM [H&W UNDER AGE 65 RETIREE COUNT]
UNION ALL
SELECT Sum([TOTALS]) As TOTAL, "H&W OVER" AS queryNumber
FROM [>65 H&W TOTAL RETIREES]


You need something like the following to get the sum. qUnion2
SELECT Sum(Total), "Grand Total" as queryNumber
FROM qUnion1

Then

SELECT Total, QueryNumber
FROM qUnion1
UNION ALL
SELECT Total, QueryNumber
FROM qUnion2

You might be able to do the following and cut out the use of qUnion2
completely.

SELECT Total, QueryNumber
FROM qUnion1
UNION ALL
SELECT Sum(Total), "GrandTotal"
FROM qUnion1
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..