Running Total in Query (Access 2003)

[James]

Hi All,

I'm trying to insert a running total in my query. I want a running
total to total cube record and restart when an assignment # changes.

See my expected results below. Thanks!

Table A.Assignment# Query.Cube (Calculated field) Running
Cube By Assignment
123 .
5 .5
123 .
10 .15
123 .
5 .20
222 .
1 .1
222 .
5 .6
233 .
5 1.1
555 .
7 .7
555 .
1 .8
555 .
2 1.0

 

[John W. Vinson]

Hi All,

I'm trying to insert a running total in my query. I want a running
total to total cube record and restart when an assignment # changes.

See my expected results below. Thanks!

Table A.Assignment# Query.Cube (Calculated field) Running
Cube By Assignment
123 .
5 .5
123 .
10 .15
123 .
5 .20
222 .
1 .1
222 .
5 .6
233 .
5 1.1
555 .
7 .7
555 .
1 .8
555 .
2 1.0

Well, word wrap got that messed up, but you can use a Subquery or a DSum() to
calculate the running total. What are your tables and how are they related?
Assuming that it's all in Table A, and that Table A has a unique ID name AID
(as it must to define a sort order to "run" with):

SELECT [Table A].[Assignment#], [Cube], (SELECT Sum(X.[Cube] FROM [Table A] AS
X WHERE X.[Assignment #] = [Table A].[Assignment #] AND X.AID <= [Table
A].AID) AS [Running Cube By Assignment]
FROM <whatever>
ORDER BY [Assignment #], [AID];
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[James]

Hi All,

I'm trying to insert a running total in my query.  I want a running
total to total cube record and restart when an  assignment # changes.

See my expected results below.  Thanks!

Table A.Assignment#         Query.Cube (Calculated field)    Running
Cube By Assignment
123                                   ..
5                                           .5
123                                   ..
10                                         .15
123                                   ..
5                                           .20
222                                   ..
1                                           .1
222                                   ..
5                                           .6
233                                   ..
5                                          1.1
555                                   ..
7                                            .7
555                                   ..
1                                            .8
555                                   ..
2                                          1.0

Well, word wrap got that messed up, but you can use a Subquery or a DSum() to
calculate the running total. What are your tables and how are they related?
Assuming that it's all in Table A, and that Table A has a unique ID name AID
(as it must to define a sort order to "run" with):

SELECT [Table A].[Assignment#], [Cube], (SELECT Sum(X.[Cube] FROM [Table A] AS
X WHERE X.[Assignment #] = [Table A].[Assignment #] AND X.AID <= [Table
A].AID) AS [Running Cube By Assignment]
FROM <whatever>
ORDER BY [Assignment #], [AID];
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Never used Dsum before but does the following make sense?


Dsum(Query.Cube,Table.A,Table.A=Table.A)

 

[John W. Vinson]

Never used Dsum before but does the following make sense?


Dsum(Query.Cube,Table.A,Table.A=Table.A)

Not directly. As the message was posted there's no field named A in any of
these tables; the three arguments to DSum() must be text strings; and there's
nothing in this as written that restricts the values being summed to those
prior to the current record or to the current assignment # value.

Something more like

DSum("[Cube]", "[Table A]", "[Assignment#]= " & [Assignment#] & " AND [AID] <=
" & [AID])

would work better, assuming that Assignment# is a Number field and that AID
exists, is unique, is in ascending order by the desired order of records, and
is numeric.

A Subquery and a DSum() are just two ways to do the same thing. Sometimes one
is better, sometimes the other; DSum() can be slow and inefficient, but may
allow the resulting query to be updated when a subquery might not.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[James]

Never used Dsum before but does the following make sense?

Dsum(Query.Cube,Table.A,Table.A=Table.A)

Not directly. As the message was posted there's no field named A in any of
these tables; the three arguments to DSum() must be text strings; and there's
nothing in this as written that restricts the values being summed to those
prior to the current record or to the current assignment # value.

Something more like

DSum("[Cube]", "[Table A]", "[Assignment#]= " & [Assignment#] & " AND [AID] <=
" & [AID])

would work better, assuming that Assignment# is a Number field and that AID
exists, is unique, is in ascending order by the desired order of records,and
is numeric.

A Subquery and a DSum() are just two ways to do the same thing. Sometimesone
is better, sometimes the other; DSum() can be slow and inefficient, but may
allow the resulting query to be updated when a subquery might not.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thanks for the help, the dsum does slow down performance. Wow. I
appreciate it however.