Sum the results of query with UNION

[Hendy88]

Hello,

I have the following SQL that produces two record totals (as it
should). But I want to SUM those two values from within the same
query. Rather than create a second query that SUMs the two values
from the first query:

SELECT Count(*) AS SubCount
FROM [MyTable1]
WHERE (([MyTable1].USER)="JOHNSMITH")
UNION ALL SELECT Count(*) AS SubCount
FROM [MyTable2]
WHERE (([MyTable2].USER)="JOHNSMITH");

The results I get are:

SubCount
--------------
380
45

What I want is:

TotalCount
---------------
425

I've tried "wrapping" that entire SQL code inside of a SELECT SUM,
but
upon execution it says, "this operation is not allowed in
subqueries".

Any ideas?

Thanks in advance!

 

[John W. Vinson]

Hello,

I have the following SQL that produces two record totals (as it
should). But I want to SUM those two values from within the same
query. Rather than create a second query that SUMs the two values
from the first query:

SELECT Count(*) AS SubCount
FROM [MyTable1]
WHERE (([MyTable1].USER)="JOHNSMITH")
UNION ALL SELECT Count(*) AS SubCount
FROM [MyTable2]
WHERE (([MyTable2].USER)="JOHNSMITH");

The results I get are:

SubCount
--------------
380
45

What I want is:

TotalCount
---------------
425

I've tried "wrapping" that entire SQL code inside of a SELECT SUM,
but
upon execution it says, "this operation is not allowed in
subqueries".

Posting your message three times won't get you a faster or better answer.

The UNION query is doing precisely what it's intended to do: generating two
recordsets and splicing them together. Summing numbers isn't part of what a
UNION query is designed to do!

You can use the UNION and base a second Totals query upon it, or use DCount:

Select DCount("*", "[MyTable1]", "[User] = 'JOHNSMITH'") + DCount("*",
"[MyTable2]", "[USER] = 'JOHNSMITH'")

DCount is not especially fast, but then neither are totals queries based upon
UNION queries - maybe try both and see which works best!

The fact that you have two tables with - evidently - the same "kind" of data
makes me suspicious that your tables may need better normalization; what are
these tables, and what kind of information are you counting?

John W. Vinson [MVP]

 

[Hendy88]

The first two times I tried to post, Internet Explorer crashed on me
from work so I didn't think the post actually made it through. So I
tried a third time from home, hence, the reason for the three posts.
After I realized that they did in fact post, I tried to remove the
extras. Apparently they are still there??

The tables actually hold document indexing queues from the corporate
data warehouse that is Oracle based. I have no control over how those
tables are created or how they are normalized. In fact, they don't
even have normal Field Names. They are all UF001, UF002, UF003, etc.
There's actually another table that you go up against to know what
those field names even coorespond to.

I simplified the table and field names for purposes of this post.
These tables are linked to Access through an ODBC connection. I'm
connected to two of these tables that are Incoming and Outgoing
traffic off of a fax and index document server. I need to grab "JOHN
SMITH"s totals from both tables for statistical purposes. Currently
employees have to manually transfer their "numbers" to a spreadsheet.
Through the ODBC connection, I can look in these tables and pull there
productivity directly at the end of the day.

 

[John W. Vinson]

I simplified the table and field names for purposes of this post.
These tables are linked to Access through an ODBC connection. I'm
connected to two of these tables that are Incoming and Outgoing
traffic off of a fax and index document server. I need to grab "JOHN
SMITH"s totals from both tables for statistical purposes. Currently
employees have to manually transfer their "numbers" to a spreadsheet.
Through the ODBC connection, I can look in these tables and pull there
productivity directly at the end of the day.

Well, that makes sense. A UNION query still is not the right tool to do what
you want, though!

Your choices would seem to be a Totals query based on a UNION query, the two
DCount() calls, or opening a Recordset in code. Or possibly you can create a
Query joining the two tables on the name field (though one of your examples
had two different names in the two SELECTs in the UNION).


John W. Vinson [MVP]

 

[Hendy88]

I got it. This code works:

SELECT Sum(SubCount) AS TotalCount
FROM (SELECT Count(*) AS SubCount
FROM [MyTable1]
WHERE (([MyTable1].USER)="JOHNSMITH")
UNION ALL SELECT Count(*) AS SubCount
FROM [MyTable2]
WHERE (([MyTable2].USER)="JOHNSMITH"));

Actually this seems to work faster than what you suggested:

Select DCount("*", "[MyTable1]", "[User] = 'JOHNSMITH'") +
DCount("*",
"[MyTable2]", "[USER] = 'JOHNSMITH'")

Either way... I got what I needed. Thanks so much for your insight!!