The right person for the job

[Guest]

I am trying to find available employees for orders. the orders have a
orderdate (this is when it can get started) and a length (how long it will
take to complete) but i am finding it difficult to create a query to do so.

i have created a query which retrieves all employees which are available
after the orderdate and have a higher duration then the length

the orders are input as follows

OrderID Orderdate Length Enddate
1 2/01/05 20 22/01/05
2 14/01/05 10 24/01/05
3 5/02/05 10 15/02/05
4 10/02/05 15 25/02/05
5 9/03/05 19 28/03/05

the employees availablity, which is connected to the employee table, is
entered as follows

Employeeid Startdate Duration Enddate
1 1/01/05 15 16/01/05
1 18/01/05 10 28/01/05
1 2/03/05 25 27/03/05
2 5/01/05 14 19/01/05
2 2/02/05 26 28/02/05
2 10/03/05 12 22/03/05
3 3/01/05 20 23/01/05
3 28/01/05 18 15/02/05
3 25/02/05 20 17/03/05
4 1/03/05 25 26/03/05
4 1/04/05 19 20/04/05
5 1/01/05 25 26/01/05
5 1/02/05 24 25/02/05
5 1/03/05 26 27/03/05
5 1/04/05 23 24/04/05

when i tried to restrict this by making sure the orderdate is before the
employee enddate, the query did not retrieve anything and even if this did,
the results would not be correct. i can seem to think how i can achieve this.

Can anyone help

 

[Ken Snell [MVP]]

Before you (or we) can model your decision process by a query, you need to
clearly state how you would make the decision about an employee's
availability if you do it without the use of the database. One thought that
I have is to look for employees where the orderdate is between the
employee's startdate and enddate; however, that ignores what needs to happen
if the orderdate-to-employeeenddate interval is shorter than the Length for
the order?

So, step back and write down exactly how the decision process occurs when
you do it manually. From that, it should be possible to model that decision
process in the database.

 

[Guest]

Thanks you

i understand what you are trying to say.

this query is part 2 of 2, i have already got a query that finds an employee
with the right expertise. this will join with that and retrieve all employees
that are right for the job.

the correct employee will be an employee who is available. therefore
employee startdate is after orderdate, orderdate is before employee enddate
and employee duration is more or equal to order length.

am i making sense ?

i have put the above on a query and retrieve nothing, or have you any other
suggestions.

 

[Ken Snell [MVP]]

Does the query that you're running to find the employees with the correct
expertise also return the Employeename, Startdate and Enddate fields (not
necessary, but it could also return the Duration field)? That will make
things a bit easier if it does. The example below (not tested) assumes
-- the name of this "select employees by expertise" query is "qryEmpExp"
-- "qryEmpExp" returns the EmployeeID, EmployeeName, StartDate, and
EndDate fields

SELECT Orders.OrderID, Orders.Orderdate, Q.EmployeeID,
Q.EmployeeName FROM Orders,
[SELECT qryEmpExp.EmployeeID, qryEmpExp.EmployeeName
FROM qryEmpExp WHERE Orders.Orderdate BETWEEN
qryEmpExp.StartDate AND qryEmpExp.EndDate AND
Orders.Enddate <= qryEmpExp.EndDate]. AS Q
ORDER BY Orders.OrderID, Q.EmployeeID;


--

Ken Snell
<MS ACCESS MVP>

 

[John Viescas]

PMJI, but your selection criteria as stated will never find an available
employee. As I look at your sample data, the duration of the order is
clearly end date minus start date - exactly the number of days available.
If you look for employees who are available only AFTER the order start date,
then the number of days available before the required order end date will
never be enough to satisfy the order. I think you'll get what you want if
employee start date <= order start date AND employee end date >= order end
date. The query should look something like:

SELECT Orders.*, qryEmployeeAvailable.*
FROM Orders, qryEmployeeAvailable
WHERE qryEmployeeAvailable.StartDate <= Orders.Orderdate
AND qryEmpoyeeAvailable.Enddate >= Orders.Enddate;

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

Hi, i have tried this and retrieve employee which have less duration. can you
make suggestions of any other methods that i can use to get an available
employee.

i will probably post this question again. hope you can help.

 

[Guest]

Thanks for the response, i think i am getting into a mess here. i am going to
post this question again and ask how i could do this. i have got myself
confused, sorry for telling you my probs.

yes i have tried this and it does not seem to bring the right information
up, can you make suggestions of different ways.

i have employee table:
employeeId
Name
Contact Details

Employee Availabililty Table:
EmployeeId
StartDate
Duration
Enddate

Order Table:
OrderId
CustomerID
OrderDate
Length
Status

you can make assummations on relationshipships, but can you think of any
other way i can try to get availability.

Does the query that you're running to find the employees with the correct
expertise also return the Employeename, Startdate and Enddate fields (not
necessary, but it could also return the Duration field)? That will make
things a bit easier if it does. The example below (not tested) assumes
-- the name of this "select employees by expertise" query is "qryEmpExp"
-- "qryEmpExp" returns the EmployeeID, EmployeeName, StartDate, and
EndDate fields

SELECT Orders.OrderID, Orders.Orderdate, Q.EmployeeID,
Q.EmployeeName FROM Orders,
[SELECT qryEmpExp.EmployeeID, qryEmpExp.EmployeeName
FROM qryEmpExp WHERE Orders.Orderdate BETWEEN
qryEmpExp.StartDate AND qryEmpExp.EndDate AND
Orders.Enddate <= qryEmpExp.EndDate]. AS Q
ORDER BY Orders.OrderID, Q.EmployeeID;


--

Ken Snell
<MS ACCESS MVP>

Thanks you

i understand what you are trying to say.

this query is part 2 of 2, i have already got a query that finds an
employee
with the right expertise. this will join with that and retrieve all
employees
that are right for the job.

the correct employee will be an employee who is available. therefore
employee startdate is after orderdate, orderdate is before employee
enddate
and employee duration is more or equal to order length.

am i making sense ?

i have put the above on a query and retrieve nothing, or have you any
other
suggestions.

 

[John Viescas]

Given the sample data you posted earlier, what result did you get when you
ran my query?

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

hi,

the query cames back with nothing !

and i apologise, i do not have a enddate field in the order table

i have put the tables below, sorry, does this make it any clearer:

OrderID Orderdate Length
1 2/01/05 20
2 14/01/05 10
3 5/02/05 10
4 10/02/05 15
5 9/03/05 19

the employees availablity, which is connected to the employee table,
is
entered as follows

Employeeid Startdate Duration Enddate
1 1/01/05 15 16/01/05
1 18/01/05 10 28/01/05
1 2/03/05 25 27/03/05
2 5/01/05 14 19/01/05
2 2/02/05 26 28/02/05
2 10/03/05 12 22/03/05
3 3/01/05 20 23/01/05
3 28/01/05 18 15/02/05
3 25/02/05 20 17/03/05
4 1/03/05 25 26/03/05
4 1/04/05 19 20/04/05
5 1/01/05 25 26/01/05
5 1/02/05 24 25/02/05
5 1/03/05 26 27/03/05
5 1/04/05 23 24/04/05

 

[John Viescas]

Don-

If there's no EndDate in the Order table, then my query should not have run
at all because it references a non-existent field! Try this:

SELECT Orders.*, qryEmployeeAvailable.*
FROM Orders, qryEmployeeAvailable
WHERE qryEmployeeAvailable.StartDate <= Orders.Orderdate
AND qryEmpoyeeAvailable.Enddate >= CDate(Orders.Orderdate + Orders.Length);


--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)