Results 1 to 10 of 10

Thread: Create Running Totals

  1. #1
    dascooper is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    5

    Create Running Totals

    I am trying to create a query that provides a running total for the number of planned events. I have the following code that, when run, gives me a data type missmatch. Below is the subselect from the sql code. The problem lies in the use of dates as a comparison. I want to include all the previous events to have a current running sum. The problematic code is below:

    Plan: (select sum( [number planned]) from [q counts] nn where dateserial([nn].[year],[nn].[month],1) <= dateserial([q counts].[year],[q counts].[Month],1) and [nn].[Function_Names] = [q counts].[Function_Names])

    I have tried comparing year to year and month to month (which works) but doesn't give me the correct amounts (the data goes over several years).

    Any help/suggestions. The table q counts contains just four fields:

    Function_Names
    number planned
    Year
    Month

    Thanks!

  2. #2
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696

  3. #3
    dascooper is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    5
    I get the concept and have tried to build it into my query. It seems to work where the year is the same. However, I need to be able to have a running total across years. I can't quite get it to work right.

  4. #4
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    Quote Originally Posted by dascooper View Post
    I get the concept and have tried to build it into my query. It seems to work where the year is the same. However, I need to be able to have a running total across years. I can't quite get it to work right.
    For some reason, a lot of people don't understand how to make running totals work. How about posting some data you've now and then another sample of how it should look when it's all over??

    be sure to cover all the possible pitfalls/data inconsistencies that one might run into.

  5. #5
    dascooper is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    5
    Here is before and after shot of what I am trying to do:

    Rev Due Date Function_Names MCount
    Customer Service 0
    Power Production 0
    Transmission & Distribution 0
    6/1/2011 Power Production 1
    7/1/2011 Power Production 1
    8/1/2011 Power Production 1
    9/1/2011 Power Production 9
    10/1/2011 Power Production 2
    10/2/2011 Power Production 1
    6/1/2012 Power Production 1
    6/1/2013 Power Production 1
    6/1/2014 Power Production 1


    Rev Due Date Function_Names Running Total
    2011 06 Power Production 1
    2011 07 Power Production 2
    2011 08 Power Production 3
    2011 09 Power Production 12
    2011 10 Power Production 14
    2011 10 Power Production 15
    2012 06 Power Production 16
    2013 06 Power Production 17
    2014 06 Power Production 18


    The only change is that the months should be summed (note two records for 2011/10). I'm not so worried about getting it by each function name (I can deal with that later). The ultimate goal is to create a set of charts to show progress of planned vs actual number of closed projects.

  6. #6
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    cooper,

    this can be solved exactly the same way this one was: http://www.accessforums.net/forms/ho...orm-12533.html

    the sql I gave there was:

    Code:
    SELECT tblMoney.fldcode, tblMoney.flddate, 
     
    [fldvalue] + 
     
    iif(DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#") is null, 0, 
    DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#")) 
     
    + 
     
    IIf((DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) Is Null, 
    0, DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) AS Expr1
     
    FROM tblMoney
     
    ORDER BY tblMoney.fldDate;
    this portion:
    Code:
    [fldvalue] +
    ensures that the evaluated record's total field is added to the mix. The next section:
    Code:
    iif(DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#") is null, 0, 
    DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#")) 
     
    +
    sums all of the previous records where the date is LESS than the date of the record being evaluated.

    then you have the last portion:
    Code:
    IIf((DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) Is Null, 
    0, DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) AS Expr1
    which is adding to the mix (if applicable), all the totals that have the SAME date as the evaluated record, but a lower ID number (e.g. - the lower ID numbers were obviously entered before higher ones)

    and then of course you have to use an ORDER BY clause, which has to be executed first otherwise the totaling technique would not work:

    Code:
    ORDER BY tblMoney.fldDate, tblMoney.fldcode;
    that's a different clause than originally posted, only because the post is a guess without actually working it.

    So in your case, you'll use "Rev Due Date" as the date field and "MCount" as the field value from the above example. I will assume that also have an ID in an autonumber field somewhere?? If you do, you can copy that sql verbatim with name substitutes only. If you have no ID field, you'll have to make one, or another unique identifier. These kinds of things really can't be done without uniqueness, because the complexing would probably start overwhelming the sql engine.

  7. #7
    dascooper is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    5
    I guess the question is how to group the data by month?

  8. #8
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    Quote Originally Posted by dascooper View Post
    I guess the question is how to group the data by month?
    oh...sorry about that. In that example, I stacked queries. the first one selected DISTINCT records. You should do that too...select distinct records based on the year and month. e.g. - one way to do that would be:

    Code:
    select format([datefield], "mm/yyyy")
    or something of that nature. producing a two-tier stack like that will make it easier on the db engine, and will also eliminate the hassle of reading incredibly long and confusing sql statements, not to mention the fact that it eliminates the need for a GROUP BY clause which is always a headache anyway.

    stacking is very much underrated! I'd use it...

    give it a go and if you run into a snag, post back we'll help you out.

  9. #9
    dascooper is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    5
    I'm still not quite getting the results I'm looking for. I haven't used a crosstab query before, but the layout gets me very near what I'm trying to do. The problem is then applying the dcount function in a crosstab sql. I think I have it close, but am still getting syntax errors. The starting point (before applying the crosstab looks like this:

    q Initiatives_CrosstabExpr1Total Of NumberCustomer ServicePower ProductionTransmission & Distribution
    753223202011 061
    1
    2011 071
    1
    2011 081
    1
    2011 099
    9
    2011 103
    3
    2012 061
    1
    2013 061
    1
    2014 061
    1



    This is the code to obtain the above data.

    TRANSFORM Count([q Initiatives].[Number]) AS CountOfNumber
    SELECT Format([Rev Due Date],"yyyy mm") AS Expr1, Count([q Initiatives].[Number]) AS [Total Of Number]
    FROM [q Initiatives]
    GROUP BY Format([Rev Due Date],"yyyy mm")
    PIVOT [q Initiatives].[Function_Names];


    This is the code with the dcount function Applied (again, not quite working. The bold text shows where Access seems to be having trouble with.

    TRANSFORM Count([q Initiatives].[Number]) AS CountOfNumber
    SELECT Format([Rev Due Date],"yyyy mm") AS Period, dCount("*","q Initiatives","[Period] <=format([Rev Due Date],"yyyy mm") AS [Total Of Number]")
    FROM [q Initiatives]
    GROUP BY Format([Rev Due Date],"yyyy mm")
    PIVOT [q Initiatives].[Function_Names];


    Thanks for your help. I've been struggling with this for some time (I had a similar issue a few years ago, I haven't been able to apply the same logic to solving this problem).

    David

  10. #10
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    I didn't give advice on a crosstab query. I'm going to bow out of this now, because everything you need in order to do this I already provided. You're more than welcome to use it to your advantage.

    good luck to ya.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with a totals filter
    By deiniolj in forum Programming
    Replies: 11
    Last Post: 03-30-2011, 03:34 AM
  2. Select query for running totals
    By asawadude in forum Queries
    Replies: 2
    Last Post: 10-07-2010, 02:41 PM
  3. Carried over totals
    By Alex Motilal in forum Reports
    Replies: 3
    Last Post: 01-29-2010, 09:13 AM
  4. Import a totals value
    By vCallNSPF in forum Access
    Replies: 5
    Last Post: 12-13-2009, 05:01 PM
  5. Using running total in query to create graphs
    By maggioant in forum Queries
    Replies: 4
    Last Post: 10-02-2009, 04:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums