This is the UNION ALL query that I put together to sum each field. My fields
are as follows. 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 divn 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 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];
Chaim said:
What button are you pushing? The 'Run' button? If so, what do your table and
query grid look like? The fact that anything is being asked for implies that
co-number and divn-number are not the names of fields in the table. What are
your criteria?
--
Chaim
tamxwell said:
One query pulls all info from field[co-number] and then field[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 just
division mb17 it works fine. But co-01 has 2 divisions and I need to export
both to Excel on a single sheet.
This also involves a Union Query that sums all the different fields.
