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
13  5806 
> 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?
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
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?
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
> 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
<<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
> 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)
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
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
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.
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: Jim |
last post by:
I'm having a problem with a date query..im trying to pull customer
data based on a date specified from a form to 3 months prior to the
date specified. So lets say in the form I specified 1/2/2004....
|
by: Kissi5559 |
last post by:
Hello,
Could anyone help me with a query? I have a date field and want to display only
records for a specified month.
Example:
Date:5/12/2004
Query for all records in the month of May, 2004.
...
|
by: Serious_Practitioner |
last post by:
Good day, and thank you in advance for any assistance you can provide.
I have a table in an Access 2000 .mdb file, and I've run into something odd
and insolvable, at least for me. The database is...
|
by: Mark Roughton |
last post by:
I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and...
|
by: Stan |
last post by:
I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for...
|
by: jennwilson |
last post by:
Using Access 2000 - I have a query that is suppose to return the records from table within specified time range and find matching data from another table .
Table houses Clinician name, location...
|
by: nhkam |
last post by:
I am using MS Access 2007
I have a transaction table which holds all records with posting date on each of them. I used a make table query to find out the max date hold in the transaction table and...
|
by: dizzydangler |
last post by:
Hi all,
I am a new Access user and just starting to get my head around some of the basic concepts, so please take it easy on me :) My company has been managing client records on excel, and I’m in...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
