calculating sums

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

 

[Michel Walsh]

Hi,


You could use a crosstab with PARTITION, see the help file.

With the actual query, you can also use:


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]>=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]>=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]>=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA, ALL_AGINGS_AR.[Internal/External]

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





The abominable GROUP BY is not the most efficient, but I cannot tell what
indeed "make" the group, and what can be aggregated with LAST in the SELECT
statement.



Hoping it may help,
Vanderghast, Access MVP

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

 

[Guest]

Michel,
You say I can use the crosstab? I tried to add the Sum at the start of the
IIF, and then Group by. When I run the query Branch number, then Customer
number to bring up just that companies info it still does not add a roll with
the sum of each column at the bottom. I then run export to Excel and nada.
This is the wierd thing. It should of worked. I'll look at crosstab. Heck,
I've looked at everything else.

Hi,


You could use a crosstab with PARTITION, see the help file.

With the actual query, you can also use:


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]>=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]>=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]>=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA, ALL_AGINGS_AR.[Internal/External]

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





The abominable GROUP BY is not the most efficient, but I cannot tell what
indeed "make" the group, and what can be aggregated with LAST in the SELECT
statement.



Hoping it may help,
Vanderghast, Access MVP

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

 

[Michel Walsh]

Hi,


If you want the detail and the sum at the bottom, that cannot be done in a
single simple query, but can be done with a UNION ALL


SELECT f1, f2, f3, 0 As detailSection FROM myTable
UNION ALL
SELECT f1, f2, SUM(f3), 1 FROM myTable GROUP BY f1, f2
UNION ALL
SELECT null, null, SUM(f3), 2 FROM myTable
ORDER BY detailSection, f1, f2


would, as example, list all the details and then, the sum of f3, by group,
and, at the end, list the whole total.


One of the important element of syntax to remember is that if you use an
aggregate, here SUM, then, in the SELECT statement, each field should either
be in the GROUP BY, either it should be aggregated. As a not working
example:

SELECT f1, f2, SUM(f3), f4
FROM myTable
GROUP BY f1, f2

where f4 is not in the GROUP BY list and not aggregated.


Hoping it may help,
Vanderghast, Access MVP

Michel,
You say I can use the crosstab? I tried to add the Sum at the start of the
IIF, and then Group by. When I run the query Branch number, then Customer
number to bring up just that companies info it still does not add a roll
with
the sum of each column at the bottom. I then run export to Excel and nada.
This is the wierd thing. It should of worked. I'll look at crosstab. Heck,
I've looked at everything else.

Hi,


You could use a crosstab with PARTITION, see the help file.

With the actual query, you can also use:


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]>=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]>=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]>=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA,
ALL_AGINGS_AR.[Internal/External]

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





The abominable GROUP BY is not the most efficient, but I cannot tell what
indeed "make" the group, and what can be aggregated with LAST in the
SELECT
statement.



Hoping it may help,
Vanderghast, Access MVP

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

 

[Guest]

Michel,
I do appreciate the help, believe me. I have not used the Union All before.
So this might be a dumb question.
Does the following info you gave me start my Sql statement. What I mean is
right now it's Select, then all the columns (fields) names. Then my IIF. I
presume that f1, and f2...refers to the columns I need to sum? I put my
query below the next few lines. I did receive another reply, but they
suggested I run a second query based on the first, I have way to many to do
this, so I believe yours is the anwser, I just got a bit confused. I've only
been doing Databases for a while now, and I am getting readyto start my
MCDBA. I need a lot of study..
Thanks Todd

SELECT f1, f2, f3, 0 As detailSection FROM myTable
UNION ALL

SELECT f1, f2, SUM(f3), 1 FROM myTable GROUP BY f1, f2
UNION ALL

SELECT null, null, SUM(f3), 2 FROM myTable
ORDER BY detailSection, f1, f2


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA,
ALL_AGINGS_AR.[Internal/External]

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

Hi,


If you want the detail and the sum at the bottom, that cannot be done in a
single simple query, but can be done with a UNION ALL


