Union query and sum

[Lmens]

Hello All-

I am using a union query to sum the result od 2 queries. the query asks for "parameter value" when run. How do I solve this? Please help. Below is my code. Thanks all.



SELECT [Delivery Org],
Sum([SumOfCY10 Q1 TOTAL ACTUAL]) AS [Q1 ACT],
Sum([SumOfQTR 2 FORECAST]) AS [Q2],
Sum([SumOfQTR3 FORECAST]) AS [Q3],
Sum([SumOfQTR 4 FORECAST])AS [Q4],
Sum([SumOfTOT CY10 REV]) AS [CY10]
FROM(
SELECT DISTINCTROW [CWV 6-7-10].[Delivery Org], Sum([CWV 6-7-10].[CY10 Q1 TOTAL ACTUAL]) AS [SumOfCY10 Q1 TOTAL ACTUAL], Sum([CWV 6-7-10].[QTR 2 FORECAST]) AS [SumOfQTR 2 FORECAST], Sum([CWV 6-7-10].[QTR 3 FORECAST]) AS [Sum Of QTR 3 FORECAST], Sum([CWV 6-7-10].[QTR 4 FORECAST]) AS [SumOfQTR 4 FORECAST], Sum([CWV 6-7-10].[TOT CY10 REV]) AS [SumOfTOT CY10 REV], [CWV 6-7-10].[CUSTOMER GROUP]
FROM [CWV 6-7-10]
WHERE ((([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[Delivery Org])="SUDARSAN") AND (([CWV 6-7-10].[CUSTOMER GROUP])="AT&T WIRELINE")) OR ((([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[Delivery Org])="SUDARSAN") AND (([CWV 6-7-10].[CUSTOMER GROUP])="AT&T MOBILITY")) OR ((([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[Delivery Org])="SUDARSAN") AND (([CWV 6-7-10].[CUSTOMER GROUP])="VERIZON WIRELESS")) OR ((([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[Delivery Org])="SUDARSAN") AND (([CWV 6-7-10].[CUSTOMER GROUP])="VERIZON WIRELINE"))
GROUP BY [CWV 6-7-10].[Delivery Org], [CWV 6-7-10].[CUSTOMER GROUP]
UNION
SELECT DISTINCTROW "SUDARSAN" AS [Delivery Org], Sum([CWV 6-7-10].[CY10 Q1 TOTAL ACTUAL]) AS [SumOfCY10 Q1 TOTAL ACTUAL], Sum([CWV 6-7-10].[QTR 2 FORECAST]) AS [SumOfQTR 2 FORECAST], Sum([CWV 6-7-10].[QTR 3 FORECAST]) AS [SumOfQTR 3 FORECAST], Sum([CWV 6-7-10].[QTR 4 FORECAST]) AS [SumOfQTR 4 FORECAST], Sum([CWV 6-7-10].[TOT CY10 REV]) AS [SumOfTOT CY10 REV], [CWV 6-7-10].[CWV Source]
FROM [CWV 6-7-10]
WHERE ((([CWV 6-7-10].[CWV Source])="SUS PWR") AND (([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[CUSTOMER GROUP])="AT&T WIRELINE")) OR ((([CWV 6-7-10].[CWV Source])="SUS PWR") AND (([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[CUSTOMER GROUP])="VERIZON WIRELINE")) OR ((([CWV 6-7-10].[CWV Source])="SUS PWR") AND (([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[CUSTOMER GROUP])="VERIZON WIRELESS")) OR ((([CWV 6-7-10].[CWV Source])="WLESS ENT GOV") AND (([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[CUSTOMER GROUP])="AT&T WIRELINE")) OR ((([CWV 6-7-10].[CWV Source])="WLESS ENT GOV") AND (([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[CUSTOMER GROUP])="VERIZON WIRELINE")) OR ((([CWV 6-7-10].[CWV Source])="WLESS ENT GOV") AND (([CWV 6-7-10].RAF)=4 Or ([CWV 6-7-10].RAF)=5) AND (([CWV 6-7-10].[CUSTOMER GROUP])="VERIZON WIRELESS"))
GROUP BY [CWV 6-7-10].[CWV Source]
)
GROUP BY [Delivery Org];

 

[pbaldy]

Typically when it does that, something is mispelled. The parameter prompt should say what it's looking for. Check that out first.

 

[Lmens]

Thank you. The parameter prompt is asking for "SumOfQTR3 FORECAST", the spelling is correct.

 

[pbaldy]

Are you sure? I note that 2 and 4 have a space in front of them, 3 does not. It appears from the subquery that it should have one:

Sum([CWV 6-7-10].[QTR 3 FORECAST]) AS [Sum Of QTR 3 FORECAST]

Seems like a small thing, but Access is very picky about spelling.

 

[pbaldy]

I should add that I wouldn't use spaces in any of my object names, which will avoid this in the future. There are also other spaces in that name you haven't accounted for.

 

[Lmens]

Thanks, I fixed that and now I get a new error; "Syntax error in JOIN operation". HELP!!

 

[pbaldy]

I don't see any joins right off, but you have jammed the parentheses for the subquery against the word "FROM" which might be screwing it up.

 

[Lmens]

Thank you pbaldy, the issue is solved. Your posts did help!

 

[pbaldy]

Happy to help!