Select columns based on results 1

[zandsc1]

This may not be possible at all, or I may just be overlooking something obvious. Here's my scenario: I have two tables; one table contains all of the employees, and other table tracks their training levels for different jobs. Examples:

tblEmployees:

ID | Employee | Job Title |
1 | John | Assembler |
2 | Jane | Supervisor |
3 | George | Engineer |

tblTraining:

ID | Job | Step | John | Jane | George |
1 | A1 | 1 | 0 | 4 | 0 |
2 | A1 | 2 | 2 | 4 | 0 |
3 | B2 | 1 | 3 | 0 | 4 |
4 | B2 | 2 | 0 | 0 | 4 |

Now, if I have a job and step (say, job B2, step 2), I want to find all the employees that are level 4. How can I structure a query so it only selects columns where (1) the column title is included in Employee field of tblEmployees (I don't want to be checking the 'Step' field for a training level) and (2) the training level of the person is 4?

 

[PHV]

First, normalize your data (ie tblTraining).

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zandsc1]

Thanks PHV... I should have read that article years ago.