Running Totals (2 Viewers)

davegoodo

Member
Local time
Today, 13:59
Joined
Jan 11, 2024
Messages
91
My problem is finding a Running Total calculation that helps me keep track of how I'm going.
The database is in a Horse-Racing context and I want a column/field that gives a progressive balance of wins and losses on a given day(s).
I've tried using DSum without much success but have gotten closer using Sum in a query but the query would have to be continually updated.
I need to have a column which adds each race result (win or loss) into the current column called "Progressive Total". It sounds simple, but it is
harder to solve than I thought. I have a field that returns "Win/Lose", a field that holds "Dividend", other fields that have "Stake", "Result" of race,
The Running Total needs to add the Win or Loss on a race to each row to keep a Progressive Total. The trick seems to be getting the total up to the previous row and then adding the current result to that. Then the next race becomes the previous race and that total continues to accumulate.
It would be helpful if you are familiar with betting on Horse-Racing. I tried to attach an Excel Sheet to illustrate but they're not allowed, that makes things difficult. (I have now attached an Excel file in Access below).
Thanks
 

Attachments

  • BetSmart.accdb
    724 KB · Views: 61

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:59
Joined
May 7, 2009
Messages
19,248
you have only 2 post made and .xlsx/xlsm is not allowed as attachment. you need to put it in .Zip and attach it.
but since you only have 2 post, you are not allowed yet to send an attachment.

