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]

SELECT
CDate(Format([dateCancelled],"MMM yyyy")) AS monthYear,
Sum(-1*[meetingCancelled]) AS [Total Cancelled]
FROM
tblMeetings
GROUP BY
CDate(Format([dateCancelled],"MMM yyyy"))
ORDER BY
CDate(Format([dateCancelled],"MMM yyyy"));

 

[THWatson]

MajP
Remember when you helped me with the Perfect Attendance stuff for the local Kiwanis database?

To this point, the parameters were that there were either 4 or 4 Thursdays in a month (meetings always held on Thursdays except when Christmas or New Years falls on a Thursday).

In December 2009 they cancelled the meetings for December 24 and 31, leaving only 3 Thursday meetings. This can happen at the prerogative of the President and/or Board.

This throws the system into a cocked hat, so I have to make provision somehow for a meeting being cancelled.

My thought was to have a table, and form, in which it would be entered if a meeting was cancelled, thus reducing the required # of meetings in order to calculate Perfect Attendance for a month.

I include here the module from which the process works:

Option Compare Database
Option Explicit
Public glblEndDate
Public glblStartDate
Public Function getStreakStart(memID As Long) As Date
  Dim currentMonth As Date
  Dim numMeetings As Integer
  Dim numAttended As Integer
  Dim numMakeUps As Integer
  Dim creditedMeetings
  Dim perfectMonth As Boolean
  Dim dtmEndDate As Variant
  
   On Error GoTo getStreakStart_Error

  dtmEndDate = getEndDate()
  If IsNull(dtmEndDate) Or dtmEndDate = 0 Or Not IsDate(dtmEndDate) Then
    dtmEndDate = DateSerial(Year(Date), Month(Date), 1)
  End If
  
  currentMonth = DateSerial(Year(dtmEndDate), Month(dtmEndDate), 1)
  getStreakStart = currentMonth
  perfectMonth = True
Do Until Not (perfectMonth)
  perfectMonth = False
  numMeetings = getNumberMeetings(currentMonth)
  numAttended = getNumberMeetingsAttended(memID, currentMonth)
  numMakeUps = getNumberMakeUps(memID, currentMonth)
  If numMakeUps > 4 Then
    numMakeUps = 4
  End If
  If numMakeUps + numAttended >= numMeetings Then
    perfectMonth = True
    getStreakStart = currentMonth
    currentMonth = DateSerial(Year(currentMonth), Month(currentMonth) - 1, 1)
  End If
 Loop

   On Error GoTo 0
   Exit Function

getStreakStart_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getStreakStart of Module mdlStreak"
End Function

Public Function getNumberMeetings(dtmDate As Date) As Integer
   On Error GoTo getNumberMeetings_Error

  getNumberMeetings = DLookup("Required", "tblMonths", "MonthYear = " & getSQLDate(dtmDate))

   On Error GoTo 0
   Exit Function

getNumberMeetings_Error:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getNumberMeetings of Module mdlStreak"
 
End Function
Public Function getNumberMeetingsAttended(memID As Long, currentMonth As Date) As Integer
  Dim monthStart As Date
  Dim monthEnd As Date
   On Error GoTo getNumberMeetingsAttended_Error

  monthStart = getFirstOfMonth(currentMonth)
  monthEnd = getEndOfMonth(currentMonth)
  getNumberMeetingsAttended = DCount("MemberID", "qryMeetingsAttended", "MeetingDate >= " & getSQLDate(monthStart) & " AND MeetingDate <= " & getSQLDate(monthEnd) & " AND MemberID = " & memID)
  
   On Error GoTo 0
   Exit Function

getNumberMeetingsAttended_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getNumberMeetingsAttended of Module mdlStreak"
End Function
Public Function getNumberMakeUps(memID As Long, currentMonth As Date) As Integer
  Dim monthStart As Date
  Dim monthEnd As Date
   On Error GoTo getNumberMakeUps_Error

  monthStart = getFirstOfMonth(currentMonth)
  monthEnd = getEndOfMonth(currentMonth)
  getNumberMakeUps = DCount("MemberID", "qryMakeUps", "MeetingDate >= " & getSQLDate(monthStart) & " AND MeetingDate <= " & getSQLDate(monthEnd) & " AND MemberID = " & memID)
  
   On Error GoTo 0
   Exit Function

