查询截至指定日期的记录 [英] Query To Find Records As Of Specified Date
问题描述
我正在建立一个数据库，该项目能够逐个项目跟踪一般
承包商雇用的员工。这些员工被雇用在一个项目上工作，然后在工作结束时被解雇，或者在项目结束时或者在项目结束时被解雇。任何员工都可能被雇用，并且在一段时间内多次重新雇用并重新雇用。我有的表格是
以及我问题的关键字段是：
TblEmployee
员工ID
FirstName
LastName
TblEmployment
EmploymentID
EmployeeID
DateHired
DateLaidOff
任何员工在TblEmployment都可能有多个记录。在每个月结束时，需要一份报告，显示当月最后一天工资单上的所有员工。我需要帮助创建该报告的查询。
用户必须能够指定报告的月份和年份;即，不仅仅是当前或上个月的b $ b，而是过去的任何年份和月份。查询
必须考虑到用户可能在之前的
年中指定了一个月，并且员工可能已被雇用并多次下岗
那个月之后
。
感谢您的帮助！
Sue
I''m working on a database that keeps track of employees hired by a general
contractor on a project by project basis. These employees are hired to work
on a project and are then laid off either at the end of the work they were
hired to do or at the end of the project. Any employee may be hired, laid
off and rehired several times during the course of time. The tables I have
and the critical fields to my question are:
TblEmployee
EmployeeID
FirstName
LastName
TblEmployment
EmploymentID
EmployeeID
DateHired
DateLaidOff
Any employee may have several records in TblEmployment. At the end of each
month a report is needed showing all the employees on the payroll on the
last day of the month. I need help creating the query for that report. The
user must be able to specify the month and year for the report; ie, not just
the current or previous month but any year and month in the past. The query
must take into account that the user might specify a month in the previous
year and that an employee might have been hired and laid off several times
after that month.
Thanks for all help!
Sue
推荐答案
> TblEmployee
> TblEmployee
EmployeeID
FirstName
LastName
TblEmployment
EmploymentID
员工ID
DateHired
DateLaidOff
任何员工在TblEmployment都可能有几个记录。在每个月结束时，需要一份报告，显示该月最后一天工资单上的所有员工。我需要帮助创建该报告的查询。
用户必须能够指定报告的月份和年份;即，不仅是当前或上个月的
，而是过去的任何年份和月份。
查询必须考虑到用户可能指定上一个月的一个月，并且该员工可能在该月之后被雇用并多次下岗。
EmployeeID
FirstName
LastName
TblEmployment
EmploymentID
EmployeeID
DateHired
DateLaidOff
Any employee may have several records in TblEmployment. At the end of each
month a report is needed showing all the employees on the payroll on the
last day of the month. I need help creating the query for that report. The
user must be able to specify the month and year for the report; ie, not just the current or previous month but any year and month in the past. The query must take into account that the user might specify a month in the previous
year and that an employee might have been hired and laid off several times
after that month.
我假设您有一个表格，其中日期（例如10/31/2004 =userDate）将由用户输入以便运行每份报告。如果是这样，听起来像你需要选择来自tblEmployment的不同记录，其中DateHired是>
DateLaidOff和DateHired是< userDate，然后加入到
EmployeeID上的tblEmployee。这有意义吗？
I assume you have a form where the date (e.g. 10/31/2004 = "userDate") will
be entered by the user for running each report. If so, it sounds like you
need to select distinct records from tblEmployment where DateHired is >
DateLaidOff and DateHired is < userDate, and then join to tblEmployee on
EmployeeID. Does this make sense?
1。使用两个表创建查询。
2.在字段行中，输入：
[月末]
3.在此字段下的Criteria行中，输入：
[tblEmployment]。[DateHired]和
[tblEmployment]。[DateLaidOff]
4.在查询菜单上选择参数。在对话框中输入：
[月末]日期/时间
运行查询时，用户必须输入日期，例如7 / 31/2004。
查询仅返回截至该日期的人员。
除非此人有两个重叠，否则不应该重复。
就业记录。
-
Allen Browne - 微软MVP。西澳大利亚州珀斯。
访问用户提示 - http：// allenbrowne.com/tips.html
回复群组，而不是mvps dot org的allenbrowne。
" Sue" < SM ***** @ notmyemail.com>在消息中写道
新闻：i3 ******************* @ newsread2.news.atl.eart hlink.net ...
1. Create a query using both tables.
2. In the Field row, enter:
[Last Day Of Month]
3. In the Criteria row under this field, enter:
Between [tblEmployment].[DateHired] And
[tblEmployment].[DateLaidOff]
4. Choose Parameters on the Query menu. In the dialog enter:
[Last Day Of Month] Date/Time
When you run the query, the user must enter a date such as 7/31/2004. The
query returns only those who were hired as of that date.
You should not get duplicates unless the person has two overlapping
employment records.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Sue" <sm*****@notmyemail.com> wrote in message
news:i3*******************@newsread2.news.atl.eart hlink.net...
我正在开发一个数据库，用于跟踪一般项目承包商在项目基础上雇用的员工。这些员工受雇于项目工作，然后在他们被雇用的工作结束时或在项目结束时被解雇。任何员工都可能会在一段时间内被雇用，休息和重新雇用几次。我有的表格以及我的问题的关键领域是：
TblEmployee
员工ID
FirstName
LastName
TblEmployment
EmploymentID
员工ID
DateHired
DateLaidOff
任何员工都可能有多个TblEmployment记录。在每个月结束时，需要一份报告，显示该月最后一天工资单上的所有员工。我需要帮助创建该报告的查询。
用户必须能够指定报告的月份和年份;即，不仅仅是当前或上个月，而是过去任何一年和一个月。
查询
必须考虑到用户可能指定上一个月的一个月，并且该月后员工可能已被雇用并多次下岗。
感谢您的帮助！
Sue
I''m working on a database that keeps track of employees hired by a general
contractor on a project by project basis. These employees are hired to
work
on a project and are then laid off either at the end of the work they were
hired to do or at the end of the project. Any employee may be hired, laid
off and rehired several times during the course of time. The tables I have
and the critical fields to my question are:
TblEmployee
EmployeeID
FirstName
LastName
TblEmployment
EmploymentID
EmployeeID
DateHired
DateLaidOff
Any employee may have several records in TblEmployment. At the end of each
month a report is needed showing all the employees on the payroll on the
last day of the month. I need help creating the query for that report. The
user must be able to specify the month and year for the report; ie, not
just
the current or previous month but any year and month in the past. The
query
must take into account that the user might specify a month in the previous
year and that an employee might have been hired and laid off several times
after that month.
Thanks for all help!
Sue
感谢您的快速回复！ />
我对你的反应感到困惑。 DateHired永远不会超过
DateLaidOff。此外，刚刚被雇用的员工将没有
DateLaidOff。
Sue
" deko" < WW ********* @ nospam.com>在消息中写道
news：mN ****************** @ newssvr21.news.prodigy.c om ...
Thank you for the quick response!
I''m confused by your response. DateHired will never be greater than
DateLaidOff. Also, an employee who was just hired will not have a
DateLaidOff.
Sue
"deko" <ww*******************************@nospam.com> wrote in message
news:mN******************@newssvr21.news.prodigy.c om...
TblEmployee
员工ID
FirstName
姓氏
TblEmployment
EmploymentID
员工ID
DateHired
DateLaidOff
任何员工都可能有多个TblEmployment记录。在每月
结束时，需要一份报告，显示该月最后一天工资单上的所有员工。我需要帮助创建该报告的查询。
用户必须能够指定报告的月份和年份;即，不仅仅是
TblEmployee
EmployeeID
FirstName
LastName
TblEmployment
EmploymentID
EmployeeID
DateHired
DateLaidOff
Any employee may have several records in TblEmployment. At the end of each month a report is needed showing all the employees on the payroll on the
last day of the month. I need help creating the query for that report. The user must be able to specify the month and year for the report; ie, not just
当前或上个月，而是过去的任何年份和月份。
the current or previous month but any year and month in the past. The
查询
必须考虑到用户可能在前一年的
中指定了一个月，并且该员工可能已经被雇佣并被解雇了几个$那个月之后b $ b次。
must take into account that the user might specify a month in the previous year and that an employee might have been hired and laid off several times after that month.
我假设你有一个表格的日期（例如10/31/2004 =" userDate"）
I assume you have a form where the date (e.g. 10/31/2004 = "userDate")
用户将输入
来运行每个报告。如果是这样，听起来你需要选择来自tblEmployment的不同记录，其中DateHired是>
DateLaidOff和DateHired是< userDate，然后在EmployeeID上加入tblEmployee。这有意义吗？
will be entered by the user for running each report. If so, it sounds like you
need to select distinct records from tblEmployment where DateHired is >
DateLaidOff and DateHired is < userDate, and then join to tblEmployee on
EmployeeID. Does this make sense?
这篇关于查询截至指定日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
