选择日期范围，但在休假时除外 [英] Select Date Range except while on Leave of Absence
问题描述
我有一个MS Access查询，用于计算会员在过去12个月内参加团队活动的情况（即Now（） - 365）。这是使用的几个查询之一;其他人计算过去12个月内发生的事件总数。这些用于计算百分比等。它们工作正常。
所有成员都有所谓的出勤计算日期？，即他们加入的日期。这可能是也可能不是>现在（） - 365.
当一名成员必须请病假时会出现问题，这会冻结吗？他们的出勤直到他们回来。因此，作为一个例子：
07/01/07（AttendCalcDate）。 。 。 。 12/15/07（LOAStart）。 。 。 02/21/08（LOAEnd）。 。 。现在（）
因此，我想选择LOA期间以外的所有记录。如果成员没有要求LOA，则该字段包含null。
目前，我在MS Access 2003中选择适当的记录：
设计视图
字段：EventDate
表：tbl_Join_Member_Event
总计：哪里
标准：> Now（） - 365和> = [tbl_J_MemberDates]。[AttendanceCalcDate]
表tbl_Join_Member_Event是Member_ID和Event_ID之间的多个表
提前谢谢
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
推荐答案
你好，jglabas。
你能为所有人发布MetaData吗？相关数据集。以下是如何发布表MetaData的示例：
表名= tblStudent
Hello, jglabas.
Could you post the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent
感谢您回复Fish。我在下面提供了所需的元数据。
让我更新原始请求。我有一个MS Access查询，用于计算会员在过去12个月内参加团队培训活动的情况;即，DateAdd（yyyy， - 1，Date（））。这是使用的几个查询之一;其他人计算过去12个月内发生的培训事件总数。这些用于计算百分比等。它们工作正常。
所有成员都有所谓的出勤计算日期？，他们加入的日期，或者被移到更高的技能水平。这可能是也可能不是> DateAdd（" yyyy"， - 1，Date（））。
如果会员必须请病假，这会产生问题吗？冻结？他们的出勤直到他们回来。因此，作为一个例子：
07/01/07（AttendCalcDate）。 。 。 。 12/15/07（LOAStart）。 。 。 02/21/08（LOAEnd）。 。 。日期（）
因此，我想选择LOA期以外的所有记录。如果成员没有要求LOA，则该字段包含null。
所涉及的表格如下：
表名= tbl_Join_Member_Dates
Field;类型; IndexInfo
1. MemberDateID;自动编号; PK
2. mmMember_ID;数; FK
3. TraineeMemberDate;日期/时间
4.支持会员日期;日期/时间
5. RescueMemberDate;日期/时间
6.出勤率日期;日期/时间
7. LOAStartDate;日期/时间
8. LOAEndDate;日期/时间
表名= tbl_List_Member
字段;类型; IndexInfo
1. Member_ID;自动编号; PK
2. NameFirst;文字
3. NameFirst;文字
4. SAR_Status;文字
表名= tbl_Join_Member_Event
字段;类型; IndexInfo
1. MemberTeamTrainingID;自动编号; PK
2. mmMember_ID;数; FK
3. mmEvent_ID;数; FK
4. StartDate;日期/时间
5. AttendCredit;数字
表名= tbl_Training_Event
字段;类型; IndexInfo
1. Event_ID;自动编号; PK
2. Training_StartDate;日期/时间
3. Training_StartTime;日期/时间
4. Training_EndTime;日期/时间
tbl_Training_Event（1：M）tbl_Join_Member_Event（M：1）tbl_List_Member（1：1）tbl_Join_Member_Dates
以下是我的SQL视图中的当前查询。
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
。从（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
，其中（（（tbl_Join_Member_TrainingEvent.StartDate）GT;使用DateAdd（QUOT; yyyy"， - 1，Date（））和（tbl_Join_Member_TrainingEvent）> = [tbl_Join_Member_Dates]。[AttendanceCalcDate]）AND（（tbl_Join_Member_TrainingEvent.AttendCredit）> 0））
GROUP BY tbl_Join_Member_TrainingEvent.mmMember_ID ，tb l_Join_Member_Dates.AttendanceCalcDate;
提前感谢您的帮助。
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.
您好，jglabas。
感谢您提供全面的帖子。
我想问你关于表结构的两个问题。
- [tbl_Join_Member_Event]。[StartDate]字段状态是什么？
- 为什么在[tbl_Join_Member_Dates]表的每个记录中存储这么多不同类型的日期？
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 ?
这篇关于选择日期范围，但在休假时除外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
