Need simpler way to do this for a search (SQL)

[rnc110]

I have the attached sql that works perfect(at the bottom of the page). However I thought that I would be comparing about 5 fields but it turns out I have to verify around 100 or so fields. Here's what the code does. I have a table tblEmployee with the fields (EmpId_Num,last, first,position_num) the position_num is the field that links to the table tblPosition (Position_Num, Position_Title, training_Needed1, Training_Needed2, ..... , training_Needed100, table tbleAvailclasses (Course_Num, Description) and table tblTrainingCompleted (Training_Number[primary key,autonumber], EmpID_Num, Course_Num, Date)

Here is what is happening: The employee has a position in the company (position_num) for positions there is different training courses required for different positions. Some range from 1 course where others require 100 courses. I have this SQL query (attached below that worked when i only thought there were a max of about 10 courses required. This references the Employee table to see the position then looks at the position table and starts to see if that individual has the completed class from the TrainingCompleted table. If not that class is returned.

**************CODE CODE CODE***************88
SELECT tblEmployee.EmpID_Num, tblEmployee.EmpLName, tblEmployee.EmpFName, tblEmployee.Position_Num, tblAvailClasses.Course_Num, tblAvailClasses.Description AS Description
FROM (tblPosition INNER JOIN tblAvailClasses ON tblPosition.Training_Needed1 = tblAvailClasses.Course_Num) INNER JOIN tblEmployee ON tblPosition.Position_Num = tblEmployee.Position_Num
WHERE (((Exists (Select * From tblTrainingCompleted
Where EmpID_Num=tblEmployee.EmpID_Num
And Course_Num= tblPosition.Training_Needed1))=False));

Union

SELECT tblEmployee.EmpID_Num, tblEmployee.EmpLName, tblEmployee.EmpFName, tblEmployee.Position_Num, tblAvailClasses.Course_Num, tblAvailClasses.Description AS Description
FROM (tblPosition INNER JOIN tblAvailClasses ON tblPosition.Training_Needed2 = tblAvailClasses.Course_Num) INNER JOIN tblEmployee ON tblPosition.Position_Num = tblEmployee.Position_Num
WHERE (((Exists (Select * From tblTrainingCompleted
Where EmpID_Num=tblEmployee.EmpID_Num
And Course_Num= tblPosition.Training_Needed2))=False));


See I was going to repeat this code and keep changing the Training needed like a did between segments (1,2 ... x) but this makes the query give an error message when I union together the 50th statement and I need to look at atleast 100. (QUERY IS TO COMPLEX error message. What should I do. Can I make this into a loop. I am not that good with SQL

 

[JerryDennison]

Your problem is repeating groups. You need to seperate the training needed to it's own table linked back to the employee. NOTE, this does not mean have a table where you simply repeat the groups in it. Use seperate RECORDS for each training need, not seperate fields.