running sum in a query (Access 2000)

Topic

New Reply

Any ideas on how to create a running sum field in a query….i.e. Field BALANCE to runsum Field AMOUNT

Reply | Quote

Replies

Microsoft has some examples of this:
ACC2000: How to Create Running Totals in a Query (with link to sample database)
ACC2000: How to Create a Grouped Running Sum in a Query

Reply | Quote

Hans,

I put this to use in my query and am having trouble with the formatting. I want the result to come back with 2 decimal places but the properties for the field do not allow for decimals. I think I have a text field when I need a number field. Could you please help me with that? This is what I am using in the field of my query.

RunSum: fncRunSum([EmpAutoNum],[tblAbsenceType]![Points])

Thanks so much for your help.

Reply | Quote

Try entering 0.00 or Fixed in the Format property of the RunSum field.

Reply | Quote

Hans,

No, that didn’t work either. The two fields that I am using are EmpAutoNo, which is an autonumber field that I added so I could group the points together. It is set up as Fixed but does not allow setting of decimals. (The actual employee ID that we use is a combination of text and numbers and the running sum didn’t work when I used that.) The other field is points and it is set up in the table as fixed and 2 decimal points.

In the running sum field I also tried 0.## and I got a decimal point but it still didn’t show anything to the right of it. Don’t know what that means, just thought I would throw that in. Thanks again for any help you can provide.

Reply | Quote

Sorry, no idea. The RunSum field doesn’t have a Decimals property in my test either, but setting Format to 0.00 or Fixed works OK. Perhaps you could post a severely stripped down, zipped copy of your database?

Reply | Quote

Hans,

I really appreciate your help. I’ve attached the DB. I really stripped it down, the query, qryAbsenceEntry, is the one that I am trying to get the running sum working on.

Again, thanks for your help. What a terrific resource you and this whole board is.

Reply | Quote

Don,

In the function fncRunSum you define all your variables as Long. Long are whole numbers. Change your dim statements to Single.

Function fncRunSum(lngCatID As Single, lngUnits As Single) As Single
    'Variables that retain their values.
    Static lngID As Single
    Static lngAmt As Single

Reply | Quote

Thank you, thank you.

I had tried changing it to “Decimal” but that didn’t do anything.

One last problem, and Microsoft warns about this. Boy, were they right. I am using the results of this query in a form. The warning was that if you scroll up and down in the form, the numbers may change. Yep, they sure do. It almost seems like it can only show the running sum for the numbers that are actually on the screen. Sometimes hitting F9 changes them to the correct sums and sometimes, it just start blowing them up. Does anyone know of a work around for this problem? Again, thanks for the help.

Reply | Quote

What you could do is to create a temporary table with the running sum in it as well as the other data that the form needs and use this as the source to the form.

Another alternative is to introduce a runningsum column and update this column in table tblAbsence prior to the running form.

Reply | Quote

What you could do is to create a temporary table with the running sum in it as well as the other data that the form needs and use this as the source to the form.

Another alternative is to introduce a runningsum column and update this column in table tblAbsence prior to the running form.

Reply | Quote

You hadn’t initially mentioned using this in a form, and personally, I don’t understand what you’re trying to do. You can calculate this value but it isn’t going to get stored anywhere, so what’s the point of trying to display it on a form?

Reply | Quote

Hi Charlotte,

I hadn’t mentioned that I would be using it on a form, because I figured that once I got it in the query, I could use the form to show the user the value. Little did I know? And, I keep discovering, how little I know.

What I am trying to do is this. We have created a database to keep track of our employees absences. We have a system where, the employees accrue points for excessive absences, late arrivals or early departures. When they get to different levels, we have a requirement that they receive a review, a verbal warning, a written warning, suspension and ultimately, termination. I want to set it up so that the system will tell us when someone has reached each level and also if they have received their appropriate level of “counseling”. Further complicating things is that the points are based on a rolling 12 months, so everything needs to be constantly recalculated. I had tried setting up a system to keep track of this and have not been successful. Hans has been helpful but I think that I am just not “getting it”.

