Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Total (2002)

    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

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total (2002)

    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.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Running Total (2002)

    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

  4. #4
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total (2002)

    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

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total (2002)

    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?

  6. #6
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total (2002)

    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

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

    Re: Running Total (2002)

    Why not just post a zipped version here?
    Pat

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

    Re: Running Total (2002)

    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.
    Charlotte

  9. #9
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total (2002)

    The file does not Zip to less than 100K.

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

    Re: Running Total (2002)

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

Posting Permissions

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