MS Access Cumulative Date Count

In my Access database I have 800 records which have 140 or so dates.
In totally there are around 102,000 dates. I would like to know how to group by month as a field then cumulative count them to produce a s-curve graph.

i.e. jan-00 5 Feb-00 7 and so on....

The data must be exportable into Crystal Reports.

I can produce a CrossTab Query which creates/Formats a column yyyy-mm and counts the amount in that range but, doesn't count cummulative. Can this be done.

Any help appreciated.

   
Marcus_CAsked:
Who is Participating?
 
brewdogConnect With a Mentor Commented:
Any news here, Marcus_C? I'll submit this as an answer since I think my solution worked. If not, just reject this answer and let me know what else I can do for you. :o)
0
 
RRRCommented:
Hi, Marcus_C.
Try to use DatePart method:

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

Good Luck
RRR.
0
 
paaskyCommented:
Hello Marcus_C,

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

Regards,
Paasky
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Marcus_CAuthor Commented:
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?
0
 
brewdogCommented:
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
0
 
paaskyCommented:
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
0
 
paaskyCommented:
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
0
 
paaskyCommented:
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
0
 
Marcus_CAuthor Commented:
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
0
 
brewdogCommented:
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
0
 
Marcus_CAuthor Commented:
Cheers mate. Sorry I've been on holiday
0
 
brewdogCommented:
don't apologize for being on holiday. :o) we'd all like to be in your shoes, eh? Glad this is all worked out . . .
0
All Courses

From novice to tech pro — start learning today.