Overflow Error on Union Query 1

[AlexCuse]

Hi All,

I have a four piece union query that is giving me overflow errors. The query is designed to return a result like this:

Product 1
Actual 2006 Qtr1
Planned 2006 Qtr1
Actual 2006 Qtr2
Planned 2006 Qtr2
Actual 2006 Total
Planned 2006 Total

Product 2
Actual 2006 Qtr1
Planned 2006 Qtr1....

With actual data off to the right.

Before I added totals this query was working fine, and I have tested and re-tested to find that it is the last two queries in the union. Both run correctly on their own, but don't seem to work together. Am I missing something obvious here?

Here's the Query (last two pieces). And YrQtrMedia.Quarter is referenced in the first query:

SELECT "ACTUAL" as [TYPE] ,
"zATOTAL" as [QUARTER], 
YrQtrMedia.YEAR,
YrQtrMedia.MEDIA, 
Sum([SOURCE].[NET_QUANTITY]), 
"xxx" as [Gross Leads],
"xxx" as [Gross Response %],
Sum([qryCountKeycodesLEADS].[CountOfKEYCODE]) AS [Net Leads], 
Sum([qryCountKeycodesLEADS].[CountOfKeycode])/Sum([source].[net_quantity]) AS [Net Response %], 
Sum([qryCountKeycodesLEADS].[SumOfAPPS]) AS [Sales], 
IIf(Sum([qryCountKeycodesLeads].[SumOfAPPS])=0,0,Sum([qryCountKeycodesLEADS].[SumOfApps])/Sum([qryCountKeycodesLEADS].[CountOfKeycode])) AS [Conversion %], 
"xxx" as [Creative Cost],
Sum([SOURCE].[MEDIA_COSTS]) AS [Media Cost], 
Sum([SOURCE].[PROD_COSTS]) AS [Print/Prod/Ltrshp Cost],
"xxx" as [Postage Cost],
Sum([SOURCE].[MEDIA_COSTS]) + Sum(SOURCE.PROD_COSTS) as [Total Marketing Cost]
FROM (YrQtrMedia LEFT JOIN SOURCE on YrQtrMedia.QUARTER = SOURCE.QUARTER AND YrQtrMedia.YEAR= SOURCE.YEAR AND YrQtrMedia.MEDIA = SOURCE.MEDIA)
LEFT JOIN qryCountKeycodesLEADS ON SOURCE.SOURCE_CODE = qryCountKeycodesLEADS.KEYCODE
GROUP BY YrQtrMedia.YEAR, YrQtrMedia.MEDIA



UNION SELECT "PLANNED",
"zPTOTAL",
YrQtrMedia.YEAR, 
YrQtrMedia.Media,
sum([PLAN06].[CIRC]),
sum([PLAN06].[LEADS]),
sum([PLAN06].[LEADS])/sum([PLAN06].[CIRC]),
"xxx",
"xxx",
sum([PLAN06].[SALES]),
sum([PLAN06].[SALES])/sum([PLAN06].[LEADS]),
sum([PLAN06].[Creative Cost]),
sum([PLAN06].[TOTAL_MC]),
sum([PLAN06].[Print/Prod/Ltrshp Cost]),
sum([PLAN06].[Postage Cost]),
sum([PLAN06].[Total Marketing Cost])
FROM YrQtrMedia LEFT JOIN PLAN06 on 
YrQtrMedia.QUARTER = PLAN06.QTR
 AND YrQtrMedia.YEAR= PLAN06.YEAR AND YrQtrMedia.MEDIA = PLAN06.PROGRAM
GROUP BY YrQtrMedia.YEAR, YrQtrMedia.MEDIA



ORDER BY YrQtrMedia.MEDIA, QUARTER, TYPE

I am going nuts trying to find something that would cause the overflow. Any help would be greatly appreciated.

Thanks a lot,

Alex



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[PHV]

trying to find something that would cause the overflow
Probably a division by 0

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

 

[AlexCuse]

Fresh eyes...



They don't make a big enough embarassed face.

Thanks PH, You're the man as usual...

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.