SELECT f1, f2, f3, 0 As detailSection FROM myTable
UNION ALL

SELECT f1, f2, SUM(f3), 1 FROM myTable GROUP BY f1, f2
UNION ALL

SELECT null, null, SUM(f3), 2 FROM myTable
ORDER BY detailSection, f1, f2


would, as example, list all the details and then, the sum of f3, by group,
and, at the end, list the whole total.


One of the important element of syntax to remember is that if you use an
aggregate, here SUM, then, in the SELECT statement, each field should either
be in the GROUP BY, either it should be aggregated. As a not working
example:

SELECT f1, f2, SUM(f3), f4
FROM myTable
GROUP BY f1, f2

where f4 is not in the GROUP BY list and not aggregated.


Hoping it may help,
Vanderghast, Access MVP

Michel,
You say I can use the crosstab? I tried to add the Sum at the start of the
IIF, and then Group by. When I run the query Branch number, then Customer
number to bring up just that companies info it still does not add a roll
with
the sum of each column at the bottom. I then run export to Excel and nada.
This is the wierd thing. It should of worked. I'll look at crosstab. Heck,
I've looked at everything else.

Hi,


You could use a crosstab with PARTITION, see the help file.

With the actual query, you can also use:


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]>=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]>=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]>=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA,
ALL_AGINGS_AR.[Internal/External]

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





The abominable GROUP BY is not the most efficient, but I cannot tell what
indeed "make" the group, and what can be aggregated with LAST in the
SELECT
statement.



Hoping it may help,
Vanderghast, Access MVP



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

 

[Michel Walsh]

Hi,


My example was just to "show" a possible case of syntax, a simple case, with
short names, and all, to just show the main elements.

Start by saving your query, it is sufficiently complex as it is now.


Then, make another query:


SELECT [co-number], ... , DSO, Current, [1-30], .... [360+], ...., 0 As
DetailSection FROM mySavedQuery

UNION ALL

SELECT [co-number], ... , DSO, SUM(Current), SUM([1-30]), .... SUM([360+]),
....., 1 FROM mySavedQuery GROUP BY [co-number], ... , DSO

UNION ALL

SELECT NULL, ... , NULL, SUM(Current), SUM([1-30]), ....
SUM([360+]), ...., 2 FROM mySavedQuery

ORDER BY DetailSection, [co-number], ... , DSO




Note that all SELECT must have the same number of "expressions". So, as
example, if there are 11 fields from [co-number] to DSO in the first SELECT,
supply 11 NULL in the last query, before getting the SUM on the 12th field,
Current.





Hoping it may help,
Vanderghast, Access MVP

Michel,
I do appreciate the help, believe me. I have not used the Union All
before.
So this might be a dumb question.
Does the following info you gave me start my Sql statement. What I mean
is
right now it's Select, then all the columns (fields) names. Then my IIF. I
presume that f1, and f2...refers to the columns I need to sum? I put my
query below the next few lines. I did receive another reply, but they
suggested I run a second query based on the first, I have way to many to
do
this, so I believe yours is the anwser, I just got a bit confused. I've
only
been doing Databases for a while now, and I am getting readyto start my
MCDBA. I need a lot of study..
Thanks Todd

SELECT f1, f2, f3, 0 As detailSection FROM myTable
UNION ALL

SELECT f1, f2, SUM(f3), 1 FROM myTable GROUP BY f1, f2
UNION ALL

SELECT null, null, SUM(f3), 2 FROM myTable
ORDER BY detailSection, f1, f2


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA,
ALL_AGINGS_AR.[Internal/External]

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

Hi,


If you want the detail and the sum at the bottom, that cannot be done in
a
single simple query, but can be done with a UNION ALL


SELECT f1, f2, f3, 0 As detailSection FROM myTable
UNION ALL

SELECT f1, f2, SUM(f3), 1 FROM myTable GROUP BY f1, f2
UNION ALL

SELECT null, null, SUM(f3), 2 FROM myTable
ORDER BY detailSection, f1, f2


would, as example, list all the details and then, the sum of f3, by
group,
and, at the end, list the whole total.


One of the important element of syntax to remember is that if you use an
aggregate, here SUM, then, in the SELECT statement, each field should
either
be in the GROUP BY, either it should be aggregated. As a not working
example:

SELECT f1, f2, SUM(f3), f4
FROM myTable
GROUP BY f1, f2

where f4 is not in the GROUP BY list and not aggregated.


Hoping it may help,
Vanderghast, Access MVP

Michel,
You say I can use the crosstab? I tried to add the Sum at the start of
the
IIF, and then Group by. When I run the query Branch number, then
Customer
number to bring up just that companies info it still does not add a
roll
with
the sum of each column at the bottom. I then run export to Excel and
nada.
This is the wierd thing. It should of worked. I'll look at crosstab.
Heck,
I've looked at everything else.

:

Hi,


You could use a crosstab with PARTITION, see the help file.

With the actual query, you can also use:


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]>=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]>=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]>=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA,
ALL_AGINGS_AR.[Internal/External]

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





The abominable GROUP BY is not the most efficient, but I cannot tell
what
indeed "make" the group, and what can be aggregated with LAST in the
SELECT
statement.



Hoping it may help,
Vanderghast, Access MVP



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

 

[Guest]

Hello Again Michel,

I am going to take a walk around the earth, I mean block. My brain is on
hold. Here is what I wrote. It puts an * in behind DSO,then tells me I cannot
do it. No kidding, anyway after writing all that I have so far, i feel like a
dork. Here is how I put it together,

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,*

qryBadDebtCustomerSub.Current,
qryBadDebtCustomerSub.[1-30],
qryBadDebtCustomerSub.[31-60],
qryBadDebtCustomerSub.[61-90],
qryBadDebtCustomerSub.[91-180],
qryBadDebtCustomerSub.[181-360],
qryBadDebtCustomerSub.[360+],
qryBadDebtCustomerSub.CA,
qryBadDebtCustomerSub.CM, 0 As DetailSection

FROM qryBadDebtCustomerSub

UNION ALL


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 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+]) AS Total360+,
CM, CA, [Internal/External] 1
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,



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+]) 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;

Hi,


My example was just to "show" a possible case of syntax, a simple case, with
short names, and all, to just show the main elements.

Start by saving your query, it is sufficiently complex as it is now.


Then, make another query:


SELECT [co-number], ... , DSO, Current, [1-30], .... [360+], ...., 0 As
DetailSection FROM mySavedQuery

UNION ALL

SELECT [co-number], ... , DSO, SUM(Current), SUM([1-30]), .... SUM([360+]),
....., 1 FROM mySavedQuery GROUP BY [co-number], ... , DSO

UNION ALL

SELECT NULL, ... , NULL, SUM(Current), SUM([1-30]), ....
SUM([360+]), ...., 2 FROM mySavedQuery

ORDER BY DetailSection, [co-number], ... , DSO




Note that all SELECT must have the same number of "expressions". So, as
example, if there are 11 fields from [co-number] to DSO in the first SELECT,
supply 11 NULL in the last query, before getting the SUM on the 12th field,
Current.





Hoping it may help,
Vanderghast, Access MVP

Michel,
I do appreciate the help, believe me. I have not used the Union All
before.
So this might be a dumb question.
Does the following info you gave me start my Sql statement. What I mean
is
right now it's Select, then all the columns (fields) names. Then my IIF. I
presume that f1, and f2...refers to the columns I need to sum? I put my
query below the next few lines. I did receive another reply, but they
suggested I run a second query based on the first, I have way to many to
do
this, so I believe yours is the anwser, I just got a bit confused. I've
only
been doing Databases for a while now, and I am getting readyto start my
MCDBA. I need a lot of study..
Thanks Todd

SELECT f1, f2, f3, 0 As detailSection FROM myTable
UNION ALL

SELECT f1, f2, SUM(f3), 1 FROM myTable GROUP BY f1, f2
UNION ALL

