Select Date Range except while on Leave of Absence

[jglabas]

I have a MS Access query that calculates a member’s attendance at team events over the past 12-months (i.e., Now()-365). This is one of several queries used; others calculate the total number of events that occur within the past 12-months. These are used to calculate percentages, etc. They are working fine.

All members have what is termed an ‘Attendance Calculation Date’, the date upon which they joined. This may or may not be >Now()-365.

The problem occurs when a member must take a medical leave of absence, which ‘freezes’ their attendance until they return. Thus as an example:

07/01/07 (AttendCalcDate) . . . . 12/15/07 (LOAStart) . . . 02/21/08 (LOAEnd) . . . Now()

Hence, I would like to select all records outside the LOA period. If a member has not required LOA, the field contains null.

Currently, I select the appropriate records in MS Access 2003 by:

Design View

Field: EventDate
Table: tbl_Join_Member_Event
Total: Where

Criteria: >Now()-365 And >=[tbl_J_MemberDates].[AttendanceCalcDate]

Table tbl_Join_Member_Event is a many-many table between Member_ID and Event_ID

Thanks in advance

[FishVal]

Hello, jglabas.

Could you post the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Fish

[jglabas]

Thank you for responding Fish. I have the requested metadata below.

Let me update the original request. I have a MS Access query that calculates a member’s attendance at team training events over the past 12-months; i.e., DateAdd("yyyy",-1,Date()). This is one of several queries used; others calculate the total number of training events that occur within the past 12-months. These are used to calculate percentages, etc. They are working fine.

All members have what is termed an ‘Attendance Calculation Date’, the date upon when they joined, or were moved to a higher skill level. This may or may not be > DateAdd("yyyy",-1,Date()).

The problem occurs when a member must take a medical leave of absence, which ‘freezes’ their attendance until they return. Thus as an example:

07/01/07 (AttendCalcDate) . . . . 12/15/07 (LOAStart) . . . 02/21/08 (LOAEnd) . . . Date()

Hence, I would like to select all records outside the LOA period. If a member has not required LOA, the field contains null.

The tables involved are as follows:

Table Name=tbl_Join_Member_Dates
Field; Type; IndexInfo
1. MemberDateID; AutoNumber; PK
2. mmMember_ID; Number; FK
3. TraineeMemberDate; Date/Time
4. SupportMemberDate; Date/Time
5. RescueMemberDate; Date/Time
6. AttendanceCalcDate; Date/Time
7. LOAStartDate; Date/Time
8. LOAEndDate; Date/Time

Table Name=tbl_List_Member
Field; Type; IndexInfo
1. Member_ID; AutoNumber; PK
2. NameFirst; Text
3. NameFirst; Text
4. SAR_Status; Text

Table Name=tbl_Join_Member_Event
Field; Type; IndexInfo
1. MemberTeamTrainingID; AutoNumber; PK
2. mmMember_ID; Number; FK
3. mmEvent_ID; Number; FK
4. StartDate; Date/Time
5. AttendCredit; Number

Table Name=tbl_Training_Event
Field; Type; IndexInfo
1. Event_ID; AutoNumber; PK
2. Training_StartDate; Date/Time
3. Training_StartTime; Date/Time
4. Training_EndTime; Date/Time

tbl_Training_Event (1:M) tbl_Join_Member_Event (M:1) tbl_List_Member (1:1) tbl_Join_Member_Dates

The following is my current query in SQL view.

SELECT Count(tbl_Join_Member_TrainingEvent.mmEvent_ID) AS CBM_CountTraining12, tbl_Join_Member_TrainingEvent.mmMember_ID, Sum(tbl_Join_Member_TrainingEvent.AttendCredit) AS CBM_Training12, tbl_Join_Member_Dates.AttendanceCalcDate
FROM (tbl_List_Member INNER JOIN tbl_Join_Member_Dates ON tbl_List_Member.Member_ID = tbl_Join_Member_Dates.mmMember_ID) INNER JOIN tbl_Join_Member_TrainingEvent ON tbl_List_Member.Member_ID = tbl_Join_Member_TrainingEvent.mmMember_ID
WHERE (((tbl_Join_Member_TrainingEvent.StartDate)> DateAdd("yyyy",-1,Date()) And (tbl_Join_Member_TrainingEvent)>=[tbl_Join_Member_Dates].[AttendanceCalcDate]) AND ((tbl_Join_Member_TrainingEvent.AttendCredit)>0))
GROUP BY tbl_Join_Member_TrainingEvent.mmMember_ID, tbl_Join_Member_Dates.AttendanceCalcDate;


Thanks in advance for any assistance.

[FishVal]

Hello, jglabas.

Thanks for a comprehensive post.
I would like to ask you two questions concerning table structure.

• What does [tbl_Join_Member_Event].[StartDate] field state for ?
• Why do you store so many different type of dates in each record of [tbl_Join_Member_Dates] table ?

[n8kindt]

