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
+ 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
1 Reply

 P: n/a "N. Graves" 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.