• Create running totals


    I am trying to create a set of charts with access data that capture a running total of milestone completions. The table structure is as such:

    Due Date

    I need to structure the data for charting that shows a running total of milestones due on or before each month. I have tried inserting the following:

    select count(milestone) from table1 nn where nn.due date <=table1.due date, but I get a syntax error.

    If there is an easier way work with cumulative data, please let me know!

    Viewing 3 reply threads
    • #1274557

      Any names (tables or fields) with spaces need to be enclosed with square brackets. Try this.

      select count(milestone) from [table1 nn] where nn.[due date] <=table1.[due date]

      • #1274574

        I think the other problem I am having is that I am trying to do two counts (monthly and running total) in the same query – I seem to recall that a query can only have one count field at a time – is that right? If so, I may have to break this out into multiple queries. Is that a correct assumption – anyone?

        • #1274610

          The data table didn’t translate well when I posted the response – below is the data referred to above:

          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

      • #1276282

        Any names (tables or fields) with spaces need to be enclosed with square brackets. Try this.

        select count(milestone) from [table1 nn] where nn.[due date] <=table1.[due date]

        John: Can you clarify for me how this statement works? I thought I pretty much knew SQL, but I don't understand what is going on here. As I understand it the SELECT is using a single source table (table1) and comparing a single field (due date) accessed via the table name and via an alias (nn). I would expect table1.[due date] to always equal nn.[due date], they are after all the same field in the same row of the same table. I would have expected to see some sort of self join like:

        select count(table1.milestone) from table1, table1 nn where nn.[due date] <=table1.[due date]

        Does Access do an implied self join with the OP's syntax?


    • #1274609

      I am getting close to the solution – the next issue (and I hope last issue) is grouping the data by months. Below is the code and output where I stand now. I need to be able to group the data by month so the output shows 1 record per month.

      q Counts Running TotalRev Due DateFunction_NamesRunning Total
      Customer Service

      Power Production

      Transmission & Distribution
      2011 06Power Production12011 07Power Production22011 08Power Production32011 09Power Production122011 10Power Production142011 10Power Production152012 06Power Production162013 06Power Production172014 06Power Production18

      As you can see, there are two records for 10/2011. I need to group this into one record

      CODE for above results:

      SELECT [q counts].[Rev Due Date], [q counts].Function_Names, Sum((select Sum([Number of Milestones]) from [q counts] nn where [nn].[rev due date] <= [q counts].[rev due date] and [nn].[Function_Names] = [q counts].[Function_Names])) AS [Running Total]
      FROM [q counts]
      GROUP BY [q counts].[Rev Due Date], [q counts].Function_Names;

      As a bonus question, I would also like to ultimately create a table that has the data in a table with all months for charting purposes. I would appreciate any suggestions to accomplish that.

      Thanks, David

    • #1274667

      You can use the Month and Year functions to create calculated fields and use these for Grouping.
      So if the relevant field is [Rev Due Date] you can have RevDueYear: Year([Rev Due Date]) and RevDueMonth: Month([Rev Due Date])

      The Year is needed to separate counts from the same month in different years.

    • #1276289


      Looking back at what I wrote earlier (without actually testing it) I don’t know what I was thinking.

      The only solution I can think off at the moment (and it is late here now) is to use a Dcount. Something like this.

      SELECT tblIssues.IssueID, tblIssues.DueDate, DCount(“*”,”tblIssues”,”[DueDate]<=" & [DueDate]) AS issueCount
      FROM tblIssues

      For me I need to put the format function in to force the dates into mm/dd/yyyy format to get sensible results.

      SELECT tblIssues.IssueID, tblIssues.DueDate, DCount("*","tblIssues","[DueDate]<=#" & format([DueDate],"mm/dd/yyyy") & "#" ) AS issueCount
      FROM tblIssues

      Here is an alernative that seems to work as well.

      SELECT tblIssues.IssueID, Count(tblIssues.IssueID) AS IssuesCount
      FROM tblIssues, tblIssues AS nn
      WHERE (((nn.DueDate)<=[tblissues].[duedate]))
      GROUP BY tblIssues.IssueID;
    Viewing 3 reply threads
    Reply To: Create running totals

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: