Multiple Criteria on One Field, query/report from form

[sword856]

Hello all, I have a tough problem I need to address.
I need to generate a training report for a given employee. I have a
list of possible positions. Each position has a series of mandatory
training classes necessary to be certified in that position.
Employees have taken classes for position certification and classes
that do not contribute to certification. I need to separate mandatory
training and Other training on my report, which is based off a query.
I have a form with a combo box to pick an employee's name and a combo
box to pick a position. Once the user picks these two things and
clicks "Get Report", I have a report that has 2 sections: Mandatory
and Other training. Right now, the query is set up so that I have to
type in each class name separated by OR in the criteria line to get
what I want. I would like this to be entered when the user picks a
position. I would also need to know where to store the list of
classes for each position, i.e. in a table, in multiple tables, or
what not.
Keep in mind the following:
There are classes all positions require.
There are classes a few positions require.
There are classes only one position requires.

Thanks for any help anyone might have,
George

 

[John Spencer]

Sounds to me as if you need three tables
Classes
Positions
and ClassesForPosition
The last table would have one record for each class that is required for a
position - basically the primary key from classes and the primary key from
positions.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[sword856]

Sounds to me as if you need three tables
Classes
Positions
and ClassesForPosition
The last table would have one record for each class that is required for a
position - basically the primary key from classes and the primary key from
positions.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.







- Show quoted text -

Thanks, John for your quick reply!
I have Classes and Position tables already, and making the third
wouldn't be hard, but I don't know what to do with those tables. I
can't figure out how to set the query parameters to the list of
classes in the tables.
I guess some more explanation would be in order:
I have a Personnel table and a TrainingHistory table as well.
TrainingHistory is the junction between Personnel and Classes. The
query needs to search TrainingHistory for the classes. Also, there
are classes that are not mandatory for any position.