this really isn't what u were asking for but i just thought i'd break off this little piece of unpolished code i wrote for a similar situation. it isn't catered to your situation but it might get your ideas a-rollin.

If ReportMonth <= ManStatE And ReportMonth >= ManStatS Then
    If LastPurchaseDate > ManualStatusStart Then
    BaseDate = LastPurchaseDate
    'if the consultant purchased during the frozen period, the purchase date is used as a base
    'if not the frozen period continues
    Else: BaseDate = MSMonth & "/1/" & MSYear
    End If
 
'ManStatE stands for the month in which the Manual Status Ends, ManStatS is for the month in which the Manual Status Starts... ManualStatusStart stands for the actual date. MSMonth and MSYear are report month and year

i just wrote code to include a phony date if the report occurred while the customer's account was frozen. and the frozen period stopped once the frozen period was over, or they purchased again.

the reason for the basedate is we issue monthly reports that determine a certain status based on their last sale. the base date would be the equivalent of a sale during the report month--or attendance in your case? once again, not sure if this applies to your actual question but it might be a valid alternative

[jglabas]

FishVal

Question #1. [tbl_Join_Member_Event].[StartDate]
This is the same as
[tbl_Training_Event].[Training_StartDate] and is unnecessary. Thanks for pointing that out.

Question #2. There is no short answer
This DB is for tracking training and participation on a Search and Rescue Team. There are multiple organizations that require reporting of activities, inlcuding our own team. Using a spreadsheet and other paper records was becoming impossible. The activities are:

Attendance at County required training
Attendance at meetings,
Attendance at monthly Team Trainings
Attendance at Search/Rescue missions
Attendance at extra training activities
Participation in public service

If I can solve the issue of LOA for training, I can replicate the solution to the other activities. LOA Freezes the calendar for all activities

Why do I need to record all the dates:
A Trainee member must attend 50% of all meetings and trainings
A Support and Rescue member must attend 75% of all meetings and trainings.
A trainee must meet all county requirements within 12 months.
Support or Rescue members, may take a LOA for medical or family issues. A Trainee must reapply.

Once a person becomes a trainee, they have 12-months to complete 6 training classes required by the county (this training is different from team training). They go on the clock for the 50% attendance with the team training and meetings (tracked separately from the county training courses). Their trainee date becomes the AttendanceCalcDate. Once the trainee has completed the county training, the AttendanceCalcDate is reset and they must now attend 75%. However, two of the county classes must be repeated every two years, so the dates for each course must be tracked (not shown on the table extract). And so on. There are many dates for each level of membership.



n8kindt

Let me digest what you have written. At this point, I am stumped on how to incorporate the LOA period into my existing queries.

[n8kindt]

FishVal

Question #1. [tbl_Join_Member_Event].[StartDate]
This is the same as
[tbl_Training_Event].[Training_StartDate] and is unnecessary. Thanks for pointing that out.

Question #2. There is no short answer
This DB is for tracking training and participation on a Search and Rescue Team. There are multiple organizations that require reporting of activities, inlcuding our own team. Using a spreadsheet and other paper records was becoming impossible. The activities are:

Attendance at County required training
Attendance at meetings,
Attendance at monthly Team Trainings
Attendance at Search/Rescue missions
Attendance at extra training activities
Participation in public service

If I can solve the issue of LOA for training, I can replicate the solution to the other activities. LOA Freezes the calendar for all activities

Why do I need to record all the dates:
A Trainee member must attend 50% of all meetings and trainings
A Support and Rescue member must attend 75% of all meetings and trainings.
A trainee must meet all county requirements within 12 months.
Support or Rescue members, may take a LOA for medical or family issues. A Trainee must reapply.

Once a person becomes a trainee, they have 12-months to complete 6 training classes required by the county (this training is different from team training). They go on the clock for the 50% attendance with the team training and meetings (tracked separately from the county training courses). Their trainee date becomes the AttendanceCalcDate. Once the trainee has completed the county training, the AttendanceCalcDate is reset and they must now attend 75%. However, two of the county classes must be repeated every two years, so the dates for each course must be tracked (not shown on the table extract). And so on. There are many dates for each level of membership.



n8kindt

Let me digest what you have written. At this point, I am stumped on how to incorporate the LOA period into my existing queries.

after that explanation, i am nearly convinced that you are going to have to create a module with similar coding. i don't think there is any way to do this with just queries--there might be, but IMHO it will require more work to find out than writing code would require. i was literally in your shoes about 2 months ago except i had the misfortune of not knowing a thing about vba. i had just "completed" (or so i thought) making a program, based nearly entirely on queries, that calculated our commissions. unfortunately i was unaware of a multitude of exceptions that needed to be allowed for. i hope you're not in for any more big surprises like i was.

[FishVal]

Hello, jglabas.

Sorry for late response.
I have a little doubt as for the tables structure and relationships.
I hope you will clarify your strategy of data storage.

