Query To Find Records As Of Specified Date

  • Thread starter Thread starter Sue
  • Start date Start date
S

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
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.
Click to expand...

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. 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.
 
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
 
Allen,

Thanks for the quick response!

In #3, an employee who was just hired will not have a DateLaidOff.

Sue


Allen Browne said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Sue said:
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
Click to expand...
Click to expand...
 
DateHired will never be greater than DateLaidOff

What if someone was laid off on the 2nd and then rehired on the 15th?
Also, an employee who was just hired will not have a DateLaidOff.
Click to expand...

If DateLaidOff is blank, then any DateHired is greater.

The idea is to narrow the set to your criteria and exclude any Employee that
is not part of the set
 
<<What if someone was laid off on the 2nd and then rehired on the 15th?>>

The 15th is a different record! Also, if an employee has been hired and laid
off several times, how will the query know which dates to use?

<<The idea is to narrow the set to your criteria and exclude any Employee
that is not part of the set>>
Agreed but what you suggest does not do that.

Sue
 
Use Nz() to substutute some far off future date, e.g.:

Between [tblEmployment].[DateHired] And
Nz([tblEmployment].[DateLaidOff], #1/1/2999#)


Better still, edit the SQL View of the query so the WHERE clause reads:

WHERE ((tblEmployment.DateHired <= [Last Day Of Month]) AND
((tblEmployment.DateLaidOff >= [Last Day Of Month]) OR
(tblEmployment.DateLaidOff Is Null)))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sue said:
Allen,

Thanks for the quick response!

In #3, an employee who was just hired will not have a DateLaidOff.

Sue


Allen Browne said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Sue said:
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
Click to expand...
Click to expand...
Click to expand...
 
DateHired will never be greater than DateLaidOff.

Does DateLaidOff become null when someone is rehired? If so, this might
work:

SELECT tblEmployement.EmployeeID, tblEmployee.FirstName
tblEmployee.LastName FROM tblEmployement INNER JOIN
tblEmployee ON tblEmployement.EmployeeID=tblEmployee.EmployeeID
WHERE DateHired < userDate AND DateLaidOff > userDate
 
oops... this is the correct WHERE statement, I think...

WHERE DateHired < userDate AND (DateLaidOff Is Null OR DateLaidOff >
userDate)
 
No!

deko said:
Does DateLaidOff become null when someone is rehired? If so, this might
work:

SELECT tblEmployement.EmployeeID, tblEmployee.FirstName
tblEmployee.LastName FROM tblEmployement INNER JOIN
tblEmployee ON tblEmployement.EmployeeID=tblEmployee.EmployeeID
WHERE DateHired < userDate AND DateLaidOff > userDate
Click to expand...
 
Sue said:
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
Click to expand...

One thing I noticed is that you do not have a project ID associated with
the TblEmployment. An employee could work on more than one
project...perhaps. Or an employee could start a project in one month,
end in another, and start another.
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
Click to expand...

I would create a query that links tblEmployment to TblEmployee (in order
to get the name info)

If you enter the month/year in a form, you should get the last day of
that month. In the form, you could create a field called
LastDayOfMonth and set the visible property to False. When you launch
the report, calculate the last day of the month. You know that the date
hired must be less than or equal to the last day of the month. The
DateLaidOff must be greater than the last day of the month or else be
null/empty.

Let's say you had Text box txtMonth and txtYear for month/year entry.
The following code line will store the last day of the month to that
textbox.

Me.LastDayOfMonth = DateAdd("m",1,DateSerial(txtYear,txtMonth,1)) -1

If you enter 10 (txtMonth) and 2004 (txtYear), the above code lone will
return/store 10/31/2004 to the textbox LastDayOfMonth.

Let's say your form is called ReportForm.

In the criteria row for DateHired in your query enter
<=Forms!ReportFOrm!LastDayOfMonth
to select all records where the person was hired in the month/year
selected or prior to that.

In the criteria row for DateLaidOff enter
Is Null Or >Forms!ReportFOrm!LastDayOfMonth
to get all records where the person is still working on that project or
the person was laid off after the last day of the month.
 
Thanks, Salad, that makes sense!

Sue


Salad said:
One thing I noticed is that you do not have a project ID associated with
the TblEmployment. An employee could work on more than one
project...perhaps. Or an employee could start a project in one month,
end in another, and start another.


I would create a query that links tblEmployment to TblEmployee (in order
to get the name info)

If you enter the month/year in a form, you should get the last day of
that month. In the form, you could create a field called
LastDayOfMonth and set the visible property to False. When you launch
the report, calculate the last day of the month. You know that the date
hired must be less than or equal to the last day of the month. The
DateLaidOff must be greater than the last day of the month or else be
null/empty.

Let's say you had Text box txtMonth and txtYear for month/year entry.
The following code line will store the last day of the month to that
textbox.

Me.LastDayOfMonth = DateAdd("m",1,DateSerial(txtYear,txtMonth,1)) -1

If you enter 10 (txtMonth) and 2004 (txtYear), the above code lone will
return/store 10/31/2004 to the textbox LastDayOfMonth.

Let's say your form is called ReportForm.

In the criteria row for DateHired in your query enter
<=Forms!ReportFOrm!LastDayOfMonth
to select all records where the person was hired in the month/year
selected or prior to that.

In the criteria row for DateLaidOff enter
Is Null Or >Forms!ReportFOrm!LastDayOfMonth
to get all records where the person is still working on that project or
the person was laid off after the last day of the month.
Click to expand...
 
Back
Top