SELECT null, null, SUM(f3), 2 FROM myTable
ORDER BY detailSection, f1, f2


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA,
ALL_AGINGS_AR.[Internal/External]

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

Hi,


If you want the detail and the sum at the bottom, that cannot be done in
a
single simple query, but can be done with a UNION ALL


SELECT f1, f2, f3, 0 As detailSection FROM myTable
UNION ALL

SELECT f1, f2, SUM(f3), 1 FROM myTable GROUP BY f1, f2
UNION ALL

SELECT null, null, SUM(f3), 2 FROM myTable
ORDER BY detailSection, f1, f2


would, as example, list all the details and then, the sum of f3, by
group,
and, at the end, list the whole total.


One of the important element of syntax to remember is that if you use an
aggregate, here SUM, then, in the SELECT statement, each field should
either
be in the GROUP BY, either it should be aggregated. As a not working
example:

SELECT f1, f2, SUM(f3), f4
FROM myTable
GROUP BY f1, f2

where f4 is not in the GROUP BY list and not aggregated.


Hoping it may help,
Vanderghast, Access MVP

Michel,
You say I can use the crosstab? I tried to add the Sum at the start of
the
IIF, and then Group by. When I run the query Branch number, then
Customer
number to bring up just that companies info it still does not add a
roll
with
the sum of each column at the bottom. I then run export to Excel and
nada.
This is the wierd thing. It should of worked. I'll look at crosstab.
Heck,
I've looked at everything else.

:

Hi,


You could use a crosstab with PARTITION, see the help file.

With the actual query, you can also use:


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,

SUM( IIf([DSO]<=30,[item-amount],0)) AS [Current],
SUM( IIf([DSO]<=1 And [DSO]>=31,[item-amount],0)) AS [1-30],
SUM(IIf([DSO]<=60 And [DSO]>=31,[item-amount],0)) AS [31-60],
SUM(IIf([DSO]<=90 And [DSO]>=61,[item-amount],0) )AS [61-90],
SUM(IIf([DSO]<=180 And [DSO]>=91,[item-amount],0) )AS [91-180],
SUM(IIf([DSO]<=365 And [DSO]>=181,[item-amount],0)) AS [181-360],
SUM(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]))

GROUP BY 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,
ALL_AGINGS_AR.CM, ALL_AGINGS_AR.CA,
ALL_AGINGS_AR.[Internal/External]

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





The abominable GROUP BY is not the most efficient, but I cannot tell
what
indeed "make" the group, and what can be aggregated with LAST in the
SELECT
statement.



Hoping it may help,
Vanderghast, Access MVP



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

 

[Michel Walsh]

Hi,


Indeed, you are in a bad case of special special special case of syntax.
In this specific case, the syntax forbid the use of * in an UNION. You have
to type the whole list of field, no typo allowed But in fact, you
don't need the *, since you already type the whole list of fields.


Imagine a UNION as a merge of two SELECT, one on top of the other. For the
merge to occur, the NUMBER of fields, in each SELECT has to be the same and
thus, to be sure of that, * is not allowed. Just be sure each SELECT
statement, by itself, work, and that each SELECT statement has the same
number of fields...


You will get it, you will !


Hoping it may help,
Vanderghast, Access MVP

Hello Again Michel,

I am going to take a walk around the earth, I mean block. My brain is on
hold. Here is what I wrote. It puts an * in behind DSO,then tells me I
cannot
do it. No kidding, anyway after writing all that I have so far, i feel
like a
dork. Here is how I put it together,

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,*

qryBadDebtCustomerSub.Current,
qryBadDebtCustomerSub.[1-30],
qryBadDebtCustomerSub.[31-60],
qryBadDebtCustomerSub.[61-90],
qryBadDebtCustomerSub.[91-180],
qryBadDebtCustomerSub.[181-360],
qryBadDebtCustomerSub.[360+],
qryBadDebtCustomerSub.CA,
qryBadDebtCustomerSub.CM, 0 As DetailSection

