Cumulative Sum

[kamaldoctor]

Hi All,

I have been struggling to write a query which will give me following results in field DistTotal as shown where:

RefNo field is variable
For placing 1 Default of DistTotal Value = 0
For placing 2 Default of DistTotal Value = Lenghth Value of placing 1
For placing 3 Default of DistTotal Value = Lenghth Value of placing 1 + placing 2
For placing 4 Default of DistTotal Value = Lenghth Value of placing 1 + placing 2 + placing3

so on & on & on…….till Placing is 30….

placing HName Length DistTotal RefNo
1 DREAMLINER 5.50 0 366
1 PRINCE ARIUS 1.75 0 356

2 TWITTILLATOR 0.05 1.75 356
2 SPEED BABY 0.75 5.50 366

3 BETHLEHEM 1.75 6.25 366
3 DAREDEVIL 1.5 1.80 356

4 STYLISH STROKES 0.75 8.00 366
4 MIRACLE OF MERCY 0.20 3.3 35

Please guide me how to write the query to achieve above results.
Thanks a Ton.

 

[plog]

I was unable to discern what you want from your explanation or from the data you posted. Post some sample data, along with field names and then what the results of the query should be based on that sample data. Use this format for posting data:

placing, HName, Length, DistTotal, rDate, RefNo
1, DREAMLINER, 5.5, 0, 4/28/2013, 366
1, AWESOME SMILE, .5, 0, 4/28/2013, 369

Also, some notes about your table--if Length and DistTotal fields are to only hold numeric data, then you should make their field types numeric, not text. And why do you have a DistTotal field at all in the table--your initial post makes it seem like that's a calculated value. Is it?

 

[kamaldoctor]

Hi plog,

Thanks for your response. Yes indeed DistTotal is calculated value which will not change once calculated and posted. Default value is 0(zero).

Yes, I will change Length and DistTotal field types to numeric.
ORIGINAL DATA:
---------------
placing, HName, Length, DistTotal, RefNo
1, DREAMLINER, 5.50, 0, 366
1, PRINCE ARIUS, 1.75, 0, 356
2, TWITTILLATOR, 0.05, 0, 356
2, SPEED BABY, 0.75, 0, 366
3, BETHLEHEM, 1.75, 0, 366
3, DAREDEVIL, 1.5, 0, 356
4, STYLISH STROKES, 0.75, 0, 366
4, MIRACLE OF MERCY, 0.20, 0, 356

CALCULATED DATA:
------------------
placing, HName, Length, DistTotal, RefNo
1, DREAMLINER, 5.50, 0, 366
1, PRINCE ARIUS, 1.75, 0, 356
2, TWITTILLATOR, 0.05, 1.75, 356
2, SPEED BABY, 0.75, 5.50, 366
3, BETHLEHEM, 1.75, 6.25, 366
3, DAREDEVIL, 1.5, 1.80, 356
4, STYLISH STROKES, 0.75, 8.00, 366
4, MIRACLE OF MERCY, 0.20, 3.3, 356

The above calculated data is used by me as history of race horses how they finished in a race. In layman's language - Length is the distance between the horse and the horse which followed him.

DistTotal is total Lengths from the horse which was placed 1st(Winner) for a given RefNo(Race).

I trust the data posted above is as required.

Thanks and Regards.

 

[plog]

What you want is a simple running total query. You can search the site for more info to learn about them. The below SQL will produce the results you want.

SELECT DUMPHANDHIST.placing, DUMPHANDHIST.HName, DUMPHANDHIST.Length, IIf([placing]=1,0,DSum("[Length]","DUMPHANDHIST","[RefNo]=" & [RefNo] & " AND [placing]<" & [placing]))*1 AS DistTotal, DUMPHANDHIST.rDate, DUMPHANDHIST.RefNo
FROM DUMPHANDHIST;

 

[kamaldoctor]

hi plog,

Thanks. I am a novice and never worked with SQL statements. Where do I write this statement?

In the meantime I will try and gather more knowledge about SQL.

Thanks & Regards

 

[kamaldoctor]

hi plog,

OK. I went into SQL View and pasted the statement provided by you and tried to run the query which is giving me "Compile Error Message".

 

[kamaldoctor]

hi plog,
It worked.....exactly the way I wanted it !!! Please ignore my earlier post.
Thanks A Ton.....This opens up a big knowledge bank for me which will provide me with many opportunities to manipulate data.
thanks & regards

 

[kamaldoctor]

Hi Plog,

The SQL you wrote for me worked brilliantly for me so far. I have recently introduced a text field called 'Season' for sorting my data. As the HName as well as RefNo may repeat in different 'Season', I request your help in incorporating 'Season' as additional criteia in the SQL statement. Sample data is posted below:


ORIGINAL DATA:
---------------
placing, HName, Length, DistTotal, RefNo, Season
1, DREAMLINER, 5.50, 0, 366, MYM12
1, PRINCE ARIUS, 1.75, 0, 356, MYM12
2, TWITTILLATOR, 0.05, 0, 356, MYM12
2, SPEED BABY, 0.75, 0, 366, MYM12
3, BETHLEHEM, 1.75, 0, 366, MYM12
3, DAREDEVIL, 1.5, 0, 356, MYM12
4, STYLISH STROKES, 0.75, 0, 366, MYM12
4, MIRACLE OF MERCY, 0.20, 0, 356, MYM12
1, DREAMLINER, 5.50, 0, 366, BS12
1, PRINCE ARIUS, 1.75, 0, 356, BS12
2, TWITTILLATOR, 0.05, 0, 356, BS12
2, SPEED BABY, 0.75, 0, 366, BS12
3, BETHLEHEM, 1.75, 0, 366, BS12
3, DAREDEVIL, 1.5, 0, 356, BS12
4, STYLISH STROKES, 0.75, 0, 366, BS12
4, MIRACLE OF MERCY, 0.20, 0, 356, BS12

CALCULATED DATA:
------------------
placing, HName, Length, DistTotal, RefNo, Season
1, DREAMLINER, 5.50, 0, 366, MYM12
1, PRINCE ARIUS, 1.75, 0, 356, MYM12
2, TWITTILLATOR, 0.05, 1.75, 356, MYM12
2, SPEED BABY, 0.75, 5.50, 366, MYM12
3, BETHLEHEM, 1.75, 6.25, 366, MYM12
3, DAREDEVIL, 1.5, 1.80, 356, MYM12
4, STYLISH STROKES, 0.75, 8.00, 366, MYM12
4, MIRACLE OF MERCY, 0.20, 3.3, 356, MYM12
1, DREAMLINER, 5.50, 0, 366, BS12
1, PRINCE ARIUS, 1.75, 0, 356, BS12
2, TWITTILLATOR, 0.05, 1.75, 356, BS12
2, SPEED BABY, 0.75, 5.50, 366, BS12
3, BETHLEHEM, 1.75, 6.25, 366, BS12
3, DAREDEVIL, 1.5, 1.80, 356, BS12
4, STYLISH STROKES, 0.75, 8.00, 366, BS12
4, MIRACLE OF MERCY, 0.20, 3.3, 356, BS12

Thanks & Regards