Employee Emergency

[Guest]

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

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]))


DonMoody wrote:

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 query I gave you a couple of posts ago works, I tried it. If it's
not working for you then you are not getting something right (a
table/field/form/control name?). Look closer.

Once you crack this, it will return all qualifying eployees' slots (nine
for order 1, as per your example data). Then joining this with the
previous query on EmployeeID in a new query, will give you qualifying
slots for qualifying employees only.

Nikos

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

:

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]))


DonMoody wrote:


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 ?

 

[Guest]

Thanks Nikos,

just got out of bed, going work, got a little time!

i understand what your were trying to say now. i will try this and get beack
to you but this will be on sunday evening, when i go back.

thank you very much nikos, you dont understand how much i appreciate that
the help that you have givenme.

thanks, i will get back to you

Don,

The query I gave you a couple of posts ago works, I tried it. If it's
not working for you then you are not getting something right (a
table/field/form/control name?). Look closer.

Once you crack this, it will return all qualifying eployees' slots (nine
for order 1, as per your example data). Then joining this with the
previous query on EmployeeID in a new query, will give you qualifying
slots for qualifying employees only.

Nikos

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

:

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

DonMoody wrote:

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]))


DonMoody wrote:


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 ?

 

[Guest]

Hi Nikos,

this query works just fine but now i am confused on how i am going to put
two of the queries that you gave together becuase i initally thought that the
queries would not be on the forms.

Query 1

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename, tblEmployee.Surname
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID=tblOrderskill.SkillID) ON
tblEmployee.EmployeeID=tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=Forms!frmtest!txtOrderID))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename, tblEmployee.Surname
HAVING
(((Count(tblOrderskill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=" & Forms!frmtest!txtOrderID)));

and query 2

SELECT DISTINCT tblEmployeeAvailability.EmployeeID
FROM tblEmployeeAvailability
WHERE (((tblEmployeeAvailability.Duration)>=Forms!frmtest2!txtOrderLength)
And
((tblEmployeeAvailability.EndDate)>=(Forms!frmtest2!txtOrderDate+Forms!frmtest2!txtOrderLength)));

i am looking into it, thought i would give you some feedback,

hope you can give me some ideas, i am working on it though.

speak to you soon.

Don,

The query I gave you a couple of posts ago works, I tried it. If it's
not working for you then you are not getting something right (a
table/field/form/control name?). Look closer.

Once you crack this, it will return all qualifying eployees' slots (nine
for order 1, as per your example data). Then joining this with the
previous query on EmployeeID in a new query, will give you qualifying
slots for qualifying employees only.

Nikos

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

:

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

DonMoody wrote:

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]))


DonMoody wrote:


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 ?

 

[Guest]

Another question, is there no way of doing this query without the forms and
bring all information up together. for example, query1 is for expertise, this
only brings up the employees who have the expertise in the order in the
orderid box, can this not be carrioed out in the query mode but with results
for all the orderids together.

sorry if i am asking a stupid question but if this can be achieved then i
can create the form that i want.

i have put both queries together and put the employee id in a combo box, so
a order id is selected and the combo box brings the result but to do this
again i have to go into design mode of the form, select an other id and then
click on the combo box or else it just projects the same results all the time.

Don,

The query I gave you a couple of posts ago works, I tried it. If it's
not working for you then you are not getting something right (a
table/field/form/control name?). Look closer.

Once you crack this, it will return all qualifying eployees' slots (nine
for order 1, as per your example data). Then joining this with the
previous query on EmployeeID in a new query, will give you qualifying
slots for qualifying employees only.

Nikos

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

:

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

DonMoody wrote:

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]))


DonMoody wrote:


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,

Once you have both queries saved, start making a new query in design
view, add both saved queries and join them on EmployeeID. This new query
will return all skilled and available employees for a given order.

HTH,
Nikos

Hi Nikos,

this query works just fine but now i am confused on how i am going to put
two of the queries that you gave together becuase i initally thought that the
queries would not be on the forms.

Query 1

