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

Assignments

P: n/a
I work for a company with many employees and we provide multiple services to
cliemts on a project basis. At any point in time, employees may be assigned
to one or more projects. Also, for certain projects, an employee may be on
and off a project several times during the life of the project. We have a
large Access database that is use for tracking projects. The pertient tables
I am working with are:
TblEmployee
EmployeeID
etc

TblProject
ProjectID
etc

TblEmployeeOnProject
EmployeeOnProjectID
ProjectID
EmployeeID
StartDate
EndDate
etc

I am currently working on an addition to the database for reporting who was
assigned to a selected project during a selected date range. I need advise
on how to write the query that will give me a list of employees assigned to
a project during a selected date range.
a) no duplicates if on and off more than once during date range
b) some employees may have been working on the project during the date
range but are not currently working on the project. These employees would
have a StartDate and EndDate in TblEmployeeOnProject
c) some employees may have been working on the project during the date
range and are still currently working on the project. These employees would
have a StartDate but no EndDate in TblEmployeeOnProject

Thanks for all advise!

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


P: n/a
As a start... created in the Query Builder and tried with very limited data
and even more limited testing, to return employees assigned to ProjectID = 1
between the dates 2/1/2004 and 5/25/2004:

SELECT DISTINCTROW tblEmployee.EmployeeFirstName,
tblEmployee.EmployeeLastName, tblProject.ProjectName
FROM tblProject RIGHT JOIN (tblEmployee RIGHT JOIN tblEmployeeOnProject ON
tblEmployee.EmployeeID = tblEmployeeOnProject.EmployeeID) ON
tblProject.ProjectID = tblEmployeeOnProject.ProjectID
WHERE (((tblEmployeeOnProject.ProjectID)=1) AND
((tblEmployeeOnProject.StartDate)<=#5/25/2004#) AND
((tblEmployeeOnProject.EndDate) Is Null)) OR
(((tblEmployeeOnProject.ProjectID)=1) AND
((tblEmployeeOnProject.StartDate)<=#5/25/2004#) AND
((tblEmployeeOnProject.EndDate)>=#2/1/2004#));

In real life, of course, you'll have to create a form to enter the From and
To dates of the period of interest, and the Project, and build the SQL
statement (not always as easy as it sounds) to match this, with your user's
selected ID and dates. But, getting a query that works is the first, and
sometimes most difficult, step.

Larry Linson
Microsoft Access MVP

"Richard" <rs****@earthlink.net> wrote in message
news:G5*****************@newsread3.news.atl.earthl ink.net...
I work for a company with many employees and we provide multiple services to cliemts on a project basis. At any point in time, employees may be assigned to one or more projects. Also, for certain projects, an employee may be on
and off a project several times during the life of the project. We have a
large Access database that is use for tracking projects. The pertient tables I am working with are:
TblEmployee
EmployeeID
etc

TblProject
ProjectID
etc

TblEmployeeOnProject
EmployeeOnProjectID
ProjectID
EmployeeID
StartDate
EndDate
etc

I am currently working on an addition to the database for reporting who was assigned to a selected project during a selected date range. I need advise
on how to write the query that will give me a list of employees assigned to a project during a selected date range.
a) no duplicates if on and off more than once during date range
b) some employees may have been working on the project during the date
range but are not currently working on the project. These employees would
have a StartDate and EndDate in TblEmployeeOnProject
c) some employees may have been working on the project during the date
range and are still currently working on the project. These employees would have a StartDate but no EndDate in TblEmployeeOnProject

Thanks for all advise!

Richard

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.