getNumberMakeUps_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getNumberMakeUps of Module mdlStreak"
End Function

Function getSQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            getSQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            getSQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Public Function getFirstOfMonth(dtmDate As Date) As Date
   On Error GoTo getFirstOfMonth_Error

  getFirstOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

   On Error GoTo 0
   Exit Function

getFirstOfMonth_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getFirstOfMonth of Module mdlStreak"
End Function
Public Function getEndOfMonth(dtmDate As Date) As Date
   On Error GoTo getEndOfMonth_Error

  getEndOfMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)

   On Error GoTo 0
   Exit Function

getEndOfMonth_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getEndOfMonth of Module mdlStreak"
End Function

Public Function getMin(mkups As Variant, mkupsallowed As Variant) As Integer
   On Error GoTo getMin_Error

  If IsNull(mkups) Then mkups = 0
  If mkups > mkupsallowed Then
    getMin = mkupsallowed
  Else
    getMin = mkups
  End If

   On Error GoTo 0
   Exit Function

getMin_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getMin of Module mdlStreak"
End Function

Public Function getEndDate() As Variant
  ' you can pull this off a form
  'getEndDate = forms("yourForm").yourControlname
   On Error GoTo getEndDate_Error

  getEndDate = Forms("frmPerfectAttendance").txtEndDate

   On Error GoTo 0
   Exit Function

getEndDate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getEndDate of Module mdlStreak"
End Function
Public Function getStartDate() As Variant
  ' you can pull this off a form
  'getStartDate = forms("yourForm").yourControlname
  'or return a global variable
   On Error GoTo getStartDate_Error

   getStartDate = CDate(Forms("frmPerfectAttendance").txtStartDate)

   On Error GoTo 0
   Exit Function

getStartDate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getStartDate of Module mdlStreak"
End Function

If you have any further suggestion I'd be really happy to hear it.

Tom

 

[THWatson]

I'm thinking that I can simplify the getting of a count of cancelled meetings per month by:
1. Eliminate the Yes/No field "MeetingCancelled" from the table. There wouldn't be an occasion where this table had data and the MeetingCancelled field was No.
2. Change the SQL to

SELECT CDate(Format([DateCancelled],"MMM yyyy")) AS MonthYear, Count(tblCancelledMeetings.MeetingCancelledID) AS CountOfMeetingCancelledID
FROM tblCancelledMeetings
GROUP BY CDate(Format([DateCancelled],"MMM yyyy"));

Tom

 

[MajP]

Tom, good to see you back. Yes this is all very familiar. And what you say makes sense because you only need the list of canceled meetings.

However. You have a table "required" that lists the number of required meetings in a month. I would think if the month was required to have 4 and one got cancelled then you would simply modify this table to 3 and all should work.

If for some reason you did not want to change the # of required meetings then you could modify the function

Public Function getNumberMeetings(dtmDate As Date) As Integer
On Error GoTo getNumberMeetings_Error
getNumberMeetings = DLookup("Required", "tblMonths", "MonthYear = " & getSQLDate(dtmDate))
On Error GoTo 0
Exit Function
getNumberMeetings_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getNumberMeetings of Module mdlStreak"
End Function

Have the dlookup pull from a query that subtracts the number of cancelled from the required, or do two dlookups and pull the number of cancelled from tblCancelMeetings for that month.

 

[THWatson]

MajP
Thanks for your reply and your suggestions for approach. I appreciate it.

What you have suggested is what I was pondering and you have reinforced it.

I'll look at modifying the table tblMonths that holds the required # of meetings for each month. At first blush that seems like a lot more work. The table is definitively constructed and includes the required # of meetings per month on up to A.D. 2130.
I would have to add 1 columns to the table, one to hold Cancelled (which would the great majority of the time be 0). I guess the Cancelled column would be filled in via an Update Query from the form where Cancelled Meetings are entered.

The other option, as you suggest is to modify the function.