What you want is validation of [tbl_Join_Member_Event] table data. If an event date falls into a member LOA period, then record in [tbl_Join_Member_Event] table relating the event to the member is considered to be invalid. My questions are the following:

• Does this mean the [tbl_Join_Member_Event] table record is not relevant at all and should be deleted and/or rejected by input validation logic?
• Does this mean the record is generally allowed - e.g. record existence means assignment rather than attendance?
• Do you expect member to have multiple LOA periods?

Regards,
Fish

[jglabas]

Hello Fish

I hope this answered your questions.

What you want is validation of [tbl_Join_Member_Event] table data. If an event date falls into a member LOA period, then record in [tbl_Join_Member_Event] table relating the event to the member is considered to be invalid.

***Yes, invalid for purposes of calculating the member attendance, but not invalid for knowing the member was at the event – one example would be that the member on LOA for a family illness is willing to come half a day to help out as a trainer.

My questions are the following:
• Does this mean the [tbl_Join_Member_Event] table record is not relevant at all and should be deleted and/or rejected by input validation logic?
***No. There are exceptions to attendance in the LOA policy. A person may take LOA for family issues that do not impact his physical ability to attend training or team meetings periodically. He is encouraged to attend ‘without credit’, in order to maintain skills. Similarly, a person could have a sprained ankle but can attend a classroom session and team meetings. So we need to record his presence (but, by policy, he does not receive credit while on LOA or Medical LOA).

• Does this mean the record is generally allowed - e.g. record existence means assignment rather than attendance?
***No, record existence always means attendance. I have a main form that lists the event, and a subform in dataset view that lists everyone that attended.

• Do you expect member to have multiple LOA periods?
***Historically and currently, this cannot occur within the same 12 month period. A member may take up to one year of LOA and may not take a second LOA in two consecutive years. But, a future bylaw change could conceivably change. Were you suggesting a LOA_table rather than a single occurrence field?

Thank you

[jglabas]

Hi n8kindt

Hopefully there is a solution in queries. I have dozens of queries that need to be altered to account for LOA. Adding something to the criteria line in a query would beat having to convert everything to vba. My skill level: Access - beginner; vba - at least one step below beginner. Simple subs I can do, but I would need a bit more time under my belt before taking on a revision to vba at this point.

It is a struggle to produce something accurate that the team can use, without having to go back to the nightmare of paper records. Maybe I need to just dive in and do it but the trade off, in terms of the investment of time needed to produce something useful in vba vs queries is not good right now. As I learn more, I frequently realize what I did not know when I started this project, and I realize now that I don't even know what knowledge I lack in many areas. I am waiting for a member to join who is a dba. :-)

Thanks

[FishVal]

Ok.

That actually could be easily performed with SQL queries.
In your current tables structure even easier, though I suggest you to create a "LOA_table". Maybe the same or something else should be done with the rest fields in [tbl_Join_Member_Dates] table.

Data organized this way could be analyzed in the following way:

• A separate query should be created to join member/event tables for further outer join with "LOA_table".
  qryMembersEvents
  

  SELECT tblMembersEvents.keyMemberEventID, tblMembersEvents.keyEventID, tblEvents.dteDate, tblMembersEvents.keyMemberID, tblMembers.txtMemberName
  FROM (tblMembersEvents INNER JOIN tblEvents ON tblMembersEvents.keyEventID = tblEvents.keyEventID) INNER JOIN tblMembers ON tblMembersEvents.keyMemberID = tblMembers.keyMemberID;
   

• Dataset obtained in previous step is outer joined with "LOA_table" where member event day falls into any LOA period to obtain those not matching.
  

   qryMembersEventsOutOfLOA
  SELECT qryMembersEvents.keyEventID, qryMembersEvents.keyMemberID, qryMembersEvents.txtMemberName, [tblMemberLOAs].[keyMemberLOAID]
  FROM qryMembersEvents LEFT JOIN tblMemberLOAs ON ([tblMemberLOAs].dteEnd>=qryMembersEvents.dteDate) AND ([tblMemberLOAs].dteStart<=qryMembersEvents.dteDate) AND (qryMembersEvents.keyMemberID=[tblMemberLOAs].keyMemberID)
  WHERE IsNull([tblMemberLOAs].[keyMemberLOAID]);
   

• And finally usual aggregate query.
  qryFinalCount
  

  SELECT qryMembersEventsOutOfLOA.keyMemberID, qryMembersEventsOutOfLOA.txtMemberName, Count(qryMembersEventsOutOfLOA.keyEventID) AS CountOfkeyEventID
  FROM qryMembersEventsOutOfLOA
  GROUP BY qryMembersEventsOutOfLOA.keyMemberID, qryMembersEventsOutOfLOA.txtMemberName;
   

Naming is somewhat different but, I guess, quite recognizable.

Regards,
Fish

[jglabas]

Fish

Thanks for putting all that effort into this. I will be back on this particular job this weekend and will get back to you with my results. I appreciate the assistance.

John