Employee Emergency

[Guest]

Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Nikos Yannacopoulos]

Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]
FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =
tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

 

[David Ferguson]

Not exactly what you're looking for but it may help.

Create a query using your OrderID table to find out the highest skillID
required for each order (using Max in the Totals row)

Then create a second query using the employeeID table and the query above
linking the SkillId and the MaxofSkillID fields.

This will show which employees have the appropriate skill level for each
order

(Note: This is assuming that the the skillIDs are such that an employee
can't have skill 2 unless he has skill 1)

HTH

Dave

 

[JohnFol]

Hey Don, first you complain that no-one has volunteered their time and
effort in helping you, and then you don't have the decency to acknowledge
the support from Nikos and David.

Not really in the spirit of NG's is it . .. . .

 

[Guest]

Hello,

i am really sorry, i know how this looks but i really do appreciate the help
that every body has gave me on this community, especially Nikos, who has
helped and solved a problem for me early. i am a student and go back home
from my uni town becuase i have to work to support myself during the week, i
work 12hrs a day for friday night and saturday night, i have just come back
and will be here again for week. i dont get time to spend on the computer
over the weekend.

i really appreciate the help i have recieved, i dont even know if you will
get this message becuase it is on the third page but i do try the ideas i get
and even give feedback.

i am going to try the ideas that i have been given now and will post
feedback, which i do anyway. could you tell me how i can post comments for
nikos and david

 

[Guest]

Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.

Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]
FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =
tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Guest]

Hello dave,

thank you for your response,

the employee could have any of these skill in no specfic order, i only chose
the numbers becuase i thought it would be the best way to identify them. i
think Nikos has a good idea but it needs abit of modification, please could
you help, i dont understand it but i have asked for that.

i would be grateful if you could read the JohnFols email (who has made a
good point) and please read my reply, i do need your help and i do appreciate
all help i do recieve, please dont think that i dont.

thank you,

 

[Guest]

sorry, i have given an basic example, hope this helps

employee id SkillId
1 1
1 2
2 1
2 2
2 3
3 2
4 1
4 3
5 2
5 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2
3 2
3 3
4 3
5 1
5 3

sorry to be a pain, hope you can help
thanks again

Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]
FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =
tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[John Spencer (MVP)]

UNTESTED three query solution. Not sure it will work, but ...

Q1:
SELECT EmployeeID, OrderID, E.SkillID
FROM Employees AS E INNER JOIN Orders AS O
ON E.SkillID = O.SkillID

Q2:
SELECT OrderID, Count(SkillID) as CountOfSkills
FROM Orders
ORDER BY OrderID

SELECT Q1.EmployeeID, Q1.OrderId
FROM Q1 Inner Join Q2
ON Q1.OrderID = Q2.OrderID
GROUP BY Q1.EmployeeID, Q1.OrderID
HAVING Count(Q1.SkillId) = Q2.CountOfSkills

This assumes not duplicates in the skill ids for an employee or for an order.

 

[Nikos Yannacopoulos]

Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill
ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:

Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]
FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =
tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Guest]

Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill
ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:

Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]
FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =
tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:

Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Nikos Yannacopoulos]

Don,

Assuming you have a fom called MyForm, with a control called ctlOrderID
on it, in which you enter (or select, if the control is a
listbox/combobox) the order ID to match to an employee, then the query
can be modified to read that parameter doractly from the form. The query
becomes:

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)= Forms![MyForm]![ctlOrderID]) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]="
& Forms![MyForm]![ctlOrderID])))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

That is, I have used a reference to the control on the form instead of
typing in the order ID.

The logic behind the query (switch to design view to help understand):
Join the two tables on SkillID, filter on OrderID; this returns all
employees with at least one skill required by the order; one row per
skill; Count SkillID from tblOrders = number of skills required matching
a particular employee's skills (group on employee , so count per
employee), so if that's equal to the number of skills required by the
order regardless of employees (that's the DCOunt on tblOrders) in means
that the particular employee has all the skills required. I'm not sure
I'm explaining this very well, it's kind of complicated; it may help you
understanding if you recobnstruct the query and see what you get step by
step, i.e. first just the tables and the orderID filter, then the group
by / count, then the DCount filter.

HTH,
Nikos

Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

:

Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill
ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:



Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]

FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =

tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:


Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Guest]

Thanks for the response, sorry for thedelay in my response but i couldnt seem
to logon yesterday. I have Nikos query, i appreciate the time and help you
have provided.

Thanks

 

[Guest]

Hello Nikos,

sorry for the delay in responding, i couldnt logon yesterday but this works
just fine and thanks for the explanation.

I dont want to be a burden but i have another problem but this time it has
got to do with the availabilty.

Don,

Assuming you have a fom called MyForm, with a control called ctlOrderID
on it, in which you enter (or select, if the control is a
listbox/combobox) the order ID to match to an employee, then the query
can be modified to read that parameter doractly from the form. The query
becomes:

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)= Forms![MyForm]![ctlOrderID]) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]="
& Forms![MyForm]![ctlOrderID])))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

That is, I have used a reference to the control on the form instead of
typing in the order ID.

The logic behind the query (switch to design view to help understand):
Join the two tables on SkillID, filter on OrderID; this returns all
employees with at least one skill required by the order; one row per
skill; Count SkillID from tblOrders = number of skills required matching
a particular employee's skills (group on employee , so count per
employee), so if that's equal to the number of skills required by the
order regardless of employees (that's the DCOunt on tblOrders) in means
that the particular employee has all the skills required. I'm not sure
I'm explaining this very well, it's kind of complicated; it may help you
understanding if you recobnstruct the query and see what you get step by
step, i.e. first just the tables and the orderID filter, then the group
by / count, then the DCount filter.

HTH,
Nikos

Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

:

Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill
ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Don wrote:

Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:



Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]

FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =

tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:


Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Guest]

I have orders with a start date, duration and end date

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

now, the employees have another table, which stores their availablity

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

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

but

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.

have you any ideas on how i can retrieve the best result, i have displayed
my data above. thanks Nikos, you are a great help, thank you

Don,

Assuming you have a fom called MyForm, with a control called ctlOrderID
on it, in which you enter (or select, if the control is a
listbox/combobox) the order ID to match to an employee, then the query
can be modified to read that parameter doractly from the form. The query
becomes:

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)= Forms![MyForm]![ctlOrderID]) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]="
& Forms![MyForm]![ctlOrderID])))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

That is, I have used a reference to the control on the form instead of
typing in the order ID.

The logic behind the query (switch to design view to help understand):
Join the two tables on SkillID, filter on OrderID; this returns all
employees with at least one skill required by the order; one row per
skill; Count SkillID from tblOrders = number of skills required matching
a particular employee's skills (group on employee , so count per
employee), so if that's equal to the number of skills required by the
order regardless of employees (that's the DCOunt on tblOrders) in means
that the particular employee has all the skills required. I'm not sure
I'm explaining this very well, it's kind of complicated; it may help you
understanding if you recobnstruct the query and see what you get step by
step, i.e. first just the tables and the orderID filter, then the group
by / count, then the DCount filter.

HTH,
Nikos

Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

:

Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill
ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Don wrote:

Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:



Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]

FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =

tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:


Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Nikos Yannacopoulos]

Don,

Why "the orderdate is before the employee enddate"? I would have thought
the order date must be before (employee enddate - order length), so
there is enough time for the employee to complete it. Am I missing
something here? What was your rationale?
At any rate, it would help if you posted your query's SQL expression
(even better, the one that worked and the one that didn't!)

Nikos

I have orders with a start date, duration and end date

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

now, the employees have another table, which stores their availablity

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

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

but

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.

have you any ideas on how i can retrieve the best result, i have displayed
my data above. thanks Nikos, you are a great help, thank you


:

Don,

Assuming you have a fom called MyForm, with a control called ctlOrderID
on it, in which you enter (or select, if the control is a
listbox/combobox) the order ID to match to an employee, then the query
can be modified to read that parameter doractly from the form. The query
becomes:

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)= Forms![MyForm]![ctlOrderID]) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]="
& Forms![MyForm]![ctlOrderID])))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

That is, I have used a reference to the control on the form instead of
typing in the order ID.

The logic behind the query (switch to design view to help understand):
Join the two tables on SkillID, filter on OrderID; this returns all
employees with at least one skill required by the order; one row per
skill; Count SkillID from tblOrders = number of skills required matching
a particular employee's skills (group on employee , so count per
employee), so if that's equal to the number of skills required by the
order regardless of employees (that's the DCOunt on tblOrders) in means
that the particular employee has all the skills required. I'm not sure
I'm explaining this very well, it's kind of complicated; it may help you
understanding if you recobnstruct the query and see what you get step by
step, i.e. first just the tables and the orderID filter, then the group
by / count, then the DCount filter.

HTH,
Nikos

Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

:



Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill

ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Don wrote:


Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON

tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:




Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]

FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =


tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:



Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Guest]

hello Nikos,

you are right, i also provided you with some incorrect information, there is
not enddate on the orderdate.

but i understand what you are saying, what should i put in the query to get
what i need, i think that i am getting my expression mixed up.

no you are not missing anything, it is me

Can you tell me, how i can do this correctly
i am trying also, i will give you feedback on my efforts, if you like.
i have put the table information below:

Order Table

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

Employee Table

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

thanks Nikos

Don,

Why "the orderdate is before the employee enddate"? I would have thought
the order date must be before (employee enddate - order length), so
there is enough time for the employee to complete it. Am I missing
something here? What was your rationale?
At any rate, it would help if you posted your query's SQL expression
(even better, the one that worked and the one that didn't!)

Nikos

I have orders with a start date, duration and end date

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

now, the employees have another table, which stores their availablity

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

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

but

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.

have you any ideas on how i can retrieve the best result, i have displayed
my data above. thanks Nikos, you are a great help, thank you


:

Don,

Assuming you have a fom called MyForm, with a control called ctlOrderID
on it, in which you enter (or select, if the control is a
listbox/combobox) the order ID to match to an employee, then the query
can be modified to read that parameter doractly from the form. The query
becomes:

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)= Forms![MyForm]![ctlOrderID]) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]="
& Forms![MyForm]![ctlOrderID])))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

That is, I have used a reference to the control on the form instead of
typing in the order ID.

The logic behind the query (switch to design view to help understand):
Join the two tables on SkillID, filter on OrderID; this returns all
employees with at least one skill required by the order; one row per
skill; Count SkillID from tblOrders = number of skills required matching
a particular employee's skills (group on employee , so count per
employee), so if that's equal to the number of skills required by the
order regardless of employees (that's the DCOunt on tblOrders) in means
that the particular employee has all the skills required. I'm not sure
I'm explaining this very well, it's kind of complicated; it may help you
understanding if you recobnstruct the query and see what you get step by
step, i.e. first just the tables and the orderID filter, then the group
by / count, then the DCount filter.

HTH,
Nikos

Don wrote:

Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

:



Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill

ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Don wrote:


Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON

tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:




Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]

FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =


tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:



Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Nikos Yannacopoulos]

OK... to make this simpler, since there is a form called MyForm where
the OrderID is read from, I'll assume there a two more controls on it,
ctlOrderDate and ctlOrderLength which pick up this information once the
OrderID is selected. Given This, I would envisage something like:

SELECT Employeeid FROM tblEmployeeAvailability
WHERE (Duration >= Forms![MyForm]![ctlOrderLength]
AND EndDate >= (Forms![MyForm]![ctlOrderDate] +
Forms![MyForm]![ctlOrderLength]))

hello Nikos,