I also have to look at the Perfect Attendance form and see how everything interacts with the code behind that form.

I'll post my results.

Thanks again.

Tom

 

[THWatson]

MajP
As we might have suspected, it's not as simple as changing the Module.

There are those complicated queries that determine various things with respect to Perfect Attendance. So I have to go back through and re-work them so that they pull the correct number of NET required meetings for a month when 1 or more meetings are cancelled.

Murphy took over again!

Tom

 

[THWatson]

MajP
I hope you get this, because, in view of your work on this previously, you will understand my question.

You devised for me the complicated approach to calculate Perfect Attendance. All of that works perfectly well...a little slow running two of the reports but does the job.

My issue is this:
Currently, things are designed to determine, at the end of any given month, the members who have achieved 12 (or more) months Perfect Attendance in a row, and are thus eligible for a reward. The club secretary has wondered about giving out PA awards at the end of each fiscal year...rationalizing that they could then recognize the recipients all at once rather than spread out through the year.
The fiscal year runs from October 1 through to September 30 of the following year.

Thence my question:
At the end of September, could the secretary click one button and have the program check that month and each of the previous 11 months, to see who was due for a PA award?
My concern is that since those 2 reports run fairly slowly now, because they're pawing through a lot of data, that trying to do this looping back would really tax most systems. I'm sure it's likely doable but is it worth it?

By the way, I have had to rejig some things. The club wants the option to hold the "occasional" meeting on a day of the week other than Thursday, and also, at the prerogative of the club president, to cancel a meeting.

Prior to this the PA was calculated by reading from "tblMonths" the # of required Thursdays in each month (as all meetings were on Thursdays, excluding Christmas and New Years). So I have had to change the approach from that of reading from tblMonths to reading from a query that takes into account actual meetings held in a month. This does provide for the needed flexible meeting approach.

As far as the module (see prior post) is concerned, the only change is in the one line

getNumberMeetings = DLookup("Required", "tblMonths", "MonthYear = " & getSQLDate(dtmDate))

which has been changed to

getNumberMeetings = DLookup("Required", "qryRegularMeetingsTotalByMonth", "MonthYear = " & getSQLDate(dtmDate))

Tom

 

[MajP]

I do not have the time this week to look at it, but definitely the answer is yes. The functions are already designed to find streaks given a end date, so you would simply loop the call to the function 12 times passing a different end date.

However, I think to speed this all up I would do something like this. I would maintain a persistent table of streaks then you can run the code to fill the table. Once the table is filled you can answer all the questions from the table and base reports on it. The table would hold each streak and duration

It may take a while to fill the table, but you would only do that once a month or so. After that all reports/forms would be instantaneous
So the table would be
TblStreaks
personID
endMonth
startMonth
streakCount

The code would start from the each persons first meeting and then fill to the current date. So assume Joe started in 1/1/2010 and had perfect attendance to 1/4/2010. Then missed May and was there for June and July. The table would have
ID Strt End StreakCount
Joe 1/2010 1/2010 1
Joe 2/2010 1/2010 2
Joe 3/2010 1/2010 3
Joe 4/2010 1/2010 4
Joe 6/2010 6/2010 1
Joe 7/2010 6/2010 2

I think the code to do this would actually be more efficient than the queries using the functions. This would be able to read through the data sequentially. The queries are flexible, but require looping for each person and are obviously not persistent.
Your thoughts.

 

[THWatson]

Thanks, MajP
I'll have a look, and post back.

Tom

 

[MajP]

can you post an updated DB? Also you may want to delete a lot of the personal information in the personnel table before posting. I would mainly need the Primary key and name. I can not find any of the old copies.

 

[THWatson]

MajP
Here's the link to the Front End and Back End files.

http://www.myotherdrive.com/dyn/ls/901.445707.06042009.04840.6a65dy/Files

Let me know if it works.

Tom

 

[MajP]

At the end of September, could the secretary click one button and have the program check that month and each of the previous 11 months, to see who was due for a PA award

Does this mean anyone who had 12 months streak that falls in the period of 1Oct to 30 Sep gets an award?
So if in November I have a streak of 13 but miss the rest of the year do I get an award? The problem with that logic is the year prior I would get an award for 12 months, but in the current year my streak ends with one more month.

