UNION ALL CALCULATING SUMS

[tamxwell]

Hello,

Everything seemed to be going ok with the Union All query, but after looking
at some figures, it seems that when I start with the original query that I
made the Union All from, it has been doubling the amount of records. The
original query had 2007 records after the Union All it has 4015. I tried to
remove the first SELECT and it worked as far as correcting the numbers of
records, but in the fields Current, 1-30.... 360+ they show up as expr1014,
expr 1014.etc. I have tried everything.

I will list the Union ALL so you can see where I need to do my fix.
Thanks Todd

SELECT
qryBadDebtbyBranch.[co-number],
qryBadDebtbyBranch.[divn-number],
qryBadDebtbyBranch.[cust-number],
qryBadDebtbyBranch.[cust-name],
qryBadDebtbyBranch.[TranType],
qryBadDebtbyBranch.[ref-number],
qryBadDebtbyBranch.[as-of-date],
qryBadDebtbyBranch.[due-date],
qryBadDebtbyBranch.[item-amount],
qryBadDebtbyBranch.[Today],

qryBadDebtbyBranch.DSO,
qryBadDebtbyBranch.Current,
qryBadDebtbyBranch.[1-30],
qryBadDebtbyBranch.[31-60],
qryBadDebtbyBranch.[61-90],
qryBadDebtbyBranch.[91-180],
qryBadDebtbyBranch.[181-360],
qryBadDebtbyBranch.[360+],
qryBadDebtbyBranch.[CA],
qryBadDebtbyBranch.[CM],
qryBadDebtbyBranch.[Internal/External], 0 As DetailSection



FROM qryBadDebtbyBranch

UNION ALL


SELECT
qryBadDebtbyBranch.[co-number],
qryBadDebtbyBranch.[divn-number],
qryBadDebtbyBranch.[cust-number],
qryBadDebtbyBranch.[cust-name],
qryBadDebtbyBranch.[TranType],
qryBadDebtbyBranch.[ref-number],
qryBadDebtbyBranch.[as-of-date],
qryBadDebtbyBranch.[due-date],
qryBadDebtbyBranch.[item-amount],
qryBadDebtbyBranch.[Today],
qryBadDebtbyBranch.[DSO],


Sum(Current) ,
Sum([1-30]) ,
Sum([31-60]) ,
Sum([61-90]) ,
Sum([91-180]),
Sum([181-360]),
Sum([360+]),

qryBadDebtbyBranch.[CA],
qryBadDebtbyBranch.[CM],
qryBadDebtbyBranch.[Internal/External],1


FROM qryBadDebtbyBranch


GROUP BY

qryBadDebtbyBranch.[co-number],
qryBadDebtbyBranch.[divn-number],
qryBadDebtbyBranch.[cust-number],
qryBadDebtbyBranch.[cust-name],
qryBadDebtbyBranch.[TranType],
qryBadDebtbyBranch.[ref-number],
qryBadDebtbyBranch.[as-of-date],
qryBadDebtbyBranch.[due-date],
qryBadDebtbyBranch.[item-amount],
qryBadDebtbyBranch.[Today],
qryBadDebtbyBranch.[DSO],
qryBadDebtbyBranch.[CA],
qryBadDebtbyBranch.[CM],
qryBadDebtbyBranch.[Internal/External]

UNION ALL SELECT

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
SUM(CURRENT),SUM([1-30]),SUM([31-60]),SUM([61-90]),SUM([91-180]),SUM([181-360]),SUM([360+]),NULL,NULL,NULL, 2

FROM qryBadDebtbyBranch

ORDER BY DeTailSection,
qryBadDebtbyBranch.[co-number],
qryBadDebtbyBranch.[divn-number],
qryBadDebtbyBranch.[cust-number],
qryBadDebtbyBranch.[cust-name],
qryBadDebtbyBranch.[TranType],
qryBadDebtbyBranch.[ref-number],
qryBadDebtbyBranch.[as-of-date],
qryBadDebtbyBranch.[due-date],
qryBadDebtbyBranch.[item-amount],
qryBadDebtbyBranch.[Today],
qryBadDebtbyBranch.[DSO],
qryBadDebtbyBranch.[CA],
qryBadDebtbyBranch.[CM],
qryBadDebtbyBranch.[Internal/External];