you are right, i also provided you with some incorrect information, there is
not enddate on the orderdate.

but i understand what you are saying, what should i put in the query to get
what i need, i think that i am getting my expression mixed up.

no you are not missing anything, it is me

Can you tell me, how i can do this correctly
i am trying also, i will give you feedback on my efforts, if you like.
i have put the table information below:

Order Table

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

Employee Table

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

thanks Nikos

:

Don,

Why "the orderdate is before the employee enddate"? I would have thought
the order date must be before (employee enddate - order length), so
there is enough time for the employee to complete it. Am I missing
something here? What was your rationale?
At any rate, it would help if you posted your query's SQL expression
(even better, the one that worked and the one that didn't!)

Nikos

I have orders with a start date, duration and end date

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

now, the employees have another table, which stores their availablity

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

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

but

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.

have you any ideas on how i can retrieve the best result, i have displayed
my data above. thanks Nikos, you are a great help, thank you


:



Don,

Assuming you have a fom called MyForm, with a control called ctlOrderID
on it, in which you enter (or select, if the control is a
listbox/combobox) the order ID to match to an employee, then the query
can be modified to read that parameter doractly from the form. The query
becomes:

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON

tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)= Forms![MyForm]![ctlOrderID]) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]="
& Forms![MyForm]![ctlOrderID])))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

That is, I have used a reference to the control on the form instead of
typing in the order ID.

The logic behind the query (switch to design view to help understand):
Join the two tables on SkillID, filter on OrderID; this returns all
employees with at least one skill required by the order; one row per
skill; Count SkillID from tblOrders = number of skills required matching
a particular employee's skills (group on employee , so count per
employee), so if that's equal to the number of skills required by the
order regardless of employees (that's the DCOunt on tblOrders) in means
that the particular employee has all the skills required. I'm not sure
I'm explaining this very well, it's kind of complicated; it may help you
understanding if you recobnstruct the query and see what you get step by
step, i.e. first just the tables and the orderID filter, then the group
by / count, then the DCount filter.

HTH,
Nikos

Don wrote:


Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

:




Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill


ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Don wrote:



Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON


tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:





Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]

FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =



tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:




Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Guest]

Thanks for the reply Nikos,

i will try this but i am some functionality problems with the forms. i am
working through these at the moment.

i have been suggested that i could do queries in the vb part of a form,
would this be a good idea to do these queries inside the forms.

the only problem would be, i dont know how to do this, i know basic vb but
not at this level.

any ideas, nikos

thanks

p.s. i will try the query.

OK... to make this simpler, since there is a form called MyForm where
the OrderID is read from, I'll assume there a two more controls on it,
ctlOrderDate and ctlOrderLength which pick up this information once the
OrderID is selected. Given This, I would envisage something like:

SELECT Employeeid FROM tblEmployeeAvailability
WHERE (Duration >= Forms![MyForm]![ctlOrderLength]
AND EndDate >= (Forms![MyForm]![ctlOrderDate] +
Forms![MyForm]![ctlOrderLength]))

hello Nikos,

you are right, i also provided you with some incorrect information, there is
not enddate on the orderdate.

but i understand what you are saying, what should i put in the query to get
what i need, i think that i am getting my expression mixed up.

no you are not missing anything, it is me

Can you tell me, how i can do this correctly
i am trying also, i will give you feedback on my efforts, if you like.
i have put the table information below:

Order Table

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

Employee Table

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

thanks Nikos

:

Don,

Why "the orderdate is before the employee enddate"? I would have thought
the order date must be before (employee enddate - order length), so
there is enough time for the employee to complete it. Am I missing
something here? What was your rationale?
At any rate, it would help if you posted your query's SQL expression
(even better, the one that worked and the one that didn't!)

Nikos

Don wrote:

I have orders with a start date, duration and end date

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

now, the employees have another table, which stores their availablity

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

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

but

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.

have you any ideas on how i can retrieve the best result, i have displayed
my data above. thanks Nikos, you are a great help, thank you


:



Don,

Assuming you have a fom called MyForm, with a control called ctlOrderID
on it, in which you enter (or select, if the control is a
listbox/combobox) the order ID to match to an employee, then the query
can be modified to read that parameter doractly from the form. The query
becomes:

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON

tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)= Forms![MyForm]![ctlOrderID]) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]="
& Forms![MyForm]![ctlOrderID])))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

