Create running totals

Topic

New Reply

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!

Reply | Quote

Replies

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]

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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;

Reply | Quote