Running Totals (1 Viewer)

[strive4peace]

hi @davegoodo

please add some sample data to tables so others can see expected values.

I opened what you sent. Here are a few initial comments:

the first thing I look at when I open an unfamiliar database is the Relationships Diagram.

No diagram. Dragged tables on to get a birds eye view of everything. No defined relationships. Maybe because you made a sample database? Or maybe because that's something to be considered?

Best not to start fieldnames (or any names) with a number -- such as 1Plus and 2Plus -- better is Plus1 and Plus2

Also watch out for DefaultValue! instead of keeping zero that Access automatically does when fields are numeric or currency, many times, the DefaultValue should be deleted, so there is none.

 

[strive4peace]

ps, @davegoodo

in Analysis table:
ID fieldname maybe should be AnalysisID so it's name isn't ambiguous?

"Date" is a reserved word and should be qualified like RaceDate or dtRace or dtmRace (if Time is also important)

Problem names and reserved words in Access by Allen Browne

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

also even though I suggested Plus1 and Plus2 as better names for fields ... anytime you have a name with a number in it, perhaps there needs to be a related table? A consideration anyway.

I'm sure more will have comments to help you better with your structure -- that is most important to get right!

... then, once the database is more solid, you can get a better answer for showing running calculations

 

[davegoodo]

ps, @davegoodo

in Analysis table:
ID fieldname maybe should be AnalysisID so it's name isn't ambiguous?

"Date" is a reserved word and should be qualified like RaceDate or dtRace or dtmRace (if Time is also important)

Problem names and reserved words in Access by Allen Browne

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

also even though I suggested Plus1 and Plus2 as better names for fields ... anytime you have a name with a number in it, perhaps there needs to be a related table? A consideration anyway.

I'm sure more will have comments to help you better with your structure -- that is most important to get right!

... then, once the database is more solid, you can get a better answer for showing running calculations

I'll make changes and put some sample data in it. I can't do this straight away because it is race day. But I'll make the changes and put it up tomorrow. I'll get back to you when I've done this. I'm very embarassed.

 

[MajP]

I'll make changes and put some sample data in it. I can't do this straight away because it is race day. But I'll make the changes and put it up tomorrow. I'll get back to you when I've done this. I'm very embarassed.

I am not sure what else you are looking for. It is not going to change anything. I already provided a working solution and showed clearly that it provides the desired running sum. You just have to modify the query to the correct field names. regardless of any modification the approach is exactly the same.

 

[strive4peace]

I'll make changes and put some sample data in it. I can't do this straight away because it is race day. But I'll make the changes and put it up tomorrow. I'll get back to you when I've done this. I'm very embarassed.

don't be embarassed -- all who are new to Access make mistakes. Good that you know Excel so well so it will be easier for you to learn.

@MajP you always have brilliants solutions ... but since Dave is new to Access, it would be good for him to learn about data structure and setting up relationships

 

[MajP]

but since Dave is new to Access, it would be good for him to learn about data structure and setting up

I agree, that is good and very important, but is not needed to provide the solution.
My point was that the original data not only had sample data, but even had the expected results in the form of calculated running sum values from Excel. Only thing missing was the time field to further the sort. The OP does not need to worry about posting a new question or even a new db to see what the solution is, the approach will not change unless you want a different method for the running sum.

 

[apr pillai]

Please check whether this example is useful: https://www.msaccesstips.com/2019/11/running-sum-in-ms-access-query.html

 

[davegoodo]

I'm adding a sample database which is based on the actual database I am using. There are probably many things I need to get right within it but the sample records should give an accurate insight into what is going on.