SELECT tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename, tblEmployee.Surname
FROM tblEmployee INNER JOIN (tblEmployeeskill INNER JOIN tblOrderskill ON
tblEmployeeskill.SkillID=tblOrderskill.SkillID) ON
tblEmployee.EmployeeID=tblEmployeeskill.EmployeeID
WHERE (((tblOrderskill.OrderID)=Forms!frmtest!txtOrderID))
GROUP BY tblEmployeeskill.EmployeeID, tblOrderskill.OrderID,
tblEmployee.Forename, tblEmployee.Surname
HAVING
(((Count(tblOrderskill.SkillID))=DCount("[SkillID]","tblOrderSkill","[OrderID]=" & Forms!frmtest!txtOrderID)));

and query 2

SELECT DISTINCT tblEmployeeAvailability.EmployeeID
FROM tblEmployeeAvailability
WHERE (((tblEmployeeAvailability.Duration)>=Forms!frmtest2!txtOrderLength)
And
((tblEmployeeAvailability.EndDate)>=(Forms!frmtest2!txtOrderDate+Forms!frmtest2!txtOrderLength)));

i am looking into it, thought i would give you some feedback,

hope you can give me some ideas, i am working on it though.

speak to you soon.


:

Don,

The query I gave you a couple of posts ago works, I tried it. If it's
not working for you then you are not getting something right (a
table/field/form/control name?). Look closer.

Once you crack this, it will return all qualifying eployees' slots (nine
for order 1, as per your example data). Then joining this with the
previous query on EmployeeID in a new query, will give you qualifying
slots for qualifying employees only.

Nikos

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

:



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

DonMoody wrote:


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]))


DonMoody wrote:



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,

I'm afraid you have to do this one order at a time, because of the
DCount function; the whole trick is counting the skills required by job
vs. those of the employee, to make sure the employee has them all, so if
you don't specify an order, then which order's required skills are you
counting?
More bad news for you: this is not as straight-forward as you would like
it; for each orderm there may be several employees qualified to carry it
out, possibly at different times; how do you allocate orders to
employees under the circumstances? It looks to me like this would have
to be an iterative process; i.e. take one order at a time, select an
employee and a time slot out of the available alternatives, then mark
that employee and the time slot unavailable and continue with the next
order. I don't want to disappoint you, but this is serious programming!
More over, I don't have experience with this kind of algorithms, so when
you get there I'll ask you to start a new thread, to get help from
someone more experienced than myself on the subject.

Nikos

Another question, is there no way of doing this query without the forms and
bring all information up together. for example, query1 is for expertise, this
only brings up the employees who have the expertise in the order in the
orderid box, can this not be carrioed out in the query mode but with results
for all the orderids together.

sorry if i am asking a stupid question but if this can be achieved then i
can create the form that i want.

i have put both queries together and put the employee id in a combo box, so
a order id is selected and the combo box brings the result but to do this
again i have to go into design mode of the form, select an other id and then
click on the combo box or else it just projects the same results all the time.

:

Don,

The query I gave you a couple of posts ago works, I tried it. If it's
not working for you then you are not getting something right (a
table/field/form/control name?). Look closer.

Once you crack this, it will return all qualifying eployees' slots (nine
for order 1, as per your example data). Then joining this with the
previous query on EmployeeID in a new query, will give you qualifying
slots for qualifying employees only.

Nikos

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

:



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

DonMoody wrote:


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]))


DonMoody wrote:



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 ?

 

[Guest]

Hi Nikos,

you have helped me enough already, so i will never be disappointed.

i got the query working and now i need to put it on to a form, i understand
what you are trying to say and yes i am going to do it one order at a time.

my intention is to select an outstanding order (which is a query becuase
status is a field in the order table set to default outstanding), then i want
the form to display the employee details and the available dates for that
employee, i would like to select a date and click a button. i would like to
the button to change the order status to inprogress and delete the
availability from the employeeavailablity table and insert it into the
allocation table.

i thought this may be the easiest way to do it ?

what do you think ?

