Hi all,
I am working on a document control database where I have a table
"tblControl" containing two columns "DocID" and "SchIssueDate".
"DocID" is the key field with text format while "SchIssueDate" is Date
format.
I am trying to create a S-Curve for the documents to be issued each
month to show the required progress. I have made this query for this:
SELECT Format([SchIssueDate],"mmm-yyyy") AS [Month],
DatePart("yyyy",[SchIssueDate]) AS AYear, DatePart("m",[SchIssueDate])
AS AMonth, DCount("DocID","tblControl","DatePart('m',
[SchIssueDate])<=" & [AMonth] & " And DatePart('yyyy',
[SchIssueDate])<=" & [AYear] & " ") AS RunTot
FROM tblControl
GROUP BY Format([SchIssueDate],"mmm-yyyy"),
DatePart("yyyy",[SchIssueDate]), DatePart("m",[SchIssueDate])
ORDER BY DatePart("yyyy",[SchIssueDate]), DatePart("m",[SchIssueDate]);
This works fine for one year progress. i.e. the DCount keeps counting
Documents till December. But then for January of next year it drops
the count back bacause of [SchIssueDate])<=" & [AMonth]... the AMonth
value is 1.
Here is what I get:
Month AYear AMonth RunTot
Jun-2006 2006 6 1
Nov-2006 2006 11 5
Dec-2006 2006 12 16
Jan-2007 2007 1 19
Feb-2007 2007 2 31
Mar-2007 2007 3 34
Apr-2007 2007 4 88
May-2007 2007 5 226
Jun-2007 2007 6 537
Jul-2007 2007 7 746
Aug-2007 2007 8 917
Sep-2007 2007 9 1166
Oct-2007 2007 10 1481
Nov-2007 2007 11 1685
Dec-2007 2007 12 1870
Jan-2008 2008 1 287 (- Dcount fails here to display running count)
Feb-2008 2008 2 380
Mar-2008 2008 3 391
Would greatly appreciate if anyone can suggest a solution?
Regards,
Vikas