By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,289 Members | 1,699 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,289 IT Pros & Developers. It's quick & easy.

Select statement Help

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"N. Graves" <ng*****@REMOVEyahoo.com> wrote in message
news:e2********************************@4ax.com...
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

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)

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.