UNION ALL- CALCULATING SUMS

G

Guest

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];
 
M

[MVP] S.Clark

2007 + 2007 = 4014 + the 1 null record = 2015?

A Union query adds records to the set, so where do you see the math going
wrong here?


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

tamxwell said:
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];
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculating sums 21
Using NULL 1
USING THE "IN CLAUSE" 4
Calculating sums Question 4
More Help withthe WHERE Clause 2
Need to write a query 5
Step through each field in query 1
Sub-Totaling 2

Top