Calculating sums Question

G

Guest

Here is my Statement in SQL View. I need to have a sum for Fields Current,
[1-30], [31-60], [61-90],[91-180], [181-360], [360+]. From what I understand
it should be (Sum([Current]) It will not work. I am getting frustrated. As I
run my different queries, I need to export them to Excel on the desktop.
Please Help

Todd

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

[Today]-[due-date] AS DSO,

IIf([DSO]<=30,[item-amount],0) AS [Current],
IIf([DSO]<=1 And [DSO]>=31,[item-amount],0) AS [1-30],
IIf([DSO]<=60 And [DSO]>=31,[item-amount],0) AS [31-60],
IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) AS [61-90],
IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) AS [91-180],
IIf([DSO]<=365 And [DSO]>=181,[item-amount],0) AS [181-360],
IIf([DSO]>=366,[item-amount],0) AS [360+],


ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA, ALL_AGINGS_AR.[Internal/External]




FROM ALL_AGINGS_AR

WHERE (((ALL_AGINGS_AR.[co-number])=[Which Branch do you want data for?])
AND ((ALL_AGINGS_AR.[cust-number])=[Enter Customer number]))

ORDER BY ALL_AGINGS_AR.[co-number], ALL_AGINGS_AR.[divn-number],
ALL_AGINGS_AR.[cust-number], ALL_AGINGS_AR.[as-of-date];
 
D

Douglas J Steele

Presumably you've saved what you've got as a query. For the sake of
argument, let's call that query qryTemp.

Create a second query based on qryTemp:

SELECT [co-number], [divn-number],
[cust-number], [cust-name],
[due-date], TranType, [ref-number],
[as-of-date], [item-amount], Today,
DSO,
Sum([Current]) AS TotalCurrent,
Sum([1-30]) AS Total1-30,
Sum([31-60]) AS Total31-60,
Sum([61-90]) AS Total61-90,
Sum([91-180]) AS Total91-180,
Sum([181-360]) AS Total181-360,
Sum([360+]) ASA Total360+,
CM, CA, [Internal/External]
FROM qryTemp
GROUP BY [co-number], [divn-number],
[cust-number], [cust-name],
[due-date], TranType, [ref-number],
[as-of-date], [item-amount], Today,
DSO, CM, CA, [Internal/External]

That's assuming that you'll have multiple records for that combination of
fields after the GROUP BY. If not, remove sufficient fields so that you do.
 
G

Guest

Doug,
I really do appreciate the help. I've tried it so many way I think I've
confused my self to the point of "almost no return" When I built the second
query it put an * in the statement???? Then the error messages is "Cannot
group on fields with an *.
What am I doing wrong?
Todd

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

Sum([Current]) AS TotalCurrent,
Sum([1-30]) AS Total1-30,
Sum([31-60]) AS Total31-60,
Sum([61-90]) AS Total61-90,
Sum([91-180]) AS Total91-180,
Sum([181-360]) AS Total181-360,
Sum([360+]) ASA Total360+,
CM, CA, [Internal/External]

FROM qryBadDebtbyCM

GROUP BY qryBadDebtbyCM.[co-number], qryBadDebtbyCM.[divn-number],
qryBadDebtbyCM.[cust-number], qryBadDebtbyCM.[cust-name],
qryBadDebtbyCM.[due-date], qryBadDebtbyCM.TranType,
qryBadDebtbyCM.[ref-number], qryBadDebtbyCM.[as-of-date],
qryBadDebtbyCM.[item-amount], qryBadDebtbyCM.Today, qryBadDebtbyCM.DSO;






Douglas J Steele said:
Presumably you've saved what you've got as a query. For the sake of
argument, let's call that query qryTemp.

Create a second query based on qryTemp:

SELECT [co-number], [divn-number],
[cust-number], [cust-name],
[due-date], TranType, [ref-number],
[as-of-date], [item-amount], Today,
DSO,
Sum([Current]) AS TotalCurrent,
Sum([1-30]) AS Total1-30,
Sum([31-60]) AS Total31-60,
Sum([61-90]) AS Total61-90,
Sum([91-180]) AS Total91-180,
Sum([181-360]) AS Total181-360,
Sum([360+]) ASA Total360+,
CM, CA, [Internal/External]
FROM qryTemp
GROUP BY [co-number], [divn-number],
[cust-number], [cust-name],
[due-date], TranType, [ref-number],
[as-of-date], [item-amount], Today,
DSO, CM, CA, [Internal/External]

