By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,574 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Query To Find Records As Of Specified Date

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
> 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.


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?
Nov 13 '05 #2

P: n/a
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...
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

Nov 13 '05 #3

P: n/a
Sue
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
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.


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?

Nov 13 '05 #4

P: n/a
Sue
Allen,

Thanks for the quick response!

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

Sue
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
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...
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


Nov 13 '05 #5

P: n/a
> 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.


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
Nov 13 '05 #6

P: n/a
Sue
<<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
"deko" <ww*******************************@nospam.com> wrote in message
news:ek*******************@newssvr21.news.prodigy. com...
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.


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

Nov 13 '05 #7

P: n/a
> 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
Nov 13 '05 #8

P: n/a
oops... this is the correct WHERE statement, I think...

WHERE DateHired < userDate AND (DateLaidOff Is Null OR DateLaidOff >
userDate)
Nov 13 '05 #9

P: n/a
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.
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:j9******************@newsread2.news.atl.earth link.net...
Allen,

Thanks for the quick response!

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

Sue
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
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...
> 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



Nov 13 '05 #10

P: n/a
Sue
No!

"deko" <ww*******************************@nospam.com> wrote in message
news:4R******************@newssvr21.news.prodigy.c om...
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

Nov 13 '05 #11

P: n/a
Sue wrote:
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
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


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.
Nov 13 '05 #12

P: n/a
you're awful bitchy for someone receiving free advice.

"Sue" <sm*****@notmyemail.com> wrote in message
news:_z******************@newsread2.news.atl.earth link.net...
No!

"deko" <ww*******************************@nospam.com> wrote in message
news:4R******************@newssvr21.news.prodigy.c om...
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


Nov 13 '05 #13

P: n/a
Sue
Thanks, Salad, that makes sense!

Sue
"Salad" <oi*@vinegar.com> wrote in message
news:%z****************@newsread3.news.pas.earthli nk.net...
Sue wrote:
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


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


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.

Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.