Don,

I'm afraid you have to do this one order at a time, because of the
DCount function; the whole trick is counting the skills required by job
vs. those of the employee, to make sure the employee has them all, so if
you don't specify an order, then which order's required skills are you
counting?
More bad news for you: this is not as straight-forward as you would like
it; for each orderm there may be several employees qualified to carry it
out, possibly at different times; how do you allocate orders to
employees under the circumstances? It looks to me like this would have
to be an iterative process; i.e. take one order at a time, select an
employee and a time slot out of the available alternatives, then mark
that employee and the time slot unavailable and continue with the next
order. I don't want to disappoint you, but this is serious programming!
More over, I don't have experience with this kind of algorithms, so when
you get there I'll ask you to start a new thread, to get help from
someone more experienced than myself on the subject.

Nikos

Another question, is there no way of doing this query without the forms and
bring all information up together. for example, query1 is for expertise, this
only brings up the employees who have the expertise in the order in the
orderid box, can this not be carrioed out in the query mode but with results
for all the orderids together.

sorry if i am asking a stupid question but if this can be achieved then i
can create the form that i want.

i have put both queries together and put the employee id in a combo box, so
a order id is selected and the combo box brings the result but to do this
again i have to go into design mode of the form, select an other id and then
click on the combo box or else it just projects the same results all the time.

:

Don,

The query I gave you a couple of posts ago works, I tried it. If it's
not working for you then you are not getting something right (a
table/field/form/control name?). Look closer.

Once you crack this, it will return all qualifying eployees' slots (nine
for order 1, as per your example data). Then joining this with the
previous query on EmployeeID in a new query, will give you qualifying
slots for qualifying employees only.

Nikos

DonMoody wrote:

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

:



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

DonMoody wrote:


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]))


DonMoody wrote:



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 ?

 

[Guest]

Hi Nikos,

i have a problem, the query i have put together is causing problems.

it is only retrieving information for the employee ids in the expertise
query even though i have joined the availablity and the expertise querie.

i dont understand why it is doing this, i have put employeeid=employeeid but
it is retrieving too much info.

Don,

I'm afraid you have to do this one order at a time, because of the
DCount function; the whole trick is counting the skills required by job
vs. those of the employee, to make sure the employee has them all, so if
you don't specify an order, then which order's required skills are you
counting?
More bad news for you: this is not as straight-forward as you would like
it; for each orderm there may be several employees qualified to carry it
out, possibly at different times; how do you allocate orders to
employees under the circumstances? It looks to me like this would have
to be an iterative process; i.e. take one order at a time, select an
employee and a time slot out of the available alternatives, then mark
that employee and the time slot unavailable and continue with the next
order. I don't want to disappoint you, but this is serious programming!
More over, I don't have experience with this kind of algorithms, so when
you get there I'll ask you to start a new thread, to get help from
someone more experienced than myself on the subject.

Nikos

Another question, is there no way of doing this query without the forms and
bring all information up together. for example, query1 is for expertise, this
only brings up the employees who have the expertise in the order in the
orderid box, can this not be carrioed out in the query mode but with results
for all the orderids together.

sorry if i am asking a stupid question but if this can be achieved then i
can create the form that i want.

i have put both queries together and put the employee id in a combo box, so
a order id is selected and the combo box brings the result but to do this
again i have to go into design mode of the form, select an other id and then
click on the combo box or else it just projects the same results all the time.

:

Don,

The query I gave you a couple of posts ago works, I tried it. If it's
not working for you then you are not getting something right (a
table/field/form/control name?). Look closer.

Once you crack this, it will return all qualifying eployees' slots (nine
for order 1, as per your example data). Then joining this with the
previous query on EmployeeID in a new query, will give you qualifying
slots for qualifying employees only.

Nikos

DonMoody wrote:

Thanks Nikos,

sound advice

i will keep that in mind.

any ideas for this query ?

i am not getting anywhere with this, i am lost !

:



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

DonMoody wrote:


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]))


DonMoody wrote:



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 ?