Sum of controls on form 1

[THWatson]

I have a tblCancelledMeetings with the following makeup:
MeetingCancelledID ... AutoNumber
DateCancelled ... Date/Time
MeetingCancelled ... Yes/No

What I want to do is get a Sum by Month of the meetings cancelled during each month. The great majority of months will not have any cancelled meetings.

Thanks.

Tom

 

[MajP]

http://www.4shared.com/document/IYd7foAC/MajP_13Jul_Guelph_Kiwanis.html

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.

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.

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.

 

[THWatson]

MajP
I just picked this up. I'll have a run through it and post back.

Thanks.

Tom

 

[MajP]

OK. I will be off the net for a few weeks so take a look at the code and basic strategy. Tried to make it flexible enough so that you can use it a couple of different ways.
As a data analyst, one thing of interest is

Month  CountOfMeeting
1      42
2      63
3      48
4      40
5      69
6      27
7      8
8      28
9      38
10     43
11     60
12     22

This is the count of perfect attendance over the years by month. Looks like everyone is on vacation in July which makes keeping the streak going difficult. I assume that November has a holiday parties and one less thursday meeting for Thanksgiving so everyone makes it. December gets tough with the holidays. Must have some good May festivities because that it the hot month. They may want to consider some summer policy for increasing attendance.

 

[THWatson]

MajP
I have run into a bit of a glitch, and still haven't quite figured it out.

I was trying to figure out why Member Smith, MemberID 38, isn't showing with enough PA credit months. Missing are May 2009 and July 2009, each of which he should have PA credit.

So I will show the results of the new "pop" queries, restricting the input to MemberID 38.

popQryAttendedMakeupRequired
May and July 2009 are missing

popqryCountMakeUpsByMonth
May and July 2009 are missing

popqryCountMeetingsAttendedByMonth
This shows the required # of meetings for May is 4, and the required # of months for July is 5

popqryCountRegularMeetingsByMonth
This shows the required count of meetings for May is 4, and the required count of meetings for July is 5

Since he has met the requirements he should get credited for PA for both May and July of 2009.

However when I run popQryPerfectMonths he does not get credit for either of those months.

Something is off in the first two. I'll have to look closely at them and see what's going on.

- - - - - - - -

As for your suggestions for vacations in July and holiday parties in November...vacations here typically take place in either July or August. As for November, this is Canada, so we celebrate Thanksgiving on the second Monday of October.
The month of May...well, I don't have a way of accounting for that being a hot month.

- - - - - - - -

Looks as if you are on holidays for a few weeks. Have a wonderful vacation!!!

Tom

 

[MajP]

Thanks. My logic was correct, but the execution was sloppy.

My popQryAttendedMakeupRequired needed left joins to include all the records and I had inner joins.

The other thing was that I had built this query to answer all my questions about attended, required, makeups, and total credited meetings, but I never actually used it. So I modified the getStreakStart2 to just read the query for a month with perfect attendance instead of recalculating within the code. This further sped the code up.

Here is the update.

http://www.4shared.com/document/H40xvWqa/MajP_14Jul_Guelph_Kiwanis.html

The data looks far more correct. Takes about 6 seconds to run 2 years of streak data.

 

[THWatson]

MajP
Yep, the data is starting to look right.

As for the "popQryAttendedMakeupRequired" query's need for left joins, I had worked on that but kept getting an "ambiguous join" error message. I was still working on it when you posted back. Maybe I only did one of the 3 inner joins, instead of all 3.

I'll look through the changes and run a few more checks.

Thanks again.

Tom

 

[THWatson]

MajP
You will be off the net when I send this, but I wanted to send an update with respect to my current experimentation with the new codes.