FROM qryBadDebtCustomerSub

UNION ALL


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 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+]) AS Total360+,
CM, CA, [Internal/External] 1
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,



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+])
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;

 

[Guest]

Michel,
When I built the query it put the * behind DSO, and I'm no sure why. I'll
keep at it. I love dba programming, I just need to get this finished. You are
so cool for helping me!

Hi,


Indeed, you are in a bad case of special special special case of syntax.
In this specific case, the syntax forbid the use of * in an UNION. You have
to type the whole list of field, no typo allowed But in fact, you
don't need the *, since you already type the whole list of fields.


Imagine a UNION as a merge of two SELECT, one on top of the other. For the
merge to occur, the NUMBER of fields, in each SELECT has to be the same and
thus, to be sure of that, * is not allowed. Just be sure each SELECT
statement, by itself, work, and that each SELECT statement has the same
number of fields...


You will get it, you will !


Hoping it may help,
Vanderghast, Access MVP

Hello Again Michel,

I am going to take a walk around the earth, I mean block. My brain is on
hold. Here is what I wrote. It puts an * in behind DSO,then tells me I
cannot
do it. No kidding, anyway after writing all that I have so far, i feel
like a
dork. Here is how I put it together,

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,*

qryBadDebtCustomerSub.Current,
qryBadDebtCustomerSub.[1-30],
qryBadDebtCustomerSub.[31-60],
qryBadDebtCustomerSub.[61-90],
qryBadDebtCustomerSub.[91-180],
qryBadDebtCustomerSub.[181-360],
qryBadDebtCustomerSub.[360+],
qryBadDebtCustomerSub.CA,
qryBadDebtCustomerSub.CM, 0 As DetailSection

FROM qryBadDebtCustomerSub

UNION ALL


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 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+]) AS Total360+,
CM, CA, [Internal/External] 1
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,



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+])
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;

 

[Guest]

Hey,
I see what your saying. I put the * in another of my queries, which is why
it was pulling it in the Union All. I still have a syntx error on GROUP BY.
I'm getting closer, I think. Please look and see if you can find a problem
with the GROUP BY clause. Please

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,
qryBadDebtCustomerSub.Current,
qryBadDebtCustomerSub.[1-30],
qryBadDebtCustomerSub.[31-60],
qryBadDebtCustomerSub.[61-90],
qryBadDebtCustomerSub.[91-180],
qryBadDebtCustomerSub.[181-360],
qryBadDebtCustomerSub.[360+],
qryBadDebtCustomerSub.[CA],
qryBadDebtCustomerSub.[CM], 0 As DetailSection

FROM qryBadDebtCustomerSub


UNION ALL


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) ,
Sum([1-30]) ,
Sum([31-60]) ,
Sum([61-90]) ,
Sum([91-180]) ,
Sum([181-360]) ,
Sum([360+]) ,1

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],



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+]), 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;

Hi,


Indeed, you are in a bad case of special special special case of syntax.
In this specific case, the syntax forbid the use of * in an UNION. You have
to type the whole list of field, no typo allowed But in fact, you
don't need the *, since you already type the whole list of fields.


Imagine a UNION as a merge of two SELECT, one on top of the other. For the
merge to occur, the NUMBER of fields, in each SELECT has to be the same and
thus, to be sure of that, * is not allowed. Just be sure each SELECT
statement, by itself, work, and that each SELECT statement has the same
number of fields...


You will get it, you will !


Hoping it may help,
Vanderghast, Access MVP

Hello Again Michel,

I am going to take a walk around the earth, I mean block. My brain is on
hold. Here is what I wrote. It puts an * in behind DSO,then tells me I
cannot
do it. No kidding, anyway after writing all that I have so far, i feel
like a
dork. Here is how I put it together,

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,*

