Running Total (2002)

Topic

New Reply

Good morning all.

I need to calculate a running total in a query. I want a calculated field that will allow me to add up another field as we go. Can this be done in a query? I know it works in Reports, but that doesn’t help me, it needs to happen in a query.

Thanks,

Dave

Reply | Quote

Replies

This is a stab in the dark. You could try a subquery as a field to generate the running total. However if your main query returns lots of records then it would be horribly inefficient.

Another stab is to call a custom function with the current field to append and said function maintains the running total. That is probably more efficient but you would need to re-initialise the total in the function before calling the query (more prone to a programming error that will give false results except for the first run after it is initialised when it will always work correctly).

Why do you need it in a query rather than a report? There may be another way to achive what you want to do.

Reply | Quote

Recommend check out a couple of MSKB articles that show how to do this in a query:

ACC2000: How to Create Running Totals in a Query

ACC2000: How to Create a Grouped Running Sum in a Query

The same basic techniques illustrated apply to most versions of Access.

HTH

Reply | Quote

Thanks Mark. This appears to be just what I need. I did end up playing some with DSUM this afternoon, but didn’t get any where. I look forward to going through the article and checking this out. Seems odd that this would be so difficult to do. It certainly is a function folks would need periodically.

Dave

Reply | Quote

Have you made this work yet?

All I am getting is “error” where my runing sums should be and if I click on any “error” it tells me it cannot find the automation object and refers to the id in that particual row (I have replaced Empid with tourID in my data) rather than my column heading?

Reply | Quote

Hi Roberta. Yes, I did get it to work. It was a pain in the ass to do so, but it did. It turned out the rest of the project I was working on didn’t work, so I never put it into use. I ended up stealing a query from the Northwind database and using it as a guide. I’ve isolated that query. If you’ll e-mail me directly, I’ll send you the database with just the query in it.

Dave

Reply | Quote

Why not just post a zipped version here?
Pat

Reply | Quote

I agree with Pat. If you’ve solved your problem, it would help if you post your solution here so that others can benefit. If you zip your file and get it down to 100Kb or less, you can post it as an attachment.

Reply | Quote

The file does not Zip to less than 100K.

Reply | Quote

Then email it to me. My email address is in my profile.
Pat

Reply | Quote