Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    running sum in a query (Access 2000)

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running sum in a query (Access 2000)

    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

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: running sum in a query (Access 2000)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running sum in a query (Access 2000)

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

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: running sum in a query (Access 2000)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running sum in a query (Access 2000)

    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?

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: running sum in a query (Access 2000)

    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.
    Attached Files Attached Files

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running sum in a query (Access 2000)

    Don,

    In the function fncRunSum you define all your variables as Long. Long are whole numbers. Change your dim statements to Single. <pre>Function fncRunSum(lngCatID As Single, lngUnits As Single) As Single
    'Variables that retain their values.
    Static lngID As Single
    Static lngAmt As Single
    </pre>

    Francois

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: running sum in a query (Access 2000)

    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.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: running sum in a query (Access 2000)

    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.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: running sum in a query (Access 2000)

    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? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: running sum in a query (Access 2000)

    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.

  13. #13
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running sum in a query (Access 2000)

    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 <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running sum in a query (Access 2000)

    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.

  15. #15
    Star Lounger
    Join Date
    Jul 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running sum in a query (Access 2000)

    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;

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •