Running total in MS Access

In an MS Access query, I want one column to be the running total of another column. I tried using DSUM as explained in https://support.microsoft.com/en-us/help/290136/how-to-create-a-running-totals-query-in-microsoft-access

But that has two problems:

1) It makes the query extremely slow (The query is based on another query which takes a few seconds to run. There are about 100 records, which I guess multiplies that time taken, because the base query needs to run once for each record).
2) I am importing the output of this query into MS Excel, and I expect the data to be refreshable without even opening MS Access. Functions such as DSUM will only work within Access

Is there a way to solve both problems? I also need to solve Problem #2 for another custom function I wrote for another query.
Member_2_7966563Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
would help if we knew your table structure.  Assuming you have something like:

ID
TransDateTime
Amount

Then you can create a query that looks like:

SELECT T1.ID, T1.TransDateTime, T1.Amount, Sum(T2.Amount)
FROM yourTable as T1
INNER JOIN yourTable as T2 ON T1.ID = T2.ID and T1.TransDateTime >= T2.TransDateTime
GROUP BY T1.ID, T1.TransDateTime, T1.Amount

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Since you are not trying to export the data to Excel and sever its connection to Access, the best solution is to build from the Excel side.
1. Create a querydef in Access that joins the tables and selects the columns in the order you want to see them in Excel.  Do NOT attempt the running sum.
2. Create a spreadsheet and link it to the Access query.
3. Add the running total in Excel.
4. You should be able to set this to automatically refresh on open but you might want to give the user a "button" so he can do it as needed.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi,

a faster method than DSum would be to use a custom VBA function that uses a recordset to return the running sum calculation.  Here is an article that shows both methods using a query:

http://sfmagazine.com/post-entry/february-2016-access-calculating-running-sums/

have an awesome day,
crystal
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Member_2_7966563Author Commented:
Dale, here is the output that your query produced. Doesn't seem to work as intended....

ID      TransDateTime      Amount      SumOfAmount
1      1/1/2018      10      10
2      1/3/2018      20      20
3      1/5/2018      25      25
4      1/6/2018      30      30
0
Member_2_7966563Author Commented:
Pat, could you please elaborate on step 3? Do you mean with formulas or is there a way to add a running total column to the recordset before it is downloaded into an Excel table.

As of now, I am using formulas, but it is not elegant because the number of records in the table is always changing, and some user in the future might mess up the formulas.
0
Member_2_7966563Author Commented:
Crystal, if I use a VBA function, would I still be able to import the query results into Excel without actually opening MS Access?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I believe so. Excel will import values, not formulas. Nothing like trying ~
0
Member_2_7966563Author Commented:
Pat, your query worked after deleting one of the joins

SELECT T1.ID, T1.TransDateTime, T1.Amount, Sum(T2.Amount) AS SumOfAmount
FROM yourTable AS T1 INNER JOIN yourTable AS T2 ON T1.TransDateTime >= T2.TransDateTime
GROUP BY T1.ID, T1.TransDateTime, T1.Amount;

Open in new window


The output was

ID      TransDateTime      Amount      SumOfAmount
1      1/1/2018      10      10
2      1/3/2018      20      30
3      1/5/2018      25      55
4      1/6/2018      30      85
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Pat suggested adding a formula in Excel to do the running sum since Excel is much better suited than Access to calculate it -- although you would probably want to convert formulas to values if this is static data since running sums can eat up resources quickly.
0
Member_2_7966563Author Commented:
Sorry, I meant Dave, your query
0
PatHartmanCommented:
Yes I was suggesting formulas.  If you need a macro to insert them, then do that when the  spreadsheet loads.  I work with Access so I could do this if I had to but it would be a challenge.  Typically I start by opening Excel and turning on the macro recorder.  Then I step through what I need to happen and stop the recorder.  Hopefully, I have captured what I need or sometimes, I can make the final tweaks directly to the code.    For someone who regularly works with Excel macros, it shouldn't be difficult.

There is nothing elegant about doing a running sum in a query.  Queries are set operations and running sum is linear.   A running sum is trivial as a sequential process in a VBA loop or in an Access report and also very efficient but not in a query.  Queries work entirely different because queries are "live" and have to deal with rows moving around which doesn't happen in a linear process.  One record just follows the next presumably in the specific sort order needed for the running sum to make sense.

Just FYI, if you don't have a unique identifier to set the row order, you won't be able to make a running sum in a query.
0
Member_2_7966563Author Commented:
I finally adopted Dave's approach of running total in a query. The query runs swiftly ,and has a tight link to the data downloaded into Excel. This allows me to distribute a macro-free and idiot-proof Excel file.
0
Dale FyeCommented:
Glad I could help.

When I do this I usually have a ClientID or some other column in the table, and that is what I was thinking of when I added the ID to the JOIN.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.