qryBadDebtCustomerSub.Current,
qryBadDebtCustomerSub.[1-30],
qryBadDebtCustomerSub.[31-60],
qryBadDebtCustomerSub.[61-90],
qryBadDebtCustomerSub.[91-180],
qryBadDebtCustomerSub.[181-360],
qryBadDebtCustomerSub.[360+],
qryBadDebtCustomerSub.CA,
qryBadDebtCustomerSub.CM, 0 As DetailSection

FROM qryBadDebtCustomerSub

UNION ALL


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 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+]) AS Total360+,
CM, CA, [Internal/External] 1
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,



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+])
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;

 

[Michel Walsh]

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 the other two
SELECT, namely CA and CM, after [360+] and before the constant for the
DetailSection. Remember that the 3 SELECT must have exactly the same number
of fields.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,
It works like Lucky Charms. It was a como of removing the , and also
adding the NULL after 360+. The one thing I'm still trying to figure 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 you to 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 it is
(e-mail address removed)

 

[Michel Walsh]

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 and intermediate
sums, with DetailSection = 1. Using ORDER BY starting with DetailSection
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 experimentations that
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

Michel,
It works like Lucky Charms. It was a como of removing the , and also
adding the NULL after 360+. The one thing I'm still trying to figure 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 you to 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 it is
(e-mail address removed)

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 the other two
SELECT, namely CA and CM, after [360+] and before the constant for the
DetailSection. Remember that the 3 SELECT must have exactly the same
number
of fields.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

Question, will the 0.001 bias work for rounding out whole numbers?. As you
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 column it
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

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 and intermediate
sums, with DetailSection = 1. Using ORDER BY starting with DetailSection
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 experimentations that
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

Michel,
It works like Lucky Charms. It was a como of removing the , and also
adding the NULL after 360+. The one thing I'm still trying to figure 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 you to 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 it is
(e-mail address removed)

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 the other two
SELECT, namely CA and CM, after [360+] and before the constant for the
DetailSection. Remember that the 3 SELECT must have exactly the same
number
of fields.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


It is day and portion of day (in decimal), so that 0.5 = half a day = 12 h.
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 3 days
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 read as
4, use

- Int( - difference )


Hoping it may help,
Vanderghast, Access MVP

Michel,

Question, will the 0.001 bias work for rounding out whole numbers?. As you
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 column it
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

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 and intermediate
sums, with DetailSection = 1. Using ORDER BY starting with DetailSection
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 experimentations that
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

Michel,
It works like Lucky Charms. It was a como of removing the , and also
adding the NULL after 360+. The one thing I'm still trying to figure
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 you to
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 it is
(e-mail address removed)

:

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 the other
two
SELECT, namely CA and CM, after [360+] and before the constant for
the
DetailSection. Remember that the 3 SELECT must have exactly the same
number
of fields.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hello again,

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

Hi,


It is day and portion of day (in decimal), so that 0.5 = half a day = 12 h.
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 3 days
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 read as
4, use

- Int( - difference )


Hoping it may help,
Vanderghast, Access MVP

Michel,

Question, will the 0.001 bias work for rounding out whole numbers?. As you
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 column it
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

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 and intermediate
sums, with DetailSection = 1. Using ORDER BY starting with DetailSection
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 experimentations that
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


Michel,
It works like Lucky Charms. It was a como of removing the , and also
adding the NULL after 360+. The one thing I'm still trying to figure
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 you to
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 it is
(e-mail address removed)

:

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 the other
two
SELECT, namely CA and CM, after [360+] and before the constant for
the
DetailSection. Remember that the 3 SELECT must have exactly the same
number
of fields.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

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" <[email protected]> wrote in messagenews:[email protected]...> Hello 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" <[email protected]> wrote in message>> news:[email protected]...>> > 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" <[email protected]> wrote in message>> >> news:[email protected]...>> >> > 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>> >> >>>> >> >>>> >> >>>> >>>> >>>> >>>>>>>>

 

[Guest]

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" <[email protected]> wrote in messagenews:[email protected]...> Hello 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.>>
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>> >>

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

 

[Guest]

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" <[email protected]> wrote in messagenews:[email protected]...> Hello 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.>>

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 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"

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