Union Query

[szag via AccessMonster.com]

I am having a problem consolidating client ids in union query. My data and
query is below. Thanks for any help:

Table:2006FY
--------------------------------------------------------------------
Client Id Rev
--------------------------------------------------------------------
ABC 10,000
DEF 20,000

Table:2007FY
--------------------------------------------------------------------
Client Id Rev
--------------------------------------------------------------------
ABC 30,000
DEF 40,000

Result:
--------------------------------------------------------------------
Client Id Rev
--------------------------------------------------------------------
ABC 10,000
DEF 20,000
ABC 30,000
DEF 40,000

BUT THIS IS THE RESULT I NEED:
--------------------------------------------------------------------
ABC 40,000
DEF 60,000


Here is my sql code:

Select [2006FY.ClientID] , Sum([2006FY.Revenue])
From 2006FY
Group by ClientID
Union
Select [2007FY.ClientID], Sum([2007FY.Revenue])
From 2007FY
Group by ClientID;

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can set up the UNION query as a derived table and SUM the result:

SELECT ClientID, SUM(Revenue) As SumOfRevenue
FROM [SELECT ClientID, Revenue
FROM 2006FY
UNION ALL
SELECT ClientID, Revenue
FROM 2007FY ]. AS A
GROUP BY ClientID

FYI: If you had put all the data in ONE table you could have avoided a
UNION query. Hint: Put a date column in the table to identify each
FY's data.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSSyJH4echKqOuFEgEQIMmACgi/6nZ80FWKB4uDLv8eE7FC1p1K4Anial
hinnfKXFA7sxrd0kgLsi5F1O
=/Zm3
-----END PGP SIGNATURE-----

 

[szag via AccessMonster.com]

Thanks! I am using someone else's database so I don't have much of a choice
on the design, but "yes" you are definately right that having it all in one
table would be so much easier.

Zag

 

[John Spencer]

Are you saying the UNION query fails?
As posted, you have the brackets around the table and field name wrong.
Actually as posted you don't even need the brackets.

Select [2006FY].[ClientID] , Sum([2006FY].[Revenue])
From 2006FY
Group by ClientID
Union
Select [2007FY].[ClientID], Sum([2007FY].[Revenue])
From 2007FY
Group by ClientID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================