What I was trying to do with this query and form is to just create a list that would show each of the employees incidents for each time that they earned a point and have a column with a running sum, so that we could look at it and see when they hit each point level. I also have a field called comments and the supervisor can just put in a comment that states that they have provided the employee with the appropriate counseling. With the running sum field acting so wacky, I don’t think that is going to be a good solution either.

I am not a programmer but know just enough about VBA code to do simple tasks, nothing too complicated. I realize that what I am ultimately trying to do is quite complicated, at least for me. I really do appreciate all the help that I get from the Lounge and it’s members. I keep wishing that I could help somebody else, but usually by the time I see a problem that I can actually answer, and there aren’t many, I’m too late, somebody else has already taken care of it, which speaks to what a great resource this is. Thanks to you and everyone else that is so helpful.

Reply | Quote

How about another approach?

Set up a sub-form showing the employee absences for each employee and do the totalling on that. You would need to be careful with your sub-form recordsource to only include the last 12 months worth of records for each employee. Running sums in queries are usually very slooooooow and have other problems as you are finding.

Just my worth.

Reply | Quote

How about another approach?

Set up a sub-form showing the employee absences for each employee and do the totalling on that. You would need to be careful with your sub-form recordsource to only include the last 12 months worth of records for each employee. Running sums in queries are usually very slooooooow and have other problems as you are finding.

Just my worth.

Reply | Quote

SteveH suggested an alternative approach. Yet another one would be to display the running sum in a report. That is much easier, since reports have built-in facilities for running sums – you can set the Running Sum property of a text box to Over Group or Over All, so you don’t need to do modify queries at all. On the form, you could just calculate the number of points the way discussed in an earlier thread.

Reply | Quote

I know it’s been a long time since the original responses, but it’s still giving me problems. We were able to work around it for a while, now I need to fix it.

One of the problems that I have is that employees can earn bonus points that subtract from their total, however one of the rules is that their total points can never go below 0. I worked that out and added some code to the function that adjusts for that. I have to be careful that all calculations are done on items sorted by date, or it will get thrown off. This does seem to work, one of the problems is that when displaying a query, you need to recalculate constantly as you scroll down the screen. I have seen in other posts these problems, I just can’t avoid it.

What I had before was a form that would show the employees points and then a text box with a sum in it, to show the total. Now that I have to deal with the negatives that never go below 0, I found that putting the information in a report keeps it more accurate. Of course, I’m running into one more problem. I have an Employees form, that shows the information on each individual form, and then a subform to enter the points for each employee. I also have created a report, that links to the Employees form, when the user clicks the button, a report runs for just that employee, with accurate information. Now, here is the problem. If you open the form for a particular employee, then click on the button to see his Point Total report, click close, and then open the report again, now the total includes his original total, plus the total added again. In other words, for Employee A, he has a total of 1.5 points, the first time you open the report that’s what it shows, the second time, it shows 3 points, 3rd time 4.5 points, 4th time 6 points and so on. If you go back to the employee page, select another employee, open the report, close it and then go back to the first employee, the information is reset correctly and it starts over again.

The total comes from a Totals query that uses the Last entry from the running sum column. What can I put in to reset this amount each time I open the Report? I would think that it would be in the OnOpen or OnClose event for the report, I’m just not sure what it would be. Just a note, when I close the report, I am using the Close button on the tool bar. Thanks for your help.

Reply | Quote

I don’t know if this is the best way and haven’t test it.
Add a third parameter to the fncRunSum, a boolean variable.
In the query , set the third parameter to false.
In the on open report, call the function with the two first parameter set to 0 and the third to True.
In the function, add code to test if the third parameter is true and set lngAmt and fncRunSum to 0 and exit the function.

If you need help with the code, feel free to ask.

Reply | Quote

I don’t know if this is the best way and haven’t test it.
Add a third parameter to the fncRunSum, a boolean variable.
In the query , set the third parameter to false.
In the on open report, call the function with the two first parameter set to 0 and the third to True.
In the function, add code to test if the third parameter is true and set lngAmt and fncRunSum to 0 and exit the function.

If you need help with the code, feel free to ask.

Reply | Quote

I know it’s been a long time since the original responses, but it’s still giving me problems. We were able to work around it for a while, now I need to fix it.