As always the main problem I'm trying to work out is how to get a running total of the WinLoss column. The current "Progress" column reflects the Excel formula (I'm still using Excel for this), it calculates the progressive balance overall. I'm hopeful that someone could share a way in which I could get a Running Balance as I'm going. (To know where I'm at).

I greatly appreciate all who have contributed to this thread. I'm attaching "SampleDB.accdb" to help with your advice. Please ignore the previous databases I have uploaded, I'm sure this one will be adequate.
Thank you.

 

[davegoodo]

Please check whether this example is useful: https://www.msaccesstips.com/2019/11/running-sum-in-ms-access-query.html

Thanks for the link. I'll be spending some time working through it as it's quite large. I'm sure I'll learn a lot from it. Thanks!

 

[davegoodo]

don't be embarassed -- all who are new to Access make mistakes. Good that you know Excel so well so it will be easier for you to learn.

@MajP you always have brilliants solutions ... but since Dave is new to Access, it would be good for him to learn about data structure and setting up relationships

@strive4peace Thanks for your help Crystal. I have uploaded a new database "SampleDB.accdb". It has lots of sample records in it and should provide enough data to help create a RunningSum object. My embarassment is over now, I'll be happy just to walk away with a solution to my RunningSum dilemna. Much appreciated.

 

[arnelgp]

See Query1 (last column RunningTotal).

 

[davegoodo]

See Query1 (last column RunningTotal).

Thank you. I'll download it and have a look. I just ran it and it's great! I'll have to study the query and go to school on it.
Much appreciated, it looks great. I've got to step away from my PC now. I'll have a look tomorrow, I'm in Australia, its the time zones..

 

[arnelgp]

you are 3 hours ahead of me.

 

[Gasman]

See Query1 (last column RunningTotal).

@arnelgp
There is no field called Date?

 

[arnelgp]

i did not use Date/RaceDate on the query.

 

[Gasman]

i did not use Date/RaceDate on the query.

Ah, sorry wrong query.

 

[MajP]

So my point is that 30 replies later we have

SELECT TRANSACTION.*,
       (SELECT Sum(winlose)
        FROM   TRANSACTION AS T
        WHERE  T.transid <= TRANSACTION.transid) AS RunningTotal
FROM   [transaction];

which is the same as what was originally posted in thread 6

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

However, you may or may not want to do the running sum based on the transactionID. This assume that is the order you want. My assumption is that the real order you want is sorted by date and time. Transaction ID may or may not be sort you want, and may or may not be a good surrogate for Date and Time. Since transaction ID looks like an autonumber, then is it possible you ever enter information not in perfect date time order? For example if you miss entering a race and then go back and fix it there is no way to get it into the correct order. You get some records out of order and enter them this will not be correct. I would think the more reliable solution is.

I believe now that you have a date and time, assuming that is the real order

SELECT transaction.*,
       (SELECT SUM(winlose)
        FROM   transaction AS T
        WHERE  ( T.racedate + T.racetime ) <= (
               transaction.racedate + transaction.racetime )) AS RunningTotal
FROM   [transaction]
ORDER  BY transaction.racedate,
          transaction.racetime;

 

[GPGeorge]

Spoiler:

So my point is that 30 replies later we have

SELECT TRANSACTION.*,
       (SELECT Sum(winlose)
        FROM   TRANSACTION AS T
        WHERE  T.transid <= TRANSACTION.transid) AS RunningTotal
FROM   [transaction];

which is the same as what was originally posted in thread 6

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

However, you may or may not want to do the running sum based on the transactionID. This assume that is the order you want. My assumption is that the real order you want is sorted by date and time. Transaction ID may or may not be sort you want, and may or may not be a good surrogate for Date and Time. Since transaction ID looks like an autonumber, then is it possible you ever enter information not in perfect date time order? For example if you miss entering a race and then go back and fix it there is no way to get it into the correct order. You get some records out of order and enter them this will not be correct. I would think the more reliable solution is.

Spoiler:

I believe now that you have a date and time, assuming that is the real order

SELECT transaction.*,
       (SELECT SUM(winlose)
        FROM   transaction AS T
        WHERE  ( T.racedate + T.racetime ) <= (
               transaction.racedate + transaction.racetime )) AS RunningTotal
FROM   [transaction]
ORDER  BY transaction.racedate,
          transaction.racetime;

In 25+ years working with various database projects, I learned the hard way that relying on AutoNumbers for sequencing is a dangerous practice on which to build a mission critical application.

 

[Pat Hartman]

Autonumbers can be useful to determine data entry sequence. However, if you want to process your data in date sequence or by some other value, autonumbers won't work if your data is ever entered out of sequence.

 

[davegoodo]

So my point is that 30 replies later we have

SELECT TRANSACTION.*,
       (SELECT Sum(winlose)
        FROM   TRANSACTION AS T
        WHERE  T.transid <= TRANSACTION.transid) AS RunningTotal
FROM   [transaction];

which is the same as what was originally posted in thread 6

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

However, you may or may not want to do the running sum based on the transactionID. This assume that is the order you want. My assumption is that the real order you want is sorted by date and time. Transaction ID may or may not be sort you want, and may or may not be a good surrogate for Date and Time. Since transaction ID looks like an autonumber, then is it possible you ever enter information not in perfect date time order? For example if you miss entering a race and then go back and fix it there is no way to get it into the correct order. You get some records out of order and enter them this will not be correct. I would think the more reliable solution is.

I believe now that you have a date and time, assuming that is the real order

SELECT transaction.*,
       (SELECT SUM(winlose)
        FROM   transaction AS T
        WHERE  ( T.racedate + T.racetime ) <= (
               transaction.racedate + transaction.racetime )) AS RunningTotal
FROM   [transaction]
ORDER  BY transaction.racedate,
          transaction.racetime;

So the issue with RunningTotal is now resolved by Query 1. That's great, there are a couple of other issues that have come to mind since the initial post. See below, but basically there are 3 stages to this: 1) Entering the Meeting data - available 2 days before race day, 2) Entering bets on the next race which will be where the Result field is null, and 3) When the race is run and won where the results and other related info are entered. Unfortunately for me, I hadn't thought this through sufficiently when I first posted 30 posts ago. I apologise for the oversight, but I have 2 more questions concerning Steps 2 and 3.