G
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 !
sound advice
i will keep that in mind.
any ideas for this query ?
i am not getting anywhere with this, i am lost !
Nikos Yannacopoulos said: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 ?