"N. Graves" <ngraves@REMOVEyahoo.com> wrote in message
news:e254c1lm1ncti073381k8hghm8g45lbjq3@4ax.com...[color=blue]
>I have a table of education records.
>
> ex:
> recid Empl Course Title Complete
> 1 123 t123a 123 class A Y
> 2 123 t123b 123 class B Y
> 3 234 t123a 123 class A Y
> 4 345 t123b 123 class B Y
> etc
>
> I need to see a report of all the employees that if Complete is 'Y'
> for both Courses t123a and t123b are in the table. Like recid 1 and 2
> this employee 123 is qualified on 123's... say that ten time fast;-)
>
> I'm hoping that someone with a more knowledge than me can come up with
> a Select query that would just display
>
> Empl
> 123
>
> I'm also going to setup a crossover or matrix table that would say
> that "you are qualified on 123 if you have completed training on
> Courses t123a and t123b. This table will have may more 123's than 1.
> Not quite sure what this table will be exactly but with you help may I
> will.
>
> thanks for you help and reading my question!
>
> N. Graves[/color]
You might think this question would be easy, after all your question is
basically similar to "show me all people who can sing and dance". However,
not only is the SQL a bit long but the results can be slow. For this
particular question, you could simply try the following query once you have
modified the tabl/field names:
SELECT tblEmployee.* FROM tblEmployee
WHERE
(tblEmployee.EmpID IN
(SELECT tblEducation.Empl
FROM tblEducation WHERE tblEducation.Course="t123a"
AND tblEducation.Complete="Y"))
AND
(tblEmployee.EmpID IN
(SELECT tblEducation.Empl
FROM tblEducation WHERE tblEducation.Course="t123b"
AND tblEducation.Complete="Y"))
There are other approaches to this problem where you can use a 'helper
table' to keep a list of all the skills required and an example of this is
at
http://www.mvps.org/access/queries/qry0016.htm I don't think it is that
well explained and would need to modified in your case since you have to
match on two fields (course and complete) but the principal is the same.
As to your future plans, they do complicate things, but it might model the
real-life situation a bit better. So you could have tblCourse,
tblCoursePart, tblEmployee and tblEducation which would say which employee
took which course part (and you know how many parts there are to each course
by counting the appropriate records in the tblCoursePart)
