Take a look at the demo
1) I added a few new queries they all begin with "pop" to differentiate from existing. Some are mods to existing queries to speed things up.
2) I found that some very simple queries, with built in functions run very slow. Several queries use this function to convert the meeting date to the first of the month. This allows you to group and count the meetings for the year and month. The function is, "getFirstOfMonth". I do not know why, this is very simple but causes the queries to run real slow. However, if in the queries you calculate "MeetingYear:Year(meetingDate)" and "MeetingMonth:Month(meetingDate)" then group by year and month the queries are much faster. You can see the difference by running
popQryCountMeetingsAttendedByMonth
vs
qryCountMeetingsAttended
The query appears to run about 5 times faster. The streak calculation calls this query for each person. I assumed that this would be enough to fix the speed issue. Did the same with the query for Makeups by month. I then used the new queries in the streak function by adding new functions "getNumberMeetingsAttended2" and "getNumberMakeups2". These new functions are now much faster and recommend you replace the queries and functions with these new ones
3) However, the streak function that uses these new queries and functions was still was painfully slow. It locks my computer up. The reasons are several. Mainly if you use functions within queries to calculate a streak, you are opening and closing several recordsets (directly or indirectly) for each member. So my thought was to open a single recordset, read the streaks, and populate a table with the answers. This is orders of magnitude faster. With this approach you can actually populate a table for each month since the first meeting (10/2005) to today, each persons streak per month. With that data in a table, you can run queries and answer pretty much any streak information.
4) Look at the query "popQryAttendedMakeupRequired", this is the basis for the calculations. It is based on several queries. If I did this correct it shows for each person the number of meetings attended, the number of makeups, the number required. The query "popQryPerfectMonths", uses this query to show the perfectAttendance. These queries are near instantaneous and have about 90% of the solution
5) So the following code reads "popQryPerfectMonths" and then populates a table with streak data.
Code:
Public Sub PrintStreaks(Optional startPeriod As Date = #1/1/2005#)
Dim rs As DAO.Recordset
Dim rsNames As DAO.Recordset
Dim startStreak As Date
Dim lenStreak As Integer
Dim streakLevel As Integer
Dim memID As Long
Dim I As Integer
Dim endPeriod As Date
Dim endDate As Date
endPeriod = Now()
streakLevel = 6
Set rs = CurrentDb.OpenRecordset("popQryPerfectMonths", dbReadOnly)
Set rsNames = CurrentDb.OpenRecordset("Select MemberID from tblMembers")
Do Until startPeriod >= endPeriod
endDate = getEndOfMonth(DateSerial(Year(startPeriod), Month(startPeriod), 1))
'This shows that it cycles through each month from the start period to now
'Debug.Print endDate
Do While Not rsNames.EOF
memID = rsNames!MemberID
'This shows it cycles each member
'Debug.Print memID
startStreak = getStreakStart2(rs, memID, endDate)
'This shows the streaks for each user ending at the enddate
' Debug.Print startStreak
lenStreak = DateDiff("M", startStreak, endDate)
'this prints the length of a streak for a given end date
'If lenStreak > 0 Then Debug.Print lenStreak
If lenStreak >= streakLevel Then
'Debug.Print memID & " " & startStreak & " " & endDate & " " & lenStreak
Call insertStreakData(memID, startStreak, endDate, lenStreak)
End If
rsNames.MoveNext
Loop
rsNames.MoveFirst
startPeriod = DateAdd("M", 1, startPeriod)
Loop
rsNames.Close
rs.Close
End Sub
The startPeriod parameter is the point in time you want to start seeing streak data. It provides results from that period to today showing each persons streak for a given month.
Code:
Public Sub testPrintStreaks()
Dim startTime As Double
Dim endTime As Double
MsgBox "This updates the streak table. It takes some time."
startTime = Timer
Call delStreakData
'clear out the data
Call PrintStreaks(#1/1/2008#)
endTime = Timer
MsgBox "Update completed. Time to update = " & endTime - startTime
End Sub
Take a look at the queries and then run testPrintStreaks. If the results are correct then, you can use the results table to run queries. You should be able to answer any questions about streaks from the data table.
You would just have to tie things together so that you run the code before running queries on the table.
