Self-Referencing running total used to calculate next total in a query

[xav78]

Anyone and Everyone...., I have been working on a problem for over two months and I am spent trying to solve it. PLEASE PLEASE PLEASE there has to be an Access Guru to help!

Here is the issue:

I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records

I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do

If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it...lol ARRRRGGGGH! HELP!

 

[MarkK]

I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query.

This is not well reasoned. A spreadsheet and a database table are not the same thing. A spreadsheet row has an inherent position, essentially it has a y coordinate in a fixed space, so the idea of a "previous row" is clearly defined in respect to the row itself, and in respect to the space that contains all the rows.

This is not the case in a database table, where the concept of "Order" is not well defined. In a database the concept of a "sort order" is not fundamental to a record, so a row does not know anything about any other row, and a row does not have an inherent sort order. Even when you assert order with an ORDER BY clause in a query, each row is still not aware of its location in respect to other rows, nor does any row know anything about the overall space that contains all the rows.

Makes sense? A spreadsheet row knows the location of the previous row. A database row does not. This should change how you think about what kind of solutions are available using these very different tools.

If it makes sense to store your data in a database, fine, but it is possible that you need to present it in a spreadsheet, or that you need to process each row in your query prior to presentation, which you can do in a report or a listbox, or a listview, etc...

hth

 

[plog]

Not convinced its not possible. Give me until noon tomorrow central time.

 

[Uncle Gizmo]

What about running sum from Rogers Access library? Here:-

http://www.rogersaccesslibrary.com/forum/topic279.html

 

[xav78]

This is not well reasoned. A spreadsheet and a database table are not the same thing. A spreadsheet row has an inherent position, essentially it has a y coordinate in a fixed space, so the idea of a "previous row" is clearly defined in respect to the row itself, and in respect to the space that contains all the rows.

This is not the case in a database table, where the concept of "Order" is not well defined. In a database the concept of a "sort order" is not fundamental to a record, so a row does not know anything about any other row, and a row does not have an inherent sort order. Even when you assert order with an ORDER BY clause in a query, each row is still not aware of its location in respect to other rows, nor does any row know anything about the overall space that contains all the rows.

Makes sense? A spreadsheet row knows the location of the previous row. A database row does not. This should change how you think about what kind of solutions are available using these very different tools.

If it makes sense to store your data in a database, fine, but it is possible that you need to present it in a spreadsheet, or that you need to process each row in your query prior to presentation, which you can do in a report or a listbox, or a listview, etc...

hth

Thank you for your feedback, I do understand the basic inherent differences between a database and spreadsheet, please excuse my hasty generalization. From my experience with working in both Excel and Access, I have found many similarities in data manipulation techniques and was hoping to seek out someone who also may also have come across this and had a solution. While I am far from an expert in either, I don't like to give in unless I've exhausted all options.

As far as utilizing the spreadsheet method, it remains on the table. My company currently utilizes many spreadsheets for varying reasons, and much of them contain the same basic information. So my end goal is to simplify all our spreadsheets into a database and provide a user interface utilizing forms and reporting to simply the data retrieval for our employees.

The solution I am seeking is the last piece of the puzzle to complete this migration. Much time and work has gone into this already so to say we have to continue utilizing the spreadsheets is a little disheartening.

 

[xav78]

What about running sum from Rogers Access library? Here:-

Thanks for the suggestion Uncle Gizmo, I actually looked at this already and did give me some ideas but turned out it was not my solution

 

[Uncle Gizmo]

Rodger does have some other Solutions have you looked at those?

 

[xav78]

Not convinced its not possible. Give me until noon tomorrow central time.

I will patiently wait with my fingers crossed

 

[plog]

Sorry, not possible with striaght SQL, its too reliant on past calculations to do in a simple query.

It's still possible, its just going to take VBA and a lot of resources. Here's the broad strokes:

You should build a VBA function that you pass a KEY value and it returns a number which is the running total. Inside the function you are going to need a Recordset, it would read in all the records up until your key and loop through them. That loop would apply your running total rules and process each record in the record set one at a time. At the end of it all you would have your total for the key you passed and you return that from your function.

The amount of processing is a lot, so if you have a lot of data in your query its going to take some time to process the running total for all records.

 

[MarkK]

So the fastest way, I think, is don't do the calculations in the query at all. Do the calculations after the query is run, and before or during the presentation step, like in a Report or in Excel.