calculating sums

[tamxwell]

Michel,
This is the UNION ALL query that you helped me with. I put together to sum
each field. My fields are as follows (see below). On my database main form I
have a number of queries that the Credit Managers can just click a button to
collect data per their requirements, then export to Excel.
The Query I speak of is Bad Debt by [co-number], [divn-number] (or Company
number...Division number) .
This query works fine, but there are several division numbers per co-number.
Co-number 01 has 15 divn-numbers. The button like I say is on the main form,
the CM can click the button and he will be prompted with "[what do you want
to name
file], then he is prompted with "[what co-number] then he is prompted with
[what divn-number]. This will allow for only 1 choice for the divn-number. I
need to be able to pick co-number 01, then say divn number(S) mb17 and no45
and return the results to the bottom of the same field or column.

I hope this explains it better, I have tried a number of tings, but I am not
sure if it's because of the UNION ALL statement.

Thanks Todd



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


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

qryBadDebtCustomerSub.[CA],
qryBadDebtCustomerSub.[CM],
qryBadDebtCustomerSub.[Internal/External],1 As DetailSection


FROM qryBadDebtCustomerSub


GROUP BY

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

UNION ALL

SELECT

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,sum([item-amount]),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 qryBadDebtCustomerSub

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

Hi,


I am not sure that I follow, but if a field must be filtered on two
values, as for field1 either =1, either =2, use an IN clause, such as

field1 IN(1, 2)


or, at long


( field1=1 OR field1 = 2 )


where parenthesis are likely needed, if there is other criteria with an AND
conjunction:

field2='alpha' AND ( field1=1 OR field1 = 2 ) AND field3=202




Hoping it may help,
Vanderghast, Access MVP

HI,

Well, I fixed the problem with the UNION ALL, but as usual Boss man wants
more,more,more. You have seen my Query so I am hoping there is an easy way
to
do what I need.

One query pull all info from [co-number] and then [divn-number].
(co-number
is Company Number and Divn-number is Division Number)

I need to pull multiple divn-numbers from the [divn-number] field. What I
mean is when I hit the button it prompts what co-number, I enter 01, then
it
prompts me for what divn-number I enter mb17. If I am wanting division
mb17
it works fine. But co-01 has 2 division and I need to export both to Excel
on
a single sheet.

Yep, I tried that and it still names my columns expr1014. expr1015, etc.
I
tried to even get SUM([CURRENT]),etc to no avail.

:

Hi,


If each record is unique ( unique as per the GROUP BY you use in the
second
SELEC) then, indeed, each record is doubled, but once with detail
section =
0 and once with detail section =1. If this is so, remove everything,
including, the first UNION ALL, but add the alias AS DetailSection
after
the 1 in the now first select:


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 As DetailSection


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];Hoping
it may help,Vanderghast, Access MVP"tamxwell"
messageHello again,>> Everything seemed to be going ok with the Union All
query, but afterlooking> 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 triedto> 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 asexpr1014,> 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];>>>>>>>>>> "Michel Walsh"
wrote:>>> Hi,>>>>>> It is day and portion of day (in decimal), so that
0.5 = half a day = 12h.>>
Using 0.001 for rounding is about the same as using a 90 seconds for>>
round-up. 1E-5 would be around one second. Is 3.95 to be read as
3days>> or as 4 days? If it has to be read as 3, then use>>>>>>
Int( difference )>>>>>> assuming you only deal with positive differences.
If 3.05 has to be readas>> 4, use>>>> - Int( - difference )>>>>>>
Hoping it may help,>> Vanderghast, Access MVP>>>>>> "tamxwell"
> Michel,>>
Question, will the 0.001 bias work for rounding out whole numbers?.
Asyou>> > know I have a complex query (UNION ALL) one of my fields is>> >
[Today]-[due-date] AS DSO. No one is sure why it's not DOD (days over>>
due)?>> > but it's DSO.>> >>> > This subtracts the field [Today] or
todays date, from the field[due-date]>> > original due date. So if it was
due 3 days ago then in the DSO columnit>> > would show 3 days but it's
more
like 3.335846554654. I have tried>> > DateDiff("d",[Due-Date],[Today]) AS
DSO in my original query then doing>> > "the">> > (UNION ALL) based on
that first query, but to no avail.>> >>> > Thanks Todd>> >>> > "Michel
Walsh" wrote:>> >>> >> Hi,>> >>>> >>>> >> The detail section is an
arbitrary number that help to track from>> >> which>> >> of the 3 SELECT
the record comes from. That allows to have all details>> >> with>> >>
DetailSection =0, the final total with DetailSection = 2
andintermediate>> >> sums, with DetailSection = 1. Using ORDER BY
starting withDetailSection>> >> allows to keep the records together,
within their respective section.>> >>>> >>>> >> I got most of my
training here, in the newsgroups. I don't do>> >> database>> >>
administration "per se", I do more development and experimentationsthat>>
administration. You can start at day one to write SQL, with SELECT
*FROM>> >> mytable. That is SQL after all In fact, it depends of
your>> >> interest>>

and to what problems you are exposed... If you are never exposed to a>>
problem that involves a GROUP BY, it is unlikely you would learn
it,or>> >> even>> >> read about it. The environment have a great
influence about making>> >> yourself>> >> an "expert".>> >>>> >>>> >>>>
Vanderghast, Access MVP>> >>>> >>>> >> "tamxwell"
>> >
Michel,>> >> > It works like Lucky Charms. It was a como of removing the
, andalso>> >> > adding the NULL after 360+. The one thing I'm still
trying tofigure>> >> > out>> >> > os>> >> > the DetailSection and where
it comes into play. You are so awesome!If>> >> > I>> >> > might be able
to ask you another question, where did you get your>> >> > training?>> >>
How long have you been doing dba, and how long did it take for youto>>
just>> >> > be>> >> > able to start wrinting in SQL ?>> >> > Thanks
A Million!>> >> > Todd>> >>

PS. After all this excitement you may not want to email me, but
itis>> >> > (e-mail address removed)>> >> >>> >> > "Michel Walsh"
wrote:>> >> >>> >> >> Hi,>> >> >>>> >> >>>> >> >> It ends with a
coma, after [DSO], so SQL expect another field,which>> >> >> is>> >>
not>> >> >> present. Try removing that coma.>> >> >>>> >> >> Note
that the first SELECT seems to have 2 more fields than theother>> >>
two>> >> >> SELECT, namely CA and CM, after [360+] and before
the constantfor>> >> >> the>> >> >> DetailSection. Remember that the
3 SELECT must have exactly thesame>> >> >> number>> >> >> of
fields.>> >> >>>> >> >>>> >> >>>> >> >> Hoping it may help,>> >> >>
Vanderghast, Access MVP>> >> >>>> >> >>>> >> >>>> >>>> >>>> >>>>>>>>

 

[Michel Walsh]

Hi,


I would start by putting the parameters inside query
qryBadDebtCustomerSub. The "filtering" has to occur there, it seems to me,
since it has, or can, be done before the SUMs. The UNION query just uses
that query, so it is not "directly" implied, by itself.



Hoping it may help
Vanderghast, Access MVP