One of the problems that I have is that employees can earn bonus points that subtract from their total, however one of the rules is that their total points can never go below 0. I worked that out and added some code to the function that adjusts for that. I have to be careful that all calculations are done on items sorted by date, or it will get thrown off. This does seem to work, one of the problems is that when displaying a query, you need to recalculate constantly as you scroll down the screen. I have seen in other posts these problems, I just can’t avoid it.

What I had before was a form that would show the employees points and then a text box with a sum in it, to show the total. Now that I have to deal with the negatives that never go below 0, I found that putting the information in a report keeps it more accurate. Of course, I’m running into one more problem. I have an Employees form, that shows the information on each individual form, and then a subform to enter the points for each employee. I also have created a report, that links to the Employees form, when the user clicks the button, a report runs for just that employee, with accurate information. Now, here is the problem. If you open the form for a particular employee, then click on the button to see his Point Total report, click close, and then open the report again, now the total includes his original total, plus the total added again. In other words, for Employee A, he has a total of 1.5 points, the first time you open the report that’s what it shows, the second time, it shows 3 points, 3rd time 4.5 points, 4th time 6 points and so on. If you go back to the employee page, select another employee, open the report, close it and then go back to the first employee, the information is reset correctly and it starts over again.

The total comes from a Totals query that uses the Last entry from the running sum column. What can I put in to reset this amount each time I open the Report? I would think that it would be in the OnOpen or OnClose event for the report, I’m just not sure what it would be. Just a note, when I close the report, I am using the Close button on the tool bar. Thanks for your help.

Reply | Quote

SteveH suggested an alternative approach. Yet another one would be to display the running sum in a report. That is much easier, since reports have built-in facilities for running sums – you can set the Running Sum property of a text box to Over Group or Over All, so you don’t need to do modify queries at all. On the form, you could just calculate the number of points the way discussed in an earlier thread.

Reply | Quote

Hi Charlotte,

I hadn’t mentioned that I would be using it on a form, because I figured that once I got it in the query, I could use the form to show the user the value. Little did I know? And, I keep discovering, how little I know.

What I am trying to do is this. We have created a database to keep track of our employees absences. We have a system where, the employees accrue points for excessive absences, late arrivals or early departures. When they get to different levels, we have a requirement that they receive a review, a verbal warning, a written warning, suspension and ultimately, termination. I want to set it up so that the system will tell us when someone has reached each level and also if they have received their appropriate level of “counseling”. Further complicating things is that the points are based on a rolling 12 months, so everything needs to be constantly recalculated. I had tried setting up a system to keep track of this and have not been successful. Hans has been helpful but I think that I am just not “getting it”.

What I was trying to do with this query and form is to just create a list that would show each of the employees incidents for each time that they earned a point and have a column with a running sum, so that we could look at it and see when they hit each point level. I also have a field called comments and the supervisor can just put in a comment that states that they have provided the employee with the appropriate counseling. With the running sum field acting so wacky, I don’t think that is going to be a good solution either.

I am not a programmer but know just enough about VBA code to do simple tasks, nothing too complicated. I realize that what I am ultimately trying to do is quite complicated, at least for me. I really do appreciate all the help that I get from the Lounge and it’s members. I keep wishing that I could help somebody else, but usually by the time I see a problem that I can actually answer, and there aren’t many, I’m too late, somebody else has already taken care of it, which speaks to what a great resource this is. Thanks to you and everyone else that is so helpful.

Reply | Quote

You hadn’t initially mentioned using this in a form, and personally, I don’t understand what you’re trying to do. You can calculate this value but it isn’t going to get stored anywhere, so what’s the point of trying to display it on a form?

Reply | Quote

Thank you, thank you.

I had tried changing it to “Decimal” but that didn’t do anything.

One last problem, and Microsoft warns about this. Boy, were they right. I am using the results of this query in a form. The warning was that if you scroll up and down in the form, the numbers may change. Yep, they sure do. It almost seems like it can only show the running sum for the numbers that are actually on the screen. Sometimes hitting F9 changes them to the correct sums and sometimes, it just start blowing them up. Does anyone know of a work around for this problem? Again, thanks for the help.

Reply | Quote

Don,

In the function fncRunSum you define all your variables as Long. Long are whole numbers. Change your dim statements to Single.