try putting in Onedrive/Google drive and post the linked of the location here.
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:59
Joined
Sep 21, 2011
Messages
14,528
Please do not crosspost your question, expecially in different subforums here, as well as on other forums, without advising of such. :(
Otherwise you will have half the responses on one thread, and half on the other with likely duplicates.

Perhaps a mod can combine/amend into one?

How you have managed to attach a db with 3 posts is beyond me?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:59
Joined
May 21, 2018
Messages
8,643
You need something to get the records into the desired sort order. I cannot figure how this is sorted. There is no unique PK so I added an autonumber called EntryID. Results match.
Code:
SELECT racingdata.betid,
       racingdata.name,
       racingdata.win_lose,
       racingdata.prog,
       (SELECT SUM(B.win_lose)
        FROM   racingdata AS B
        WHERE  b.entryid <= racingdata.entryid) AS NewProg
FROM   racingdata
ORDER  BY racingdata.betid;

Query1 Query1

BetIDNameWin_LoseProgNewProg
1​
New Republic
-$10.00​
-$20.00​
($20.00)​
1​
Touristic
-$10.00​
-$10.00​
($10.00)​
2​
With Your Blessing
$26.00​
$6.00​
$6.00​
2​
Toronomica
-$10.00​
-$4.00​
($4.00)​
3​
Shadows of Love
$22.00​
$18.00​
$18.00​
4​
Free Willed
-$10.00​
$8.00​
$8.00​
5​
Gregolimo
-$10.00​
-$2.00​
($2.00)​
5​
Holymanz
-$10.00​
-$12.00​
($12.00)​
6​
Generation
-$10.00​
-$22.00​
($22.00)​
7​
Here to Shock
-$10.00​
-$42.00​
($42.00)​
7​
Just Folk
-$10.00​
-$32.00​
($32.00)​
8​
The Carpet Bagger
-$10.00​
-$52.00​
($52.00)​
8​
The Guru
-$10.00​
-$62.00​
($62.00)​
9​
Toesonthenose
$15.00​
-$47.00​
($47.00)​
10​
Shadows of Love
-$10.00​
-$57.00​
($57.00)​
10​
Spirit Queen
-$10.00​
-$67.00​
($67.00)​
11​
Finepoint
-$10.00​
-$77.00​
($77.00)​
12​
Serpentine
-$10.00​
-$87.00​
($87.00)​
13​
Dollar Magic
-$10.00​
-$97.00​
($97.00)​
14​
Eneeza
$14.00​
-$83.00​
($83.00)​
15​
Miraval Rose
$7.00​
-$76.00​
($76.00)​
16​
Sir Lucan
-$10.00​
-$86.00​
($86.00)​
17​
King Magnus
$51.00​
-$45.00​
($45.00)​
17​
Vilana
-$10.00​
-$96.00​
($96.00)​
18​
Arkansaw Kid
-$10.00​
-$55.00​
($55.00)​
19​
Deny Knowledge
-$10.00​
-$65.00​
($65.00)​
19​
Muramasa
-$10.00​
-$75.00​
($75.00)​
19​
Banker's Choice
-$10.00​
-$85.00​
($85.00)​
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:59
Joined
May 21, 2018
Messages
8,643
You need to be able to specify an exact order when doing running sums (regardless of how they appear)
I thought it was Order by betID, Date, RaceID, Tab
but that does not seem right either. If you want the running sum to match your table order then you need to provide how you want to sort these. If not add a PK and sort on how the records are entered.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:59
Joined
May 7, 2009
Messages
19,248
I added an Autonumber field (ID).
now see Query1 (design and datasheet view).
 

Attachments

  • Horse_Racing.accdb
    536 KB · Views: 61

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:59
Joined
May 21, 2018
Messages
8,643
Same solution.
 

davegoodo

Member
Local time
Today, 13:59
Joined
Jan 11, 2024
Messages
91
This is the right thread. I got mixed up thinking the "New Users" forum was a temporary thing so I moved to the Queries forum.
Sorry I have confused people. This is the site I'm using now.
 

davegoodo

Member
Local time
Today, 13:59
Joined
Jan 11, 2024
Messages
91
Please do not crosspost your question, expecially in different subforums here, as well as on other forums, without advising of such. :(
Otherwise you will have half the responses on one thread, and half on the other with likely duplicates.

Perhaps a mod can combine/amend into one?

How you have managed to attach a db with 3 posts is beyond me?
Sorry, I have made a post on the original forum advising that I moved to a more relevant forum, i.e. Queries forum. Unfortunately I have unwittingly crossposted as you have pointed out. I want to continue in the Queries forum, where I should have posted to begin with. I am sorry.
 

June7

AWF VIP
Local time
Yesterday, 19:59
Joined
Mar 9, 2014
Messages
5,501
I thought attachments were okay with even 1 post? It's URLs that are restricted.
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:59
Joined
Sep 21, 2011
Messages
14,528
I thought attachments were okay with even 1 post? It's URLs that are restricted.
Well there was a newcomer recently who said they were unable to upload a file, excel I think. I seem to recall @arnelgp replied to him.
Indeed it was this O/P and this thread
 

davegoodo

Member
Local time
Today, 13:59
Joined
Jan 11, 2024
Messages
91
There is a field in my main database called "RaceTime" which sets the order of the races. It isn't in the sample file, it is in an Excel format and not useful for this forum, but there is a field that sets the order.
 

Minty

AWF VIP
Local time
Today, 04:59
Joined
Jul 26, 2013
Messages
10,380
There is a field in my main database called "RaceTime" which sets the order of the races. It isn't in the sample file, it is in an Excel format and not useful for this forum, but there is a field that sets the order.
Post the actual fields you have and then people won't be guessing.

It's amazing how often new users think obscuring the real details helps when it really hinders the process.
I understand hiding personal data and possibly a company name but dates, and real field names makes no sense.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:59
Joined
May 21, 2018
Messages
8,643
and not useful for this forum,
Except for the fact it is completely needed for a possible solution

Remember the Order in Excel is what you see. The order of items in a table is random and is often not the same order of what you see when looking at the table datasheet. If you want to ensure you get data out of a table in a desired order then you have to specify the order or all "bets are off" (ooh look a pun.)
Something like this then
Code:
SELECT racingdata.betid,
       racingdata.name,
       racingdata.win_lose,
       racingdata.prog,
       (SELECT SUM(B.win_lose)
        FROM   racingdata AS B
        WHERE  b.[Date] <= racingdata.[Date] AND b.[RaceTime] <= racingData.[raceTime]) AS NewProg
FROM   racingdata
ORDER  BY racingData.[Date], racingData.[racetime]
 

GPGeorge

George Hepworth
Local time
Yesterday, 20:59
Joined
Nov 25, 2004
Messages
2,042
Post the actual fields you have and then people won't be guessing.

It's amazing how often new users think obscuring the real details helps when it really hinders the process.
I understand hiding personal data and possibly a company name but dates, and real field names makes no sense.
It's counterproductive, in fact, because it often leads potential responders off down the dark and winding path to the wrong assumptions.
 

davegoodo

Member
Local time
Today, 13:59
Joined
Jan 11, 2024
Messages
91
It's counterproductive, in fact, because it often leads potential responders off down the dark and winding path to the wrong assumptions.
I have now posted the Actual database, thanks for the tip George.
 

Attachments

  • BetSmart.accdb
    724 KB · Views: 55

davegoodo

Member
Local time
Today, 13:59
Joined
Jan 11, 2024
Messages
91
Unfortunately, I just realised there is no actual data in it. The reason being I was trying to get the design right before adding the data. The design issue is why I came to the forum to begin with. I forgot that I don't have data in it yet, but the actual design is there. I apologise for any confusion. I'm not sure what I can do to help?
 

davegoodo

Member
Local time
Today, 13:59
Joined
Jan 11, 2024
Messages
91
If it is any help, the RaceTime field and the Date field are going to be a composite primary key. The RaceTime field orders the data for the race, but it needs work. What can I do to help things out?
Do I need to cancel my question, put some data into the database and re-post the question?
I'm very sorry and embarassed for mucking this up.
 

Users who are viewing this thread

Top Bottom