Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003 in 2000 format.

    The database keeps track of members and their attendance. Meetings are held weekly, on Thursdays. The member also has an opportunity to attend Makeup Activities which are counted towards perfect attendance.

    A perfect attendance award is granted if the member has perfect attendance in any 12 consecutive months.

    If it's a 4 Thursday month, the member needs 4 attendance credits, if it's a 5 Thursday month, the member needs 5 attendance credits, in order for that particular month to be credited towards perfect attendance.

    The needed 4 or 5 attendance credits in a month will be comprised of the combination of Regular meetings attended plus Makeup Activities attended, but only 4 Makeups in a given month can be counted regardless of how many Makeups a person does.

    As you can see, a member could potentially have 11 straight months of perfect attendance, but miss the 12th month and he/she starts back at 0. This means that every individual member in the club is on a different cycle towards perfect attendance. Also, potentially, every member can be working on a different cycle, as if isn't perfect attendance in a calendar year but any 12 consecutive months counts.

    I am trying to think through 2 things...
    1. A system to calculate attendance for a month, given the maximum of 4 Makeups to be counted, where more than 4 might have been attended.
    2. A system to evaluate attendance and determine how many consecutive months a member has gained the perfect attendance requirement.

    Maybe there's a model out there somewhere for this?

    Additional information...
    Does one makeup activity = one regular meeting?
    In most respects yes. For example, in a 4 Thursday month (regular meetings are always on Thursdays), a member could not attend any regular meetings but go engage in 4 makeup activities, and that would count for perfect attendance. However, in a 5 Thursday month the member would have to attend at least 1 regular meeting, because 5 attendance credits would be required and a max of 4 makeups can be used regardless of how many makeup activities one did.

    Do makeup activities have to be completed before the next weekly meeting?
    No. As long as they are completed within that calendar month they will be counted.

    Can a member attend a makeup activity before a meeting she/he anticipates missing?
    Yes. The club wishes to reward doing things, not punish for not doing.

    Is perfect attendance based on whole months?
    Yes, it is calculated by month. One needs to have 4 credits in a 4 Thursday month, and 5 credits in a 5 Thursday month. However, if Christmas and New Year's fall on Thursdays no credit is needed for those days. That means that you might only need 3 credits in either December or January if that were the case.
    If the member attended every Thursday from 3/19/09 through 3/18/10, that would be perfect attendance.

    Any suggestions would be appreciated. Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    How do you store attendance and makeup activities? In one table, with a field that specifies the activity type, or in two separate tables?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763845' date='06-Mar-2009 12:45']How do you store attendance and makeup activities? In one table, with a field that specifies the activity type, or in two separate tables?[/quote]
    Both are stored in tblAttendance.
    The field "TypeOfMeeting" has 2 possible entries ("Regular Meeting" and "Makeup Activity").
    The field "Makeup Type" has 17 possible entries.

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    And do you want to know which members had perfect attendance according to your criteria over the last 12 calendar months (currently: March 2008 through February 2009), or at any time?

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I can get what I want for a given month with the following query...

    [codebox]SELECT [LastName] & ", " & [PreferredName] AS FullName, MakeupType, Count(MeetingDate) AS CountOfMeetingDate " _
    & "HoursSpent, LastName, PreferredName, First(tblAttendance.MeetingDate) AS FirstOfMeetingDate,
    IIf([CountOfMeetingDate]>=4,4,[CountOfMeetingDate]) AS Show
    FROM tblMembers INNER JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID
    WHERE (((tblAttendance.MeetingDate) Between #11/1/2008# And #11/30/2008#))
    GROUP BY [LastName] & ", " & [PreferredName], MakeupType, HoursSpent, LastName, PreferredName
    HAVING (((tblAttendance.MakeupType) Is Not Null))
    ORDER BY LastName, PreferredName;
    [/codebox]

    That produces this result:
    FullName MakeupType CountOfMeetingDate HoursSpent LastName PreferredName FirstOfMeetingDate Show
    Anderson, Peter Interclub 1 Anderson Peter 11/26/2008 1
    Bisson, Chris Interclub 1 Bisson Chris 11/26/2008 1
    Cabena, Barrie Interclub 1 Cabena Barrie 11/26/2008 1
    Hoffman, Fred Interclub 5 Hoffman Fred 11/26/2008 4

    Note that the Hoffman entry has 5 Makeups in November, 2008 but the "Show" column restricts his Makeup credits for the month to 4.

    However, it seems a bit cumbersome to have to do this for every month of the year.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    OK, but that doesn't answer my question.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763858' date='06-Mar-2009 14:26']OK, but that doesn't answer my question.[/quote]
    Hans
    I'm very sorry, but I didn't receive your question. Just came on now and see it there.

    So to your question: And do you want to know which members had perfect attendance according to your criteria over the last 12 calendar months (currently: March 2008 through February 2009), or at any time?

    One of the anomalies is that perfect attendance is granted for a member having 12 consecutive calendar months of perfect attendance in a row, regardless of when the row begins and ends.
    In other words, one member might get credited for perfect attendance from March 2008 through February 2009. Another might get credited for perfect attendance for September 2008 through August 2009. Etc.

    And perfect attendance is a combination of regular meetings plus engaging in makeup activities in the course of a month, with the maximum # of makeup activities counted being 4 in a calendar month. In a month where there are 4 Thursdays the member needs 4 attendance credits (all 4 of those could be makeup activities). In a month where there are 5 Thursdays the member needs 5 attendance credits but only 4 of those 5 can be makeups.

    The club's fiscal year runs from October of one year through September of the following year. Prior to recently, perfect attendance had to be within the confines of the fiscal year, but they have changed the policy in order to reward attendance not restrict it.

    Tom

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='THWatson' post='763992' date='07-Mar-2009 00:25'][/quote]
    See the attached database; it shows how to do this through a series of queries.

    Notes:

    I created a fixed table tblMonths that lists the number of Thursdays (except for Christmas Day and New Year's Day) for all months from January 2005 to December 2031; I used Excel to create the table and imported it into Access. (I've included the Excel workbook in the zip file).

    The sample attendance table was populated at random; I didn't bother to let meetings actually fall on Thursdays.

    I originally used queries only, but the final one, selecting the members and months in which they complete 12 months of perfect attendance, was extremely slow. So I used an append query to save intermediate results in a table, and this makes execution time acceptable.
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot, Hans. I'll have a look and post back.

    By the way, I received no e-mail indicating you had replied again. Perhaps the only part of that procedure that works is with an initial reply?

    An interesting personage icon you have there!

    Tom

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by THWatson' post='764049 View Post
    An interesting personage icon you have there!
    It's one of the stock images available when you edit your avatar (user picture).

  11. #11
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm not clear how to adapt the data sent to me by Hans, in the database attached to his reply.

    So I am attaching a database with actual tables, tblMembers1 and tblAttendance1

    Tom
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='THWatson' post='764114' date='08-Mar-2009 00:49'][/quote]

    See the attached version. [s]attachment[/s]
    I have removed the members and attendance tables that I created, and renamed yours.
    I have replaced the TypeOfMeeting and Makeup fields with number fields that refer to lookup tables.
    I have modified the queries to work with your tables.

    Since you only included data for October 2008 and later, there will obviously not be any members with 12 subsequent months of perfect attendance.

  13. #13
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764118' date='08-Mar-2009 00:37']Since you only included data for October 2008 and later, there will obviously not be any members with 12 subsequent months of perfect attendance.[/quote]

    Right, Hans. There won't be any at the moment. This is all the data that has been sent to me so far. This is a new database being constructed for a Kiwanis club.

    Thanks. I appreciate your help.

    Tom

  14. #14
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans
    I am trying to get my head around the logic of how to use what you have worked on.
    i.e. When to run what query.

    And the query called "queryPerfectAttendance" won't run at all. It's sql is
    [codebox]SELECT qryCount.MemberID, tblMembersH.LastName AS Expr1, qryCount.TheMonth
    FROM qryCount, tblMembersH
    WHERE (((qryCount.D)=12))
    ORDER BY qryCount.MemberID, qryCount.TheMonth;
    [/codebox]

    I assume that tblMembersH should be tblMembers. So if I change that, the query takes a long time and shows no records.

    Tom

  15. #15
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='THWatson' post='764148' date='08-Mar-2009 11:57']Hans
    I am trying to get my head around the logic of how to use what you have worked on.
    i.e. When to run what query.

    And the query called "queryPerfectAttendance" won't run at all. It's sql is
    [codebox]SELECT qryCount.MemberID, tblMembersH.LastName AS Expr1, qryCount.TheMonth
    FROM qryCount, tblMembersH
    WHERE (((qryCount.D)=12))
    ORDER BY qryCount.MemberID, qryCount.TheMonth;
    [/codebox]

    I assume that tblMembersH should be tblMembers. So if I change that, the query takes a long time and shows no records.

    Tom[/quote]

    For example...
    Would a query be run at the end of each month to see who had perfect attendance that month? Then that be held in a table somewhere and consecutive months accumulate in a total, and then be reset to 0 if a month occurred in which that member did not have perfect attendance?
    If a member achieves 12 months of perfect attendance, does the member's count then revert to 0, and the member begins a cycle again?

    I find the whole perfect attendance, with each member working on a different cycle, a bit of a labyrinth and therefore hard to get my head around the logical programming flow which doesn't seem very straightforward to me. 'Twould be simpler if the perfect attendance requirement coincided with the club's fiscal year, which is October through September.

    Tom

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •