Total and group a union query 1

[NeilT123]

I have the following union query

SELECT [Field Name],[Field Code], NYr0 as Effect, CInt([OM Application Year]) As Yr
FROM qry2rptA1
UNION ALL
SELECT[Field Name],[Field Code], NYr1,CINT([OM Application Year]+1)
FROM qry2rptA1
UNION ALL SELECT[Field Name],[Field Code],Nyr2, CINT([OM Application Year]+2)
FROM qry2rptA1;

which produces a table with headers
Field Name / Field Code / Effect / Yr

At the moment I then query the table and GROUP on YR and SUM the effect.

Is it possible to code the 2nd query into the above so that I only have 1 query? If so can anyone help with the code?

Thank you in advance.

 

[PHV]

At the moment I then query the table and GROUP on YR and SUM the effect
Not sure to understand what you really want ...
My guess:
SELECT Yr, Sum(Effect)
FROM (
SELECT NYr0 as Effect,CInt([OM Application Year]) As Yr FROM qry2rptA1
UNION ALL SELECT NYr1,CInt([OM Application Year]+1) FROM qry2rptA1
UNION ALL SELECT Nyr2,CInt([OM Application Year]+2) FROM qry2rptA1
) AS U
GROUP BY Yr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeilT123]

Hi PHV, sorry I may not have explained myself very well. The following is part of the table that results from my initial union query and then the further query to GROUP and SUM the results.

I can see what you were trying to do and I have tried your suggestion and a few variations but they don't give the desired answer so I thought this table might help as this is what I want but only using 1 query instead of 2

Field Name Field Code SumOfEffect Yr
Beeches 1134927234 47.55 2004
Beeches 1134927234 12.45 2005
Beeches 1134927234 6.75 2006
Broughton Road 1129621876 43.65 2005
Broughton Road 1129621876 20.85 2006
Broughton Road 1129621876 10.65 2007
Factory Field -1240275377 36.9 2004
Factory Field -1240275377 14.85 2005
Factory Field -1240275377 26.01 2006
Factory Field -1240275377 4.5 2007
Factory Field -1240275377 2.7 2008

Thank you for any input you may be able to give.

 

[PHV]

You wanted this ?
SELECT [Field Name],[Field Code],Sum(Effect) AS SumOfEffect,Yr
FROM (
SELECT [Field Name],[Field Code],NYr0 as Effect,CInt([OM Application Year]) As Yr FROM qry2rptA1
UNION ALL SELECT [Field Name],[Field Code],NYr1,CInt([OM Application Year]+1) FROM qry2rptA1
UNION ALL SELECT [Field Name],[Field Code],Nyr2,CInt([OM Application Year]+2) FROM qry2rptA1
) AS U
GROUP BY [Field Name],[Field Code],Yr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeilT123]

Thank you for the swift response. I had amended your original suggestion to something similar but had not included the [field name] in the GROUP By

Thank you again