That's assuming that you'll have multiple records for that combination of
fields after the GROUP BY. If not, remove sufficient fields so that you do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TMaxwell said:
Here is my Statement in SQL View. I need to have a sum for Fields Current,
[1-30], [31-60], [61-90],[91-180], [181-360], [360+]. From what I understand
it should be (Sum([Current]) It will not work. I am getting frustrated. As I
run my different queries, I need to export them to Excel on the desktop.
Please Help

Todd

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

[Today]-[due-date] AS DSO,

IIf([DSO]<=30,[item-amount],0) AS [Current],
IIf([DSO]<=1 And [DSO]>=31,[item-amount],0) AS [1-30],
IIf([DSO]<=60 And [DSO]>=31,[item-amount],0) AS [31-60],
IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) AS [61-90],
IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) AS [91-180],
IIf([DSO]<=365 And [DSO]>=181,[item-amount],0) AS [181-360],
IIf([DSO]>=366,[item-amount],0) AS [360+],


ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA, ALL_AGINGS_AR.[Internal/External]




FROM ALL_AGINGS_AR

WHERE (((ALL_AGINGS_AR.[co-number])=[Which Branch do you want data for?])
AND ((ALL_AGINGS_AR.[cust-number])=[Enter Customer number]))

ORDER BY ALL_AGINGS_AR.[co-number], ALL_AGINGS_AR.[divn-number],
ALL_AGINGS_AR.[cust-number], ALL_AGINGS_AR.[as-of-date];
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Look on the Tables/Queries tab under Tools | Options. Make sure the "Output
All Fields" checkbox isn't selected.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



tamxwell said:
Doug,
I really do appreciate the help. I've tried it so many way I think I've
confused my self to the point of "almost no return" When I built the
second
query it put an * in the statement???? Then the error messages is "Cannot
group on fields with an *.
What am I doing wrong?
Todd

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

Sum([Current]) AS TotalCurrent,
Sum([1-30]) AS Total1-30,
Sum([31-60]) AS Total31-60,
Sum([61-90]) AS Total61-90,
Sum([91-180]) AS Total91-180,
Sum([181-360]) AS Total181-360,
Sum([360+]) ASA Total360+,
CM, CA, [Internal/External]

FROM qryBadDebtbyCM

GROUP BY qryBadDebtbyCM.[co-number], qryBadDebtbyCM.[divn-number],
qryBadDebtbyCM.[cust-number], qryBadDebtbyCM.[cust-name],
qryBadDebtbyCM.[due-date], qryBadDebtbyCM.TranType,
qryBadDebtbyCM.[ref-number], qryBadDebtbyCM.[as-of-date],
qryBadDebtbyCM.[item-amount], qryBadDebtbyCM.Today, qryBadDebtbyCM.DSO;






Douglas J Steele said:
Presumably you've saved what you've got as a query. For the sake of
argument, let's call that query qryTemp.

Create a second query based on qryTemp:

SELECT [co-number], [divn-number],
[cust-number], [cust-name],
[due-date], TranType, [ref-number],
[as-of-date], [item-amount], Today,
DSO,
Sum([Current]) AS TotalCurrent,
Sum([1-30]) AS Total1-30,
Sum([31-60]) AS Total31-60,
Sum([61-90]) AS Total61-90,
Sum([91-180]) AS Total91-180,
Sum([181-360]) AS Total181-360,
Sum([360+]) ASA Total360+,
CM, CA, [Internal/External]
FROM qryTemp
GROUP BY [co-number], [divn-number],
[cust-number], [cust-name],
[due-date], TranType, [ref-number],
[as-of-date], [item-amount], Today,
DSO, CM, CA, [Internal/External]

That's assuming that you'll have multiple records for that combination of
fields after the GROUP BY. If not, remove sufficient fields so that you
do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TMaxwell said:
Here is my Statement in SQL View. I need to have a sum for Fields
Current,
[1-30], [31-60], [61-90],[91-180], [181-360], [360+]. From what I understand
it should be (Sum([Current]) It will not work. I am getting frustrated.
As I
run my different queries, I need to export them to Excel on the
desktop.
Please Help

Todd

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

[Today]-[due-date] AS DSO,

IIf([DSO]<=30,[item-amount],0) AS [Current],
IIf([DSO]<=1 And [DSO]>=31,[item-amount],0) AS [1-30],
IIf([DSO]<=60 And [DSO]>=31,[item-amount],0) AS [31-60],
IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) AS [61-90],
IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) AS [91-180],
IIf([DSO]<=365 And [DSO]>=181,[item-amount],0) AS [181-360],
IIf([DSO]>=366,[item-amount],0) AS [360+],


ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA, ALL_AGINGS_AR.[Internal/External]