That is, I have used a reference to the control on the form instead of
typing in the order ID.

The logic behind the query (switch to design view to help understand):
Join the two tables on SkillID, filter on OrderID; this returns all
employees with at least one skill required by the order; one row per
skill; Count SkillID from tblOrders = number of skills required matching
a particular employee's skills (group on employee , so count per
employee), so if that's equal to the number of skills required by the
order regardless of employees (that's the DCOunt on tblOrders) in means
that the particular employee has all the skills required. I'm not sure
I'm explaining this very well, it's kind of complicated; it may help you
understanding if you recobnstruct the query and see what you get step by
step, i.e. first just the tables and the orderID filter, then the group
by / count, then the DCount filter.

HTH,
Nikos

Don wrote:


Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

:




Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill


ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Don wrote:



Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON


tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:





Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]

FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =



tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:




Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?

 

[Nikos Yannacopoulos]

Don,

The general answer is you can do anything with VB behind forms; now
whetjer this is the best way to go for a particular task, that's
something I cannot answer not having the full picture. VB pays if you
know what you're doing, but will get you nowhere if the task is out of
your league - which, again, I cannot assess. I always encourage people
to go that way, knowing the kind of power VBA unleashes in Access, but
it's only fair to warn it's not an overnight process. At any rate, I
don't think there's a chance to make it work in VBA, regardless of your
skill level, until you have established the concept, so my advice at
this point would be to continue the way you are going, and when you're
happy that you've got a working algorithm them you might look into
converting that to VBA for efficiency - or even just for the kicks!

Nikos

Thanks for the reply Nikos,

i will try this but i am some functionality problems with the forms. i am
working through these at the moment.

i have been suggested that i could do queries in the vb part of a form,
would this be a good idea to do these queries inside the forms.

the only problem would be, i dont know how to do this, i know basic vb but
not at this level.

any ideas, nikos

thanks

p.s. i will try the query.

:

OK... to make this simpler, since there is a form called MyForm where
the OrderID is read from, I'll assume there a two more controls on it,
ctlOrderDate and ctlOrderLength which pick up this information once the
OrderID is selected. Given This, I would envisage something like:

SELECT Employeeid FROM tblEmployeeAvailability
WHERE (Duration >= Forms![MyForm]![ctlOrderLength]
AND EndDate >= (Forms![MyForm]![ctlOrderDate] +
Forms![MyForm]![ctlOrderLength]))

hello Nikos,

you are right, i also provided you with some incorrect information, there is
not enddate on the orderdate.

but i understand what you are saying, what should i put in the query to get
what i need, i think that i am getting my expression mixed up.

no you are not missing anything, it is me

Can you tell me, how i can do this correctly
i am trying also, i will give you feedback on my efforts, if you like.
i have put the table information below:

Order Table

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

Employee Table

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

thanks Nikos

:



Don,

Why "the orderdate is before the employee enddate"? I would have thought
the order date must be before (employee enddate - order length), so
there is enough time for the employee to complete it. Am I missing
something here? What was your rationale?
At any rate, it would help if you posted your query's SQL expression
(even better, the one that worked and the one that didn't!)

Nikos

Don wrote:


I have orders with a start date, duration and end date

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

now, the employees have another table, which stores their availablity

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

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

but

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.

have you any ideas on how i can retrieve the best result, i have displayed
my data above. thanks Nikos, you are a great help, thank you


:




Don,

Assuming you have a fom called MyForm, with a control called ctlOrderID
on it, in which you enter (or select, if the control is a
listbox/combobox) the order ID to match to an employee, then the query
can be modified to read that parameter doractly from the form. The query
becomes:

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON


tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)= Forms![MyForm]![ctlOrderID]) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]="
& Forms![MyForm]![ctlOrderID])))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

