Results 1 to 9 of 9

Thread: Running Sum

  1. #1
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266

    Unanswered: Running Sum

    I have a table, LousGDC with 6 fields

    IDNo Autonumber
    Period Text
    CostCentre Text
    CostElement Text
    Value Currency
    fldDate Date

    I am trying to create a query that has a running sum on the Value field, grouped by Period, CostCentre and CostElement. I realise I could do it in a report, but this is not for a report. I have created a similar query, but can't seem to make it work for the other table.
    Any help appreciated.

    SELECT LousBudget.IDNo AS IDNoAlias, Sum(LousBudget.Budget) AS SumOfBudget, Format(DSum("Budget","LousBudget","[IDNo]<=" & [IDNoAlias] & ""),"0,000.00") AS RunTot
    FROM LousBudget
    GROUP BY LousBudget.IDNo;

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What doesn't work? And why are you not choosing ANY method to do this besides using a query?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Sorry, should have been a bit more explicit. I am dowloading the data from SAP into Access. I have 2 tables based on the data. One table is the budget for the year. The total budget is broken down into 12 equal parts then that is totalled as a running sum for each period. The following SQL achieves just that.

    SELECT LousBudget.IDNo AS IDNoAlias, Sum(LousBudget.Budget) AS SumOfBudget, Format(DSum("Budget","LousBudget","[IDNo]<=" & [IDNoAlias] & ""),"0,000.00") AS RunTot
    FROM LousBudget
    GROUP BY LousBudget.IDNo;

    Now, what I need to do is produce a query that does the same for actual expenditure so that I can compare actual expenditure to budget on a period to period basis. Perhaps I am going about it in the wrong way. I have attached a small DB with some dummy data.
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I can't d/l the database here... but you just described a report to me. what exactly is this for that you can't use a report?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    I have managed to partiall solve the problem. The reason I can't use a report is that I have to export the results to Excel. However I have foud a function that does the trick, although, only on one group. I have 3 groups. Looks like I will have to run 3 queries. No problem, just a bit of hassle. Many thank for the interest.

    David

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You do know you can export reports to excel... right?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Hi Teddy. I must confess that I did not know that you could export reports to excel. Perhaps you could explain how this is done. It could solve my problem.

    Many thanks

    David

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Right-click -> Export?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or Tools--> Office Links--> Analyse it with MS excel (my preferred method )
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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