By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Sum in a union query

P: n/a
I have the following union query:

SELECT Count(PolicyNumber) AS TotalSubmitted, "TOTAL SUBMITTED" as
Header, "Combined" as TelemedClass, "1" as GroupA,"1" as Postion
FROM tblAppActivity
WHERE AppStatusSort = "1" And RDLastTransaction Between #01/01/2006#
and #09/30/2006#

UNION ALL
SELECT Count(PolicyNumber) AS TotalTelemedSubmittedIC, "TOTAL TELEMED
SUBMITTED" as Header, "Telemed" as TelemedClass , "2" as Groupa, "2"
as Postion
FROM tblAppActivity
WHERE AppStatusSort = "1" And Telemed_App = "y" And
IsNull(AgentGrade) And RDLastTransaction Between #01/01/2006# and
#09/30/2006#

UNION ALL
SELECT Count(PolicyNumber) AS TotalTelemedSubmittedIC, "TOTAL INNER
CIRCLE SUBMITTED" as Header, "Telemed" as TelemedClass , "2" as
Groupa, "3" as Postion
FROM tblAppActivity
WHERE AppStatusSort = "1" And AgentGrade = "R" And Telemed_App = "N"
And RDLastTransaction Between #01/01/2006# and #09/30/2006#

UNION ALL SELECT Count(PolicyNumber) AS TotalTelemedSubmittedIC, "TOTAL
TELEMED/INNER CIRCLE COMBO SUBMITTED" as Header, "Telemed" as
TelemedClass , "2" as Groupa, "4" as Postion
FROM tblAppActivity
WHERE AppStatusSort = "1" And Telemed_App ="y" And AgentGrade = "R"
And RDLastTransaction Between #01/01/2006# and #09/30/2006#;

can I have another row in my union that Sums union lines 2,3 and 4?
(GroupA = 2)

TIA

Oct 5 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You can do something like this:

Select * from
(select fld1, fld2, fld3 as fldx from table1
union all
select fldA, fldB, fldC as fldx from table2) t1
Union All
select 'a', 'b', sum(fldx) As sfldx from
(select fld1, fld2, fld3 as fldx from table1
union all
select fldA, fldB, fldC as fldx from table2) t1

In my example table1 and table2 contains different fields, but the 3rd
fld is a numeric field and I assign an alias to each fo the 3rd fields
-- 'fldx'. This way you can reference these fields in a subquery as one
field -- 'fldx' and use the Sum function. For the other 2 fields I
added spacers, 'a', 'b' to that the Union All is consistent with the
Union subquery.

With your query I would do this trick using VBA. That is, I would use
string variables so that you don't have too much code:

Dim str1 as string, str2 As String, str3 As String
Dim DB As DAO.Database, RS As DAO.Recordset

str1 = "(Select fld1, fld2, fld3 As fldxFrom table1 " _
& Union All Select fldA, fldB, fldC As fldx from tabl2) t1 "

str2 = "Select * From " & str1

str3 = "Union All Select 'a', 'b', Sum(fldx) As sfldx from " & str1

Set DB = CurrentDB
Set RS = DB.OpenRecordset(str2 & str3)
Do While Not RS.EOF
'populate result table here
RS.MoveNext
Loop

You could also just write out all the sql in the sql view of the query
builder. That will work too, but just way more code to write.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.