Or are you just saying to return everyone who had perfect attendance for the year? Obviously a lot easier.

I am guessing you mean the first, since you should already by able to do the latter. If you were doing the first, I guess the logic would be after an award is issued then the Streak starts new. That is getting pretty complicated. There is a LastPerfectAttendance field. How is that calculated and tracked? What is its purpose?

 

[THWatson]

MajP
Your questions as all apt. Let me answer one at a time.

Does this mean anyone who had 12 months streak that falls in the period of 1Oct to 30 Sep gets an award?
Yes. And to use your example, if this November a member had a 12 month streak (that would be from December of 2009 through November 2010) he would get an award at the end of the fiscal year October 2010 through September 2011.

Here's the thing: They have to decide how to do their awards. Either do them each month as they come up...or catch them all in a clump at the end of the fiscal year.

For my money, doing them each month is the way to go, but I don't run the club. The Secretary seems to think that doing them all at once might be preferable.

Or are you just saying to return everyone who had perfect attendance for the year? Obviously a lot easier.
No. The Perfect Attendance is not restricted to the 12 months of the fiscal year. You're right, that would be a snap.

There is a LastPerfectAttendance field. How is that calculated and tracked? What is its purpose?
The purpose of the LastPerfectAttendance field would be to plug in a date when a member last received an award. This would clarify when the member was next due.
For example, if you run, from the Perfect Attendance form, the members with 12 consecutive months of PA as of June, 2010, you will see that member Smith has 24 consecutive months PA. If he keeps that up, he could be due for a slew of awards unless you knew when he was last given one.
You're right about the complications of trying to re-start the streak once an award is given, and I don't even want to get into that. What they need to do is use what they have, and plug dates in that LastPerfectAttendance field, and use that as a guide.

Trouble is, until now when we're trying to get this back on a reasonable track, there has been a bit of a problem. I'll explain the picture.

Prior to my constructing this database for the Kiwanis Club, the Attendance was kept on paper. One man, who has been in the club for 54 years, kept these records. He still regards himself as the "official" record keeper, mistrusts computers and programs, and only reluctantly gave the Secretary some attendance data which the Secretary relayed to me to plug into the program. We arbitrarily decided not to press him for data prior to October 2005, deciding rather to build things from there and keep it up.
Maybe you've heard of this kind of guy. Since he was the record keeper, he was the one who signalled when somebody was due to get an award but, for whatever reason, didn't keep track of those awards. Some people always find a way to exercise control, don't they!

Anyway, now it's a building process to get things back on track, and make the Perfect Attendance stuff meaningful again. I can only assist in giving them the tools they will find helpful...from thereon it's up to them how they use things.

- - - - - - - -

I hope these answers help. I could easily say they have all that they need right now. They can calculate their PA at the end of each month...or they can make 12 calculations at the end of the fiscal year, and that wouldn't take long to do either.

The reason I made the post earlier today was due to a question from my friend, the Secretary, as to whether or not, if they want to do the PA calculations at the end of the fiscal year, it could be done with the click of one button.

Tom

 

[MajP]

I am not sure if it answers the question. Do you restart the streak? For the guy who has attendance from Oct 2009 to Nov 2010. At the end of sep 2010 the secretary runs the query and shows 12 months and gives the member and award. In sep 2011 the secretary runs the query again. If they are keeping up on the lastPerfenctattendace field then you could restart the streak, but it sounds like they are not. So in Sep 2011 the member shows 13 month streak ending in nov 2010 and would be given another award. If I hear you correctly, it is just easier to give them another award then try to figure out when the streak restarted.

Like you said, if they decide to give awards at the end of the year you could hard code that logic to restart a streak at the end of every fiscal year. Or they can enter the the award in the table.


My friend just took a job scheduling the Richmond Va tennis tournaments. The person she replaced gave her notebooks for the last 30 years. They did not believe in computers. Which lead to an interesting discussion of how Major League Baseball games are scheduled. Not by some major company, but a single family.

http://www.nytimes.com/1999/04/04/n...eduling-the-high-hard-one.html?pagewanted=all

 

