Yep, I tried that and it still names my columns expr1014. expr1015, etc.
I
tried to even get SUM([CURRENT]),etc to no avail.
Michel Walsh said:
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"
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>> >> >>>> >> >>>> >> >>>> >>>> >>>> >>>>>>>>