I have been using the testPrintStreaks and plugging in different values in the Call PrintStreaks(#1/1/2009#, 11) line of the code.

Using (#1/1/2009#, 11) takes 3.94 seconds and yields 45 results in the table "tblStreakData"

Using (#1/1/2005#, 11) takes 9.89 seconds and yields 56 results.

Using (#1/1/2005#, 6) takes 10.78 seconds and yields 133 results.

Using (#10/1/2005#, 6) takes 9.83 seconds and yields the same 133 results. As you know, 10/1/2005 is where the database data begins.

- - - - - -

So, the time required to update the table is excellent. How many months to use as the streakLevel is a judgement call. I suppose one could argue that you don't need to know anything more than the members with Perfect streaks...however, it might be nice to know those who came close.

As the database grows, the time required to update the table will, I suspect, grow a little. On the other hand, although it doesn't show in the database with which we are currently working, I did set up an Archive process which would archive Attendance data older than 5 years. This Archive process would be run a month or so following the end of a fiscal year. If this happens, the time to update that streak table would never be critical.

So, now that I can see how all this works, I can figure out the modifications to the processes on the PA form, which should now run almost instantaneously.

Tom

 

[THWatson]

MajP
No, actually, the Archive process is in the current database. The command button for it is greyed out on the Main Menu as it only shows in November and December. It is, as I suggested, designed to archive data older than 5 years.

Tom

 

[MajP]

I would have a form that the user goes to before pulling the Streak Data Reports. The form would have a combo with values 2 - 12 and defaulted to 12. There would be a text field with a calendar control defaulted to the prior October. So you can tell the user before getting streak data reports they need to update the streak data table (If they have not added additional data since the last time they updated the report, you could even allow them to bypass the update and go directly to the reports). They run the update and then the streak report menu pops up. You could have lots of different reports highlighting streak data.

So if they run it at 12 they can answer questions about streaks >= 12 and going back to the beginning of the fiscal year.

If they want to know information about streaks >=6 and going back a couple of years they can do that. They may want to run a report at 6 so they can recognize those half way to an award. This may be an incentive if the members know they only have 1 or 2 months left.

The trick is to design the interface so that you only have to run the code to get updated, but not every time you open a report if the data is already updated.

 

[THWatson]

MajP
I was already thinking along those lines of possibility, and you have fortified my thoughts.

It could be useful to know, at the end of a fiscal year, that members Abnerworthy and Shufflemire need only 1 or 2 more months to get a PA award, and this could be brought to their attention as an incentive.

It's a matter of how many months is useful.

I have just sent a note to my friend, the club secretary, outlining the possibilities, and asked for some input from him.

This is looking really good!!!

- - - - - - - -

Once again, here's wishing you a great vacation. We have a "gathering of the clan" this Sunday for the celebration of Janice's (my wife) and my 50th wedding anniversary. Got kids and grandkids coming out of my ears at the moment, as they started to move in on us already.

Tom

 

[MajP]

FYI. All of this sequence calculation could be done in a pure SQL solution without code. I tried this and found that the queries I could come up with were slower than code. Someone good at sql may be able to find a working solution. This little demo of queries shows you how you would do it.

http://www.4shared.com/document/6DC0mBhU/DateSequence.html

Look at the queries in this order:
queryInSequence: returns the records that are part of a sequence (if the member has another record one month prior to the current record then the record is within the sequence)

qryStartSequence and qryEndSequence use the queryInSequence to determine if a date is at the end of a sequence or the start of a sequence.

qryBeginEndSequence then puts the above 2 queries together so you have a record for each sequence with its beginning and end

qrySequenceLength just adds a calculated field.

This concept should be able to be done on your database, but I could not find an efficient way because you have to do all the preprocessing of makeups, regular meetings, max allowable makeups, required meetings, and then determine if it is a perfect month.

 

[THWatson]

MajP
Thanks. I'll have a look.

Tom

 

[THWatson]

MajP
I did some work on the database - converted things to the "pop" queries you made, and set things up to use the "tblStreakData". I have posted a new Front End and Back End at the following link.

http://www.4shared.com/dir/nM1O4Nm-/sharing.html

The main change is in "frmPerfectAttendance."

I was thinking about what you said, "The trick is to design the interface so that you only have to run the code to get updated, but not every time you open a report if the data is already updated."

How I decided to handle it was this...
When the user wants to run any of the 3 Perfect Attendance reports (current fiscal year, previous fiscal year, or between any 2 dates), the following code kicks in, compares the last date in tblAttendance with the last date in tblStreakData, and either signals an update of tblStreakData or not.

If Me.txtMax > DMax("streakEndDate", "tblStreakData") Then

 Dim startTime As Double
  Dim endTime As Double

    Call MsgBox("The Consecutive Months table needs updating." _
    & vbCrLf & "           This takes 10 seconds or less." _
     , vbExclamation, "Updating Consecutive Months table")
                                                           
  startTime = Timer
  Call delStreakData
  'clear out the data
  'Call PrintStreaks(#1/1/2005#, i)
  Call PrintStreaks(DMin("MeetingDate", "tblAttendance"), 6)
  endTime = Timer
  MsgBox "Update completed. Time to update = " & endTime - startTime
  'DoCmd.OpenQuery "qryStreakData"
Exit Sub
End If

This seemed to me to be the least intrusive method. Of course, this doesn't work if the user happened to have entered data prior to the current date (e.g. entering a meeting for last month that was missed, or something like that) so I also put in a command button to allow the user to update in that circumstance.

Whereas the Perfect Attendance reports were quite slow to run before, they're almost instantaneous now, with doing things from the table.

I also commented out the line in the code which called for the opening of a streak data query, as this isn't needed once things work properly.

I will still have to do some cleanup of the database but seems to me things are working pretty well.

Take care. Hope you have enjoyed a good bit of time off.

Tom

 

[THWatson]

MajP
By the way, two points of explanation...

1. The reason I changed the line in the code

Call PrintStreaks(#1/1/2005#, i)

to

Call PrintStreaks(DMin("MeetingDate", "tblAttendance"), 6)

was in preparation for archiving. If Attendance data prior to 5 years is archived, then using 1/1/2005 will no longer work after October of 2010.

2. The reason I made the number of months 6 was that I can't see the user wanting to know less than 6 consecutive months of Perfect Attendance.

Tom

 

[THWatson]

MajP
It appears you have returned from your holidays. Hope you had some wonderful times, and a good break.

I have done a bit more work on the database, especially with a form that will work on Archived data once archiving takes place (that will be this coming November).

I sent an updated Front and Back end link on July 30.

http://www.4shared.com/dir/nM1O4Nm-/sharing.html

Let me know if you would like me to send another updated Front and Back End which includes this work on the archived stuff.

Tom