[THWatson]

MajP
What a fascinating article from the New York Times. I remember back, to about 50 years ago, when I was the Secretary of a county baseball league, with 8 teams and I had to devise the year's schedule. I thought that was intricate enough. The wrinkles in the MBL schedule are mind-boggling.

Sorry that I didn't answer the question sufficiently.
Do you restart the streak?
I would say No. Here's why I think that way.
When I run the "Perfect Attendance cycle ending in September 2009" I get 9 members showing up. Sloan has 21, Smith has 15, a couple at 7, one at 6, one at 5, three others at 2.
Excluding Sloan and Smith, the other 7 members might achieve PA sometime in the next 12 months. History suggests not likely.

When I run it for September 2008, I get 6 showing up. Hoffman has 13, Sloan at 9, two at 3, two at 2. Two are Sloan and Smith, each of which continued their streak on to September 2009.

I think that, given the relatively small number of members who actually achieve a PA streak of 12, if the Secretary fills in the "Last Perfect Attendance Award" field you can see at a glance whether or not the member is due for another award now, because the report shows the date of the last award given to the member.

- - - - - - - -

When you say you could hard code that logic to restart a streak at the end of every fiscal year, if you mean that everybody starts a fresh slate at the beginning of a fiscal year...we shouldn't do that.
Because, say a member has some # of months, less than 12, going on a streak at the end of September 2010...he can still achieve a PA award sometime within the next while...but if we wipe him back to zero, he loses out on his opportunity. In other words, he should not be disadvantaged by the fiscal year.
Kiwanis International rules stipulate awards for 12 months PA, irrespective of the fiscal year.

All of which leads me back to my original thought that for my money doing the calculations at the end of each month, giving out the award(s) then is the way to go.
Give out the award, fill in the Last PA field, done, move on.

Have I put us on the same page?

Tom

 

[MajP]

I looked at the db. Some of the code was written to be reusable and flexible, but not efficient. So the excessive use of the functions, make this really slow.

With a couple of additional queries I think I can speed this up a lot. The function that gets the streaks was hardwired to start at the current month and look backwards to find a break in attendance. That identifies the beginning of the members current streak (if any). The current month then signifies the end of the streak. A simple modification of that code to accept a parameter would allow you to look for streaks ending in any month. So getting any streak that ends in any month of the year would be easy. The problem now is to speed that up.

Once you return that information your query would return the Max streak per person for the year, and filtered streaks ending in the current year.

Then if necessary, link this to the lastperfectattendance data. If the difference from the end streak date and the last perfect attendance is greater than 12 they get an award.

Doing all that is pretty simple, but would have to get the queries sped up first.

I will look at this early next week. I have already made some improvements in the code and queries.

 

[THWatson]

MajP
Thanks so much for your continued help and interest. I really appreciate it. Take your time. No rush whatsoever.

I just ran some things by my friend who is the Secretary of the club. I asked to clarify for me again why no "Last PA Award" data is filled in since 1997. I also said: "We have data from October 2005 onwards. Why not just start NOW, from the data we have for the past 5 years, and do the PA awards necessary, plug in the "Last PA Award" field, and move on."

He said the problem is that the "old guy" has steadfastly not relinquished, so far at least, the data from 1997 to 2005. Always has an excuse, or simply ignores the request.
So yes, they can continue on since October 2005, but there's a cumulative factor to the PA stuff. For example, my friend has a 17 year PA pin. He will have some additional years of PA between 1997 and 2005. If we ignore that interval, those years are effectively lost.

SO, what he is going to do is speak with each member by phone prior to September of this year, gather their PA data, and work from that...effectively doing an end run around the old guy.

Thus, very soon things should be up to snuff. Which, of course, makes our efforts worthwhile.

Tom

 

[MajP]

Should not be a big issue. The final query will include the Max Streak per member, within the fiscal year. It will include a calculated field that shows the months from the end of the streak to the last award. So if the last award info is correct/updated you will see if they are due an award or not. if it is not updated then, the secretary can verify prior to giving an award.

 

[THWatson]

Sounds fabulous!

Tom