By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,600 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,600 IT Pros & Developers. It's quick & easy.

Running Totals by date misbehaving ?

P: n/a
Running Totals by date misbehaving

I applied to the letter the sample code given
on http://support.microsoft.com/?kbid=290136

but it seems the running total breaks when the day number
is less than the previous date

Example using one Table (Access 2000):

ES QAC
6/ 8/2005 100
6/10/2005 50
6/10/2005 200
6/15/2005 400
7/ 2/2005 20
7/14/2005 60
7/14/2005 600
8/ 3/2005 500
8/20/2005 10

Expected result :

ES QAC
6/ 8/2005 100
6/10/2005 350
6/15/2005 750
7/ 2/2005 770
7/14/2005 830
8/ 3/2005 1130
8/20/2005 1140

The Query :

First Column :
Field : AMonth : DatePart("m",[ES])
Total : Group by
Sort : Ascending
Show : Yes

Second Column :
Field : ADay : DatePart("d",[ES])
Total : Group by
Sort : Ascending
Show : Yes

Third Column :
Field : RunTot: DSum("QAC","Test","DatePart('d', _
[ES])<=" & [ADay] & " And DatePart('m', _
[ES])<=" & [AMonth] & "")
Total : Expression
Show : Yes
Obtained result from the above Query :
ES QAC
6/ 8/2005 100
6/10/2005 350
6/15/2005 750
7/ 2/2005 20 <--- ???
7/14/2005 1030
8/ 3/2005 520 <--- ???
8/20/2005 1940

Can somebody tell me where I went wrong ?
Thank you for your help.

J.P.

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
u473 wrote:
Running Totals by date misbehaving

I applied to the letter the sample code given
on http://support.microsoft.com/?kbid=290136

but it seems the running total breaks when the day number
is less than the previous date

Example using one Table (Access 2000):

ES QAC
6/ 8/2005 100
6/10/2005 50
6/10/2005 200
6/15/2005 400
7/ 2/2005 20
7/14/2005 60
7/14/2005 600
8/ 3/2005 500
8/20/2005 10

Expected result :

ES QAC
6/ 8/2005 100
6/10/2005 350
6/15/2005 750
7/ 2/2005 770
7/14/2005 830
8/ 3/2005 1130
8/20/2005 1140

The Query :

First Column :
Field : AMonth : DatePart("m",[ES])
Total : Group by
Sort : Ascending
Show : Yes

Second Column :
Field : ADay : DatePart("d",[ES])
Total : Group by
Sort : Ascending
Show : Yes

Third Column :
Field : RunTot: DSum("QAC","Test","DatePart('d', _
[ES])<=" & [ADay] & " And DatePart('m', _
[ES])<=" & [AMonth] & "")
Total : Expression
Show : Yes
Obtained result from the above Query :
ES QAC
6/ 8/2005 100
6/10/2005 350
6/15/2005 750
7/ 2/2005 20 <--- ???
Well...2 is less than 8, 10, and 15. Makes sense to me.
7/14/2005 1030
8/ 3/2005 520 <--- ???
8/20/2005 1940

Can somebody tell me where I went wrong ?
Thank you for your help.

J.P.

Why dont you do something like
Field : RunTot: DSum("QAC","Test","ES <= " & [Test]![ES])
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.