Re: Employee Emergency
From: Don (Don_at_discussions.microsoft.com)
Date: 02/22/05
- Next message: Jeff Boyce: "Re: ambiguous outer joins"
- Previous message: Don: "Re: Employee Emergency"
- In reply to: Nikos Yannacopoulos: "Re: Employee Emergency"
- Next in thread: Don: "Re: Employee Emergency"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 22 Feb 2005 03:23:04 -0800
Hello Nikos,
sorry for the delay in responding, i couldnt logon yesterday but this works
just fine and thanks for the explanation.
I dont want to be a burden but i have another problem but this time it has
got to do with the availabilty.
"Nikos Yannacopoulos" wrote:
> 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
> >
> > "Nikos Yannacopoulos" wrote:
> >
> >
> >>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.
> >>>
> >>>
> >>>"Nikos Yannacopoulos" wrote:
> >>>
> >>>
> >>>
> >>>>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 ?
> >>>>
>
- Next message: Jeff Boyce: "Re: ambiguous outer joins"
- Previous message: Don: "Re: Employee Emergency"
- In reply to: Nikos Yannacopoulos: "Re: Employee Emergency"
- Next in thread: Don: "Re: Employee Emergency"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
