Runnig Total or SUM

[VINAY-NARAN]

Dear all,

I have data in table below format. I want to do running total by rec_date
rec_dateGRN09/01/200840518.716/01/2008951123/01/2008187329/01/200818313/02/200890921/02/2008212304/03/20086719/03/200813801/04/200820302/04/20084908/04/200852

I am using DSum funcation in my query.

SELECT DISTINCT Sheet1.rec_date, Sum(Sheet1.GRN) AS SumOfGRN, DSum("GRN","Sheet1","[rec_date] <#" & [rec_date] & "#") AS RunTott
FROM Sheet1
GROUP BY Sheet1.rec_date;

it's not working fine it's give me below output.
rec_dateSumOfGRNRunTott09/01/200840518.755626.716/01/2008951140518.723/01/2008187350029.729/01/200818351902.713/02/200890952085.721/02/2008212352994.704/03/20086755574.719/03/200813855184.701/04/2008203
02/04/20084952085.708/04/20085255626.7
Please help me where i am wrong?

Regards

Vinay

 

[VINAY-NARAN]

I am send mail again.
have data in table below format. I want to do running total by rec_date

rec_date GRN
09/01/2008 40518.7
16/01/2008 9511
23/01/2008 1873
29/01/2008 183
13/02/2008 909
21/02/2008 2123
04/03/2008 67
19/03/2008 138
01/04/2008 203
02/04/2008 49
08/04/2008 52

I am using DSum funcation in my query.

SELECT DISTINCT Sheet1.rec_date, Sum(Sheet1.GRN) AS SumOfGRN, DSum("GRN","Sheet1","[rec_date] <#" & [rec_date] & "#") AS RunTott
FROM Sheet1
GROUP BY Sheet1.rec_date;

it's not working fine it's give me below output which is wrong.

rec_date SumOfGRN RunTott
09-Jan-08 40518.7 55626.7
16-Jan-08 9511 40518.7
23-Jan-08 1873 50029.7
29-Jan-08 183 51902.7
13-Feb-08 909 52085.7
21-Feb-08 2123 52994.7
04-Mar-08 67 55574.7
19-Mar-08 138 55184.7
01-Apr-08 203
02-Apr-08 49 52085.7
08-Apr-08 52 55626.7

Please help me where i am wrong?

Regards

Vinay

 

[MSAccessRookie]

I am send mail again.
have data in table below format. I want to do running total by rec_date

rec_date GRN
09/01/2008 40518.7
16/01/2008 9511
23/01/2008 1873
29/01/2008 183
13/02/2008 909
21/02/2008 2123
04/03/2008 67
19/03/2008 138
01/04/2008 203
02/04/2008 49
08/04/2008 52

I am using DSum funcation in my query.

SELECT DISTINCT Sheet1.rec_date, Sum(Sheet1.GRN) AS SumOfGRN, DSum("GRN","Sheet1","[rec_date] <#" & [rec_date] & "#") AS RunTott
FROM Sheet1
GROUP BY Sheet1.rec_date;

it's not working fine it's give me below output which is wrong.

rec_date SumOfGRN RunTott
09-Jan-08 40518.7 55626.7
16-Jan-08 9511 40518.7
23-Jan-08 1873 50029.7
29-Jan-08 183 51902.7
13-Feb-08 909 52085.7
21-Feb-08 2123 52994.7
04-Mar-08 67 55574.7
19-Mar-08 138 55184.7
01-Apr-08 203
02-Apr-08 49 52085.7
08-Apr-08 52 55626.7

Please help me where i am wrong?

Regards

Vinay

Your < needs to be <=. Otherwise you will add all values up to but not including the date. Something like this should work.

SELECT Table30.theDate, 
    Sum(Table30.theValue) AS SumOfGRN, 
    DSum("theValue","Table30","[theDate] <=#" & [theDate] & "#") AS RunTott
FROM Table30
GROUP BY Table30.theDate;

 

[VINAY-NARAN]

Dear Rookie,

I have tried both < and <= but it's not giving me correct running total.

it's give irregular total with blanks.

Please advice.

Vinay

 

[Xx_TownDawg_xX]

Maybe this will help. I used this sample db to code what I needed. The trick used in this sample was to use an "alias".

 

[MSAccessRookie]

Dear Rookie,

I have tried both < and <= but it's not giving me correct running total.

it's give irregular total with blanks.

Please advice.

Vinay

The code that I posted produces exactly the output that you wanted in my test database. Of course, you need to substitute the proper table and column names from your project.

 

[VINAY-NARAN]

Dear TownDawg,
That's I want. it's working perfect.

Thank you very much.

Regards

Vinay