Running Totals

[davegoodo]

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

 

[isladogs]

Welcome to AWF. Hopefully a moderator will move this into a technical area of the forum.

Try using a subquery. The approach is basically the same as Allen Browne's meter reading example at Microsoft Access tips: Subquery basics (allenbrowne.com)

 

[arnelgp]

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.

 

[June7]

Create a running totals query - Microsoft 365 Apps | Microsoft Learn

sql - How to get Running Total in MS Access query - Stack Overflow

Or build a report and use RunningSum property of textbox. This is simplest method within Access.

 

[Gasman]

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 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.

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

BetID	Name	Win_Lose	Prog	NewProg
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 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.

 

[arnelgp]

I added an Autonumber field (ID).
now see Query1 (design and datasheet view).

 

[MajP]

Same solution.

 

[davegoodo]

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]

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]

I thought attachments were okay with even 1 post? It's URLs that are restricted.

 

[Gasman]

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

Running Totals

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...

www.access-programmers.co.uk

 

[davegoodo]

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]

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]

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

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]

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]

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.

 

[davegoodo]

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]

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.