That is, I have used a reference to the control on the form instead of
typing in the order ID.

The logic behind the query (switch to design view to help understand):
Join the two tables on SkillID, filter on OrderID; this returns all
employees with at least one skill required by the order; one row per
skill; Count SkillID from tblOrders = number of skills required matching
a particular employee's skills (group on employee , so count per
employee), so if that's equal to the number of skills required by the
order regardless of employees (that's the DCOunt on tblOrders) in means
that the particular employee has all the skills required. I'm not sure
I'm explaining this very well, it's kind of complicated; it may help you
understanding if you recobnstruct the query and see what you get step by
step, i.e. first just the tables and the orderID filter, then the group
by / count, then the DCount filter.

HTH,
Nikos

Don wrote:



Thank you very much Nikos, this is the second time you have helped me !

you dont understand how thankful i am, if there is anything i could do, just
ask.

if i could i ask you for two things:

1 if you could explan the query to me becuase i want to understand it and
learn off it, if you dont mind.

2 will i be able to change the order id on the query on a form.

thank you very much, you have just got me over a very big hurdle.

thanks

:





Don,

Yes, I was wrong, and yes, I was close. Your new set of data was very
helpful in establishing that... I changed the query to:

SELECT tblEmployeeSlikk.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill



ON tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON
tblEmployee.EmployeeID = tblEmployeeskill.EmployeeID
WHERE (((tblOrderSkill.OrderID)=1))
GROUP BY tblEmployeeSkill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename
HAVING
(((Count(tblOrderSkill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=1")));

and that seems to work. Wanna try it?

Nikos

Don wrote:




Hello Nikos,
i really appreicate your response, thank you.

i tried the following query that you gave me, i change the table names to
the right ones.

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename

FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON



tblEmployeeskill.SkillID = tblOrderskill.SkillID) ON tblEmployee.EmployeeID =
tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=1) AND
((tblOrderskill.SkillID)=DCount("[SkillID]","tblOrderskill","[OrderID]=1")))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename;

this query retrieves all the names that can do skill 1 but i orders that can
do more then one skill, so skill 1 and 2 or skill 1 and 3, not 2, or 2 and 3.

i am really sorry to be a pain but could you explain the query to me becuase
i think this could be modified to work, i think you have the right idea
there. Really sorry.

p.s. i do appreciate all the help that i recieve.

thanks, hope you can help.


:






Don,

This is quite interesting! Try this:

SELECT tblEmployees.[Employee ID]

FROM tblEmployees INNER JOIN tblOrders ON tblEmployees.SkillID =




tblOrders.SkillID
WHERE (((tblOrders.[Order ID])=1) AND
((tblOrders.SkillID)=DCount("[SkillID]","tblOrders","[Order ID]=1")))
GROUP BY tblEmployees.[Employee ID];

where I have assumed table names tblEmployees and tblOrders

HTH,
Nikos

Don wrote:





Hello,

This is the fourth time i have put this on here, please can someone help me,
i am desperate now, this is for my project. everybody i know can not do this !

I have employees that can have up to three different expertise and orders
that can also have three different requirements. all requirements and
expertise are stored on individual fields eg if employee has 2 expertise, it
is stored as follows: -

employee id SkillId
1 1
1 2
2 1
2 2
2 3

the order requirements are stored: -

Order ID SkillId
1 1
1 2
1 3
2 1
2 2

in the above case only employee 2 can do order 1 but both employee 1 and 2
can carry out order 2

I have put put skill tables in the same query and matched the skill id but
this retrieves all employees that can do every skill. i have tried to group
them but i can not do this either ?

Please can some one help ?