MS Access Cumulative Date Count

Hi, Marcus_C.
Try to use DatePart method:

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

Good Luck
RRR.

Hello Marcus_C,

SELECT Month([DateField]), count(*),
FROM Table1
GROUP BY Month([DateField]);

Regards,
Paasky

I get the following message, "the select statement includes a reserved word or an argument name is mispelled or missing"

Will this function cumulative count a range?

One problem with paasky's suggestion: all the Januarys (no matter which year they fall in) will be lumped together. To get both the detail (like your Crosstab) and the summary, you'd have some serious maneuvering to do. I might create a function like:

Public Function CumulativeCounts(intYear as integer, bytMonth as byte)

   CumulativeCounts = DCount("SomeField", "YourTable", "Year(DateField) <= " & intYear & " and Month(DateField) <= " & intMonth)

End Function

You'd put this into your query, something like this in the Field row:

Cumulative: CumulativeCounts(Year(DateField), Month(DateField))

Would that work?

brewdog

Marcus_C,

Place your date field into [DateField]. Here's a sample:

Table CONTRIBUTION has field
Contribution_Date

With the following query (Q_COUNT) you can count the rows by month.

SELECT Choose(Month(Contribution_date),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") As MonthName,  
Count(*) AS MonthCount
FROM CONTRIBUTION
WHERE Year(Contribution_Date) = 2000
GROUP BY Month(Contribution_date)
ORDER By 1;

Result:

MonthName      MonthCount
Apr              1
Jan              4
Mar              2

I guess you're counting one year per time so, in my example year 2000.

Regards,
Paasky

Marcus_C,

Could you possible do the cumulative counting in your report (it's possible with Access reports...) . Brewdogs solution should work fine, but it may take really long time counting that way (120,000 rows!)

Regards,
Paasky

Here's a sample code I have made earlier and modified now for you.

You need to make query (Q_COUNT) as follows:

SELECT Year(Contribution_Date) AS Year, Month(Contribution_date) AS Month, Count(*) AS MonthCount
FROM CONTRIBUTION
GROUP BY Year(Contribution_Date), Month(Contribution_date);

Then I have made "sum" table:
TABLE: tblCumulative
Year   NUMBER,
Month  NUMBER,
CumulativeCount NUMBER

This code counts the cumulative count from the year you enter as a parameter and makes record for each month:

Sub Cumulative_Count(YearFrom As Integer)

    Dim db As Database
    Dim qry As Recordset
    Dim rst As Recordset
    Dim lMonthCount As Long
    Dim strSQL As String
    Dim iMonth As Integer, iYear As Integer

    strSQL = "SELECT MonthCount FROM Q_COUNT WHERE "
   
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblCumulative")
   
    ' Remove old data
    db.Execute ("DELETE FROM tblCumulative")
   
    ' Build data into tblCumulative table
    lMonthCount = 0
    For iYear = YearFrom To Year(Now)
        For iMonth = 1 To 12
            With rst
                Set qry = db.OpenRecordset(strSQL & " year = " & CStr(iYear) & " and month = " & CStr(iMonth))
                If Not (qry.BOF And qry.EOF) Then
                    lMonthCount = lMonthCount + qry![MonthCount]
                End If
                .AddNew
                ![Year] = iYear
                ![Month] = iMonth
                ![CumulativeCount] = lMonthCount
                .Update
            End With
        Next
    Next
    Set rst = Nothing: Set qry = Nothing: Set db = Nothing
           
End Sub


Example: Cumulative_Count(1997)
makes cumulative count from year 1997 to 2000.

Regards,
Paasky



And

Thanks for you help chaps.

Brewdogs worked, but I would like to count cumulatively accross the months as well.

i.e. if there was 5 in Jan and 6 in Feb, you would end up with a result of:

Jan  Feb
5     11

Any clues.

Marcus_c

All right, this should do it:

Public Function CumulativeCounts(intYear As Integer, bytMonth As Byte)

    CumulativeCounts = DCount("SomeField", "YourTable", "DateField < #" & bytMonth + 1 & "/01/" & intYear & "#")

End Function

I just tested it, and it seems to work great . . .

brewdog

Cheers mate. Sorry I've been on holiday

don't apologize for being on holiday. :o) we'd all like to be in your shoes, eh? Glad this is all worked out . . .