Running Count in Query - over multiple years

[Vikas]

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

[MarcHG]

Vikas,

I don't have experience with the dcount-function, but I suppose the
and-condition in the comparison is fatal. I rather woud try something
like "between ""2006-01"" and format(schIssueDate,""yyyy-mm"")", but
more in general I probably would avoid dcount en I would use the
standard count-function using a calendar-table containing dates used
for intervals and joining this with a non-equi-join expression like
"format(tblControl.schIssueDate,"yyyy-mm"),count(*) where
tblControl.schIssueDate >= #2006/01/01# and tblControl.schIssueDate <=
calendar.monthstart and calendar.monthstart between '2006-01-01# and
#2008-01-01# group by format(tblControl.schIssueDate,"yyyy-mm");" or
some other interval.
The non-equijoin expression cannot be specified using connection-lines
in the design-window of the query-editor but could be specified
implicitly in field conditions or the sql-window using explicit
join-syntax. I did not test this, but I am pretty sure a working
solution should always be possible this way using standard
sql-constructs.

Vikas schreef:

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