Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Milestone
    etc...

    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!

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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]
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  4. #4
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  5. #5
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Quote Originally Posted by johnhutchison View Post
    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?

    Ian

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Ian

    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.

    Code:
    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.

    Code:
    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.
    Code:
    SELECT tblIssues.IssueID, Count(tblIssues.IssueID) AS IssuesCount
    FROM tblIssues, tblIssues AS nn
    WHERE (((nn.DueDate)<=[tblissues].[duedate]))
    GROUP BY tblIssues.IssueID;
    Regards
    John



Posting Permissions

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