Interesting Query Problem

[Lockrin]

I am going to make this as simple as I can
I have a Position table that has a one to many relationship to a Courses table. You need to have certain courses to obtain a "position"
I then have an employee table with a one to many relationship to the same courses table. I need to create a query that will show ONLY the employees that have completed ALL of the courses that the particular position calls for.

I think I have explained this Ok.

Please Help! I cannot figure this one out.

[Lockrin]

This query would tell me what employees have the necessary training to be qualified for whatever position. I can get the query to give me the results for all employees who have ANY of the courses but not ALL of the courses.

[NTC]

ALL of the courses that the particular position calls for: is yet in need of definition.

The best analogy I can give you is college where courses are assigned 'credits' and one must have X credits toward a 'major' to receive a degree. You may take programming courses but not enough of them to satisfy the major's minimum credit requirements. If your major is programming and you take a course in basket weaving, you may get credits but they don't apply toward your major (not sure if relevant in your case).

So you need another field to your courses that is 'credits'. You need to define a 'poisition' in terms of credit count. And you need to determine courses that are valid for some positions but possibly not others (this complexity may not be an issue for you).

If you keep the college analogy in mind it should serve as a guide. Only when these other fields are established and the overall structure is there will you be ready to make queries.

[Lockrin]

NTC, thanks for the help; This analogy makes sense. to keep your analogy I do not need the complexity of 'credits'. I only need to have 'courses' completed to obtain a 'major'. I have a table where I determine what 'courses' are needed for each 'major' I just don't know how to query the results from this table along with the table that shows which 'students' have completed which 'courses' to give me only those students who have completed all of the 'courses' necessary for each 'major'.

I hope this makes sense, and thanks again for your help NTC

[weekend00]

Tables:
1 tmpPosition:
position courseid
abc 3
abc 4
abc 6
bbb 3
bbb 4
bbb 7

2 TmpEmp
emp courseid
fan 3
fan 4
fan 6
liu 3
liu 4
liu 7
liu 6
liu 1
men 1
men 3
men 4

Querys:
1 tmpCnt: SELECT tmpPosition.position, Count(tmpPosition.courseid) AS CountOfcourseid FROM tmpPosition GROUP BY tmpPosition.position;

2 Result: SELECT tmpEmp.emp, tmpPosition.position, Count(tmpEmp.courseid) AS CountOfcourseid1, tmpcnt.CountOfcourseid
FROM (tmpEmp INNER JOIN tmpPosition ON tmpEmp.courseid = tmpPosition.courseid) INNER JOIN tmpcnt ON tmpPosition.position = tmpcnt.position
GROUP BY tmpEmp.emp, tmpPosition.position, tmpcnt.CountOfcourseid
HAVING (((Count(tmpEmp.courseid))=[countofcourseid]));

Run query "Result" will give a list of employee with completed positions.

[ajetrumpet]

I am going to make this as simple as I can
I have a Position table that has a one to many relationship to a Courses table. You need to have certain courses to obtain a "position"
I then have an employee table with a one to many relationship to the same courses table. I need to create a query that will show ONLY the employees that have completed ALL of the courses that the particular position calls for.

I think I have explained this Ok.

Please Help! I cannot figure this one out.

you might want to post your table structure of the two

[Lockrin]

See attachment of relationships

TSession - Training session for Competencies (Time and date that the competency was trained)
TSessionDetails - Details Employees in training session (equivalent to 'students')
tblCompetencies - equivalent to 'courses'
tblPreReq - This is where I decide what competencies are required for a Position
tblPosition - equivalent to 'major'

I hope this helps me explain what I'm trying to accomplish

[weekend00]

Did you try my 2 queries, I think I understood you well and te queries should work well.