Function fncRunSum(lngCatID As Single, lngUnits As Single) As Single
    'Variables that retain their values.
    Static lngID As Single
    Static lngAmt As Single

Reply | Quote

Hans,

I really appreciate your help. I’ve attached the DB. I really stripped it down, the query, qryAbsenceEntry, is the one that I am trying to get the running sum working on.

Again, thanks for your help. What a terrific resource you and this whole board is.

Reply | Quote

Sorry, no idea. The RunSum field doesn’t have a Decimals property in my test either, but setting Format to 0.00 or Fixed works OK. Perhaps you could post a severely stripped down, zipped copy of your database?

Reply | Quote

Hans,

No, that didn’t work either. The two fields that I am using are EmpAutoNo, which is an autonumber field that I added so I could group the points together. It is set up as Fixed but does not allow setting of decimals. (The actual employee ID that we use is a combination of text and numbers and the running sum didn’t work when I used that.) The other field is points and it is set up in the table as fixed and 2 decimal points.

In the running sum field I also tried 0.## and I got a decimal point but it still didn’t show anything to the right of it. Don’t know what that means, just thought I would throw that in. Thanks again for any help you can provide.

Reply | Quote

Try entering 0.00 or Fixed in the Format property of the RunSum field.

Reply | Quote

Hans,

I put this to use in my query and am having trouble with the formatting. I want the result to come back with 2 decimal places but the properties for the field do not allow for decimals. I think I have a text field when I need a number field. Could you please help me with that? This is what I am using in the field of my query.

RunSum: fncRunSum([EmpAutoNum],[tblAbsenceType]![Points])

Thanks so much for your help.

Reply | Quote

Hi Hans, I went through those articles in fine detail but there is just something I’m missing because it is giving me just the overall balance in each record and not doing it on each ID field.
Thanks in Advance and here is the SQL code where “acct-bank-Vystar chk9912” = table, and the [amount$] and [id] are fields in the query and in the table itself.

SELECT [ACCT-BANK-Vystar CHK 9912].ID, Sum([ACCT-BANK-Vystar CHK 9912].[Amount$]) AS [SumOfAmount$], Format(DSum(“amount$”,”acct-bank-Vystar chk 9912″,[id]>0),”$0,000.00″) AS Balance, [ACCT-BANK-Vystar CHK 9912].Date
FROM [ACCT-BANK-Vystar CHK 9912]
GROUP BY [ACCT-BANK-Vystar CHK 9912].ID, [ACCT-BANK-Vystar CHK 9912].Date;

Reply | Quote

Try replacing

Format(DSum(“amount$”,”acct-bank-Vystar chk 9912″,[id]>0),”$0,000.00″) AS Balance

by

Format(DSum(“amount$”,”acct-bank-Vystar chk 9912″,”[ID]<" & [ID] & " Or ([ID]=" & [ID] & " And [Date]<=#" & Format([Date],"mm/dd/yy") & "#)"),"$0,000.00") AS Balance

Reply | Quote

Try replacing

Format(DSum(“amount$”,”acct-bank-Vystar chk 9912″,[id]>0),”$0,000.00″) AS Balance

by

Format(DSum(“amount$”,”acct-bank-Vystar chk 9912″,”[ID]<" & [ID] & " Or ([ID]=" & [ID] & " And [Date]<=#" & Format([Date],"mm/dd/yy") & "#)"),"$0,000.00") AS Balance

Reply | Quote

Hi Hans, I went through those articles in fine detail but there is just something I’m missing because it is giving me just the overall balance in each record and not doing it on each ID field.
Thanks in Advance and here is the SQL code where “acct-bank-Vystar chk9912” = table, and the [amount$] and [id] are fields in the query and in the table itself.

SELECT [ACCT-BANK-Vystar CHK 9912].ID, Sum([ACCT-BANK-Vystar CHK 9912].[Amount$]) AS [SumOfAmount$], Format(DSum(“amount$”,”acct-bank-Vystar chk 9912″,[id]>0),”$0,000.00″) AS Balance, [ACCT-BANK-Vystar CHK 9912].Date
FROM [ACCT-BANK-Vystar CHK 9912]
GROUP BY [ACCT-BANK-Vystar CHK 9912].ID, [ACCT-BANK-Vystar CHK 9912].Date;

Reply | Quote