Running Sum
- Thread starter vjmehra
- Start date
R
Rich
Guest
One "James Romborough" told me many years ago a stored running sum has no place in a relational db. Was he wrong?
Cosmos75
Registered User.
- Local time
- Yesterday, 19:58
- Joined
- Apr 22, 2002
- Messages
- 1,281
Pat & Rich,
Why is it that a stored running sum has no place in a database?
- at least with Access, I find times when I would like to be able to create a Chart based on a Running Total.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31806&highlight=Running
On top of the running sum, I needed to be able to check how that running total was doing against a budget. So say for four weeks, the budget was $4000. I could check the running sum against a running budget, where the running budget would be for week one = 1 * (4000/4), week 2 = 2 * (4000/4) and so on.
Also I actually took JonK's method and did an Running Count to create a Running Avg. [Which I think is cool!]
Why is it that a stored running sum has no place in a database?
- at least with Access, I find times when I would like to be able to create a Chart based on a Running Total.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31806&highlight=Running
On top of the running sum, I needed to be able to check how that running total was doing against a budget. So say for four weeks, the budget was $4000. I could check the running sum against a running budget, where the running budget would be for week one = 1 * (4000/4), week 2 = 2 * (4000/4) and so on.
Also I actually took JonK's method and did an Running Count to create a Running Avg. [Which I think is cool!]
Last edited:
Sometimes we may need to denormalize a database.
In the book "Access 97 Developer's Handbook" Third Edition (SYBEX), the authors, when teaching Database Design, laid down some guidelines on breaking the rules of normalization.
<Quote>
Breaking the Rules: When to Denormalize
Sometimes it's necessary to break the rules of normalization and create a database that is deliberately less normal than Third Normal Form. You'll usually do this for performance reasons or because the users of the database demand it. While this won't get you any points with database design purists, ultimately you have to deliver a solution that satisfies your users. If you do decide to break the rules and denormalize your database, however, it's important that you follow these guidelines:
- Break the rules deliberately; have a good reason for denormalizing.
- Be fully aware of the trade-offs this decision entails.
- Thoroughly document your decision.
- Create the necessary application adjustments to avoid anomalies.
[The authors gave two scenarios:]
- You decide to store an indexed computed column, Soundex, in tblCustomer to improve query performance, in violation of 3NF (because Soundex is dependent on LastName).
- To improve report performance, you decide to create a column named TotalOrderCost that contains a sum of the cost of each order item in tblOrder. This violates 2NF because TotalOrderCost is not dependent on the primary key of the table. ...... Since you often create reports that need to include the total order cost but not the cost of individual items, you break 2NF to avoid having to join these two tables every time this report needs to be generated.
<End quote>
The second scenario demonstrated the need of storing a calculated field in a table, though in violation of relational database design. I wonder what "James Romborough" would say about this.
In the book "Access 97 Developer's Handbook" Third Edition (SYBEX), the authors, when teaching Database Design, laid down some guidelines on breaking the rules of normalization.
<Quote>
Breaking the Rules: When to Denormalize
Sometimes it's necessary to break the rules of normalization and create a database that is deliberately less normal than Third Normal Form. You'll usually do this for performance reasons or because the users of the database demand it. While this won't get you any points with database design purists, ultimately you have to deliver a solution that satisfies your users. If you do decide to break the rules and denormalize your database, however, it's important that you follow these guidelines:
- Break the rules deliberately; have a good reason for denormalizing.
- Be fully aware of the trade-offs this decision entails.
- Thoroughly document your decision.
- Create the necessary application adjustments to avoid anomalies.
[The authors gave two scenarios:]
- You decide to store an indexed computed column, Soundex, in tblCustomer to improve query performance, in violation of 3NF (because Soundex is dependent on LastName).
- To improve report performance, you decide to create a column named TotalOrderCost that contains a sum of the cost of each order item in tblOrder. This violates 2NF because TotalOrderCost is not dependent on the primary key of the table. ...... Since you often create reports that need to include the total order cost but not the cost of individual items, you break 2NF to avoid having to join these two tables every time this report needs to be generated.
<End quote>
The second scenario demonstrated the need of storing a calculated field in a table, though in violation of relational database design. I wonder what "James Romborough" would say about this.
Last edited:
Is it possible to use this function for three columns (e.g. score 1, score 2 and score 3), which is what I actually have, I just assumed it would be easy enough to modify your code myself so I didn't mention it, however I discovered I couldn't, so if you feel like helping me out again that would be great.
- Local time
- Yesterday, 20:58
- Joined
- Feb 19, 2002
- Messages
- 47,079
Rich, James was not wrong. Have you run into him anywhere? He always had such sound advice and beautiful numeric functions. I think he is an actuary or something like that.
Cosmos, there is a difference between a running sum and a balance. Most people only NEED a balance but they WANT to see a running sum. You can always create a running sum in a temp table with code if you need to for a graph or something. You all work on such small databases that you never see what happens once your table starts to grow. Try doing a running sum in a query on a table with 10,000 rows. That's not even large. Oh, and make sure your back end db is on the network.
Cosmos, there is a difference between a running sum and a balance. Most people only NEED a balance but they WANT to see a running sum. You can always create a running sum in a temp table with code if you need to for a graph or something. You all work on such small databases that you never see what happens once your table starts to grow. Try doing a running sum in a query on a table with 10,000 rows. That's not even large. Oh, and make sure your back end db is on the network.
Hello again, thanks for all your help so far, but the further on I get with this DB the more I realise I can't do! So far the code you have given me is working fine, however I had actually intended to use this for a slightly more complex process which I thought I'd be able to work out, but I can't get my head round it.
1) Ok basically the first thing I have been trying to do is get a running count of all the scores entered (i.e. only those above 0), I thought I would be able to modify your code slightly to do this by changing the dtotal to a dcount, but I wasn't quite sure how to go about this, so I thought I'd leave it to someone who actually knows what they are doing.
2) I then want to create an average, which is simple enough, that will just be Running/Count.
3) This next bit may be a bit tricky, basically I then want to create another field which is calculating a handicap score (this is calculated as 0.67*(200-Average), however I want this handicap to appear for the following record, i.e. if scores are entered on the 02/02/03 and the next record is dated 02/03/04 I want the handicap from the first record to appear for the second. Looking at your code it may be possible to use your dmax statement to somehow pull this out, I'm going to carry on playing around with it, but I am unlikely to get very far so if you get a chance to help me out that would be great.
Oh one more thing, which is actually probably the most important part, and the part I am having the most trouble with. Basically each record is assigned to a different person, based on a number (eg. John is 1, Mike is 2 etc.), I need the running sum (and all the other parts to apply to each person rather than the DB). I have attached a sample DB to show you what I have so far.
Cheers,
Vidal
1) Ok basically the first thing I have been trying to do is get a running count of all the scores entered (i.e. only those above 0), I thought I would be able to modify your code slightly to do this by changing the dtotal to a dcount, but I wasn't quite sure how to go about this, so I thought I'd leave it to someone who actually knows what they are doing.
2) I then want to create an average, which is simple enough, that will just be Running/Count.
3) This next bit may be a bit tricky, basically I then want to create another field which is calculating a handicap score (this is calculated as 0.67*(200-Average), however I want this handicap to appear for the following record, i.e. if scores are entered on the 02/02/03 and the next record is dated 02/03/04 I want the handicap from the first record to appear for the second. Looking at your code it may be possible to use your dmax statement to somehow pull this out, I'm going to carry on playing around with it, but I am unlikely to get very far so if you get a chance to help me out that would be great.
Oh one more thing, which is actually probably the most important part, and the part I am having the most trouble with. Basically each record is assigned to a different person, based on a number (eg. John is 1, Mike is 2 etc.), I need the running sum (and all the other parts to apply to each person rather than the DB). I have attached a sample DB to show you what I have so far.
Cheers,
Vidal
>>
.... get a running count of all the scores entered (i.e. only those above 0)
<<
On re-reading your post, I find that I have overlooked the part about counting only those scores that are above 0 and have counted the number of records instead.
Here is a revised DB, in which the figure of Count represents the number of scores that are above 0.
.... get a running count of all the scores entered (i.e. only those above 0)
<<
On re-reading your post, I find that I have overlooked the part about counting only those scores that are above 0 and have counted the number of records instead.
Here is a revised DB, in which the figure of Count represents the number of scores that are above 0.
Thanks again, there is just one small thing I was wondering if you could help me with. Is it possible to make the 'Handicap' Value 0 for the first record, as I want to be able to display HSeries for every record, which is fine apart from Hseries comes up as 0 for record 1, also as an example if the score in game 2 is 0 would it be possible to have h2 displayed as 0?
I have added HCap=0 in the code to initialise Handicap as zero, and have used an IIF expression in the Control Source of H1, H2, and H3 to return 0 when Handicap is 0 (i.e. the first record) or if Game1, Game2 or Game3 is 0.
Once again, thanks for your help, just one question, I tried to run the following if statement =IIf("([H1]+[h2]+[h3])>([game1]+[game2]+[game3])",[h1]+[h2]+[h3],[series])
but it always returns the first argument, even though for the first record it shouldn't. Any idea why this might be happening?
Cheers,
Vidal
but it always returns the first argument, even though for the first record it shouldn't. Any idea why this might be happening?
Cheers,
Vidal
- Local time
- Yesterday, 20:58
- Joined
- Feb 19, 2002
- Messages
- 47,079
Jon, if I ask nicely will you build my next app for me 
Cosmos75
Registered User.
- Local time
- Yesterday, 19:58
- Joined
- Apr 22, 2002
- Messages
- 1,281
Pat Hartman said:
Pat,
What do you mean by balance here?
Is there a difference between using JonK's query and using code to create a Running Sum? Would the code you to make the temp table be using SQL or DAO recordset?
Pat,
What do you mean by balance here?
Is there a difference between using JonK's query and using code to create a Running Sum? Would the code you to make the temp table be using SQL or DAO recordset?
- Local time
- Yesterday, 20:58
- Joined
- Feb 19, 2002
- Messages
- 47,079
A balance is like inventory on hand. I need to know what I have available now but I don't need to see a list of transactions and what the balance was after each transaction. A running sum is what you would see in a check register. Every time you add a new check you subtract it from the previous balance and show the current balance next to each transaction record. We think of this as normal and it looks like it should be simple to calculate. We do it all the time, write the new check or deposit amount and add or subtract to obtain the current balance. 1 arithmetic operation. Not so with a query. Read on.
Writing code to calculate a running sum and writing the results to a table as you go is reasonably efficient. It is a simple read until EOF loop and inside the loop you add the amount of the current row to a variable and write an output record containing the key information, the amount, and the contents of the variable at that time. The calculation requires only a single pass through the table. Calculating a running sum in a query requires that a totals query that sums all the previous rows runs for EVERY row in the in the table. To give you some idea of the magnitude of this, a running sum calculated in code and written either to a table/file/report needs to read 100 records and write 100 records. A running sum calculated in a query for the same 100 records, needs to read 4950 records and update 100 records or if you use the technique in the sample db that this post has been using, those 100 records are updated 100 * 100 (every record is updated every time) times or 10,000 times and the 4950 records are read 4950 * 100 times or 495,000 reads all together.
Of course, you guys are not seeing the problem here because you're working with a table containing only a few rows.
Writing code to calculate a running sum and writing the results to a table as you go is reasonably efficient. It is a simple read until EOF loop and inside the loop you add the amount of the current row to a variable and write an output record containing the key information, the amount, and the contents of the variable at that time. The calculation requires only a single pass through the table. Calculating a running sum in a query requires that a totals query that sums all the previous rows runs for EVERY row in the in the table. To give you some idea of the magnitude of this, a running sum calculated in code and written either to a table/file/report needs to read 100 records and write 100 records. A running sum calculated in a query for the same 100 records, needs to read 4950 records and update 100 records or if you use the technique in the sample db that this post has been using, those 100 records are updated 100 * 100 (every record is updated every time) times or 10,000 times and the 4950 records are read 4950 * 100 times or 495,000 reads all together.
Of course, you guys are not seeing the problem here because you're working with a table containing only a few rows.
vjmehra, when I copied the formula to the Control Source of a new text box, it ran fine in my database. I have no idea why an error should pop up.
>
Jon, if I ask nicely will you build my next app for me
>
Pat, you are my Access mentor. I have learnt so much from reading your posts. My solutions are usually superficial. Yours always show much deeper insight and deal with the issues at the very core.
>
Jon, if I ask nicely will you build my next app for me
>
Pat, you are my Access mentor. I have learnt so much from reading your posts. My solutions are usually superficial. Yours always show much deeper insight and deal with the issues at the very core.
