Running Totals (1 Viewer)

strive4peace

AWF VIP
Local time
Today, 02:20
Joined
Apr 3, 2020
Messages
1,002
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

AWF VIP
Local time
Today, 02:20
Joined
Apr 3, 2020
Messages
1,002
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

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

Member
Local time
Today, 17:20
Joined
Jan 11, 2024
Messages
91
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

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

You've got your good things, and you've got mine.
Local time
Today, 03:20
Joined
May 21, 2018
Messages
8,643
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

AWF VIP
Local time
Today, 02:20
Joined
Apr 3, 2020
Messages
1,002
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

You've got your good things, and you've got mine.
Local time
Today, 03:20
Joined
May 21, 2018
Messages
8,643
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.
 

davegoodo

Member
Local time
Today, 17:20
Joined
Jan 11, 2024
Messages
91
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.
 

Attachments

  • SampleDB.accdb
    556 KB · Views: 49

davegoodo

Member
Local time
Today, 17:20
Joined
Jan 11, 2024
Messages
91
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:20
Joined
May 7, 2009
Messages
19,248
See Query1 (last column RunningTotal).
 

Attachments

  • SampleDB.accdb
    648 KB · Views: 49

davegoodo

Member
Local time
Today, 17:20
Joined
Jan 11, 2024
Messages
91
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:20
Joined
May 7, 2009
Messages
19,248
you are 3 hours ahead of me.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:20
Joined
May 7, 2009
Messages
19,248
i did not use Date/RaceDate on the query.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:20
Joined
May 21, 2018
Messages
8,643
So my point is that 30 replies later we have
Code:
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

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
Code:
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;
 

Attachments

  • SampleDB_MajP.accdb
    928 KB · Views: 45

GPGeorge

George Hepworth
Local time
Today, 00:20
Joined
Nov 25, 2004
Messages
2,042
So my point is that 30 replies later we have
Code:
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

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
Code:
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

Super Moderator
Staff member
Local time
Today, 03:20
Joined
Feb 19, 2002
Messages
43,587
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

Member
Local time
Today, 17:20
Joined
Jan 11, 2024
Messages
91
So my point is that 30 replies later we have
Code:
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

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

Users who are viewing this thread

Top Bottom