FROM ALL_AGINGS_AR

WHERE (((ALL_AGINGS_AR.[co-number])=[Which Branch do you want data
for?])
AND ((ALL_AGINGS_AR.[cust-number])=[Enter Customer number]))

ORDER BY ALL_AGINGS_AR.[co-number], ALL_AGINGS_AR.[divn-number],
ALL_AGINGS_AR.[cust-number], ALL_AGINGS_AR.[as-of-date];
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug,

I ended up using the UNION ALL, worked like a charm. Thanks again for your
help, this is a frat site for exchanging ideas.

Thanks
todd

Douglas J. Steele said:
Look on the Tables/Queries tab under Tools | Options. Make sure the "Output
All Fields" checkbox isn't selected.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



tamxwell said:
Doug,
I really do appreciate the help. I've tried it so many way I think I've
confused my self to the point of "almost no return" When I built the
second
query it put an * in the statement???? Then the error messages is "Cannot
group on fields with an *.
What am I doing wrong?
Todd

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

Sum([Current]) AS TotalCurrent,
Sum([1-30]) AS Total1-30,
Sum([31-60]) AS Total31-60,
Sum([61-90]) AS Total61-90,
Sum([91-180]) AS Total91-180,
Sum([181-360]) AS Total181-360,
Sum([360+]) ASA Total360+,
CM, CA, [Internal/External]

FROM qryBadDebtbyCM

GROUP BY qryBadDebtbyCM.[co-number], qryBadDebtbyCM.[divn-number],
qryBadDebtbyCM.[cust-number], qryBadDebtbyCM.[cust-name],
qryBadDebtbyCM.[due-date], qryBadDebtbyCM.TranType,
qryBadDebtbyCM.[ref-number], qryBadDebtbyCM.[as-of-date],
qryBadDebtbyCM.[item-amount], qryBadDebtbyCM.Today, qryBadDebtbyCM.DSO;






Douglas J Steele said:
Presumably you've saved what you've got as a query. For the sake of
argument, let's call that query qryTemp.

Create a second query based on qryTemp:

SELECT [co-number], [divn-number],
[cust-number], [cust-name],
[due-date], TranType, [ref-number],
[as-of-date], [item-amount], Today,
DSO,
Sum([Current]) AS TotalCurrent,
Sum([1-30]) AS Total1-30,
Sum([31-60]) AS Total31-60,
Sum([61-90]) AS Total61-90,
Sum([91-180]) AS Total91-180,
Sum([181-360]) AS Total181-360,
Sum([360+]) ASA Total360+,
CM, CA, [Internal/External]
FROM qryTemp
GROUP BY [co-number], [divn-number],
[cust-number], [cust-name],
[due-date], TranType, [ref-number],
[as-of-date], [item-amount], Today,
DSO, CM, CA, [Internal/External]

That's assuming that you'll have multiple records for that combination of
fields after the GROUP BY. If not, remove sufficient fields so that you
do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here is my Statement in SQL View. I need to have a sum for Fields
Current,
[1-30], [31-60], [61-90],[91-180], [181-360], [360+]. From what I
understand
it should be (Sum([Current]) It will not work. I am getting frustrated.
As
I
run my different queries, I need to export them to Excel on the
desktop.
Please Help

Todd

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

[Today]-[due-date] AS DSO,

IIf([DSO]<=30,[item-amount],0) AS [Current],
IIf([DSO]<=1 And [DSO]>=31,[item-amount],0) AS [1-30],
IIf([DSO]<=60 And [DSO]>=31,[item-amount],0) AS [31-60],
IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) AS [61-90],
IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) AS [91-180],
IIf([DSO]<=365 And [DSO]>=181,[item-amount],0) AS [181-360],
IIf([DSO]>=366,[item-amount],0) AS [360+],


ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA, ALL_AGINGS_AR.[Internal/External]




FROM ALL_AGINGS_AR

WHERE (((ALL_AGINGS_AR.[co-number])=[Which Branch do you want data
for?])
AND ((ALL_AGINGS_AR.[cust-number])=[Enter Customer number]))

ORDER BY ALL_AGINGS_AR.[co-number], ALL_AGINGS_AR.[divn-number],
ALL_AGINGS_AR.[cust-number], ALL_AGINGS_AR.[as-of-date];
Click to expand...
Click to expand...
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
More Help withthe WHERE Clause 2
UNION ALL- CALCULATING SUMS 1
USING THE "IN CLAUSE" 4
nested iif 5
Revise an Append Query, or Change to an Update Query 3
Dynamic RecordSet Field Name 4

Top