Query: Cumulative count over time

[JamesBrewer01]

Hi,

I'm looking for the right way to set up a query to give me a cumulative count of items, over time, to populate a comparative line chart.

My table has a number of other columns, but I'm pretty sure the ones I need for this query are as below:

Unique_IDFinish_DateBaseline_Finish101/11/201201/11/2012230/11/201230/11/2012305/12/201229/11/2012407/12/201217/11/2012523/11/201223/11/2012601/01/201318/12/2012718/01/201318/01/2013818/12/201218/12/2012903/02/201328/01/20131001/01/201314/12/2013 Nov-1235Dec-1268Jan-13910Feb-1310

I think the answer lies somewhere with DCOUNT and DatePart, but I just can't work it out.

I hope I've explained that well enough, let me know if more info will help.

Thanks,
James

 

[JamesBrewer01]

Apologies for the table, hopefully the below looks better:

Unique_ID;Finish_Date;Baseline_Finish
1;01/11/2012;01/11/2012
2;30/11/2012;30/11/2012
3;05/12/2012;29/11/2012
4;07/12/2012;17/11/2012
5;23/11/2012;23/11/2012
6;01/01/2013;18/12/2012
7;18/01/2013;18/01/2013
8;18/12/2012;18/12/2012
9;03/02/2013;28/01/2013
10;01/01/2013;14/12/2013

Nov-12;3;5
Dec-12;6;8
Jan-13;9;10
Feb-13;10;

 

[plog]

I see you fixed your data, just one sec and I will have a solution.

 

[plog]

I would do this with sub-queries. Each one would get the totals for a column and in the month format you want:

SELECT (MonthName(Month([Baseline_Finish]),True) & "-" & Year([Baseline_Finish]) Mod 100) As ReportMonth, COUNT([Unique]) AS BaselineTotal
FROM YourTableNameHere
GROUP BY (MonthName(Month([Baseline_Finish]),True) & "-" & Year([Baseline_Finish]) Mod 100);

Name that 'sub_BaselineTotal' and replace 'YourTableNameHere' with the name of your table. Then create another query based on this SQL.

SELECT (MonthName(Month([Finish_Date]),True) & "-" & Year([Finish_Date]) Mod 100) As ReportMonth, COUNT([Unique]) AS FinishTotal
FROM YourTableNameHere
GROUP BY (MonthName(Month([Finish_Date]),True) & "-" & Year([Finish_Date]) Mod 100);

Name that 'sub_FinishDateTotal'. Then create another query based on those 2 sub-queries, linking them by ReportMonth field. Bring in one ReportMonth field and then the Total fields from each query and you will have your data.

The only issue you will face is if no month has either a Finish_Date or Baseline_Finish value. In that instance that month will not show at all. Is that a possiblity?

 

[JamesBrewer01]

Thanks Plog!

So I'm a little stuck with the third query I need to create. SQL queries are new to me and I'm not too sure how I would write it.

The data would never have a blank, but the Baseline_Finish column may show "NA" rather than a date - is that a problem?

 

[plog]

Yes, that that's two problems. I didn't say if they were blank they wouldn't show up, I said if a value for a month wasn't present in the data it wouldn't show up. Also, why isn't Baseline_Finish and Finish_Date date fields?

 

[JamesBrewer01]

Sorry i understand now.

1) There is a chance that a month won't be represented by a Finish_Date/Baseline_Finish
2) The data is an extract from MS Project and at source some values are recorded as "NA". There will only every be a date or "NA" however.

 

[plog]

1. To include all months, even if data isn't present for one of them in the sub-query, you will need to create a data source (query or table) that has all the months you want to report on. You would then bring this into the final query and LEFT JOIN from it to each of the sub-queries.

2. I would add criteria to each of the sub-queries to exclude values of NA.