dynamic query? (1 Viewer)

[kkpen]

Not sure if its a dynamic query i need or not. I need to track training for personnel. Each person belongs to a different department and each department has different training requirements. I have the personnel table, a training list table that list classes required, a table that has personnel positions (each department may have certain people in a position that require specialized training that no one else needs.) Master training table that hold the personnel id# and all the classes needed by the whole company. Some fields in the master table will be blank due to not all personnel needing them.

I have made a junction table that holds the department, training class required, and position that requires that training.

What i am trying to do is make a query or vba code that would allow me to mark the required training fields in the master training table based off the junction table for each department and position.

the junction table has: dept, training, personnel req.
example: admin, printer training, all
example: admin, supervisor training, supervisors

I want to add/update the required training using the personnel table and the junction table. (maybe not the best way?)

So all personnel in the admin department require printer training, and the supervisors also require supervisor training. I want to be able to cycle through the personnel table looking for everyone in the admin dept and mark printer training in the master training table as required and if they are a supervisor I want to mark supervisor training field.

Clear as mud right? The reason its dynamic is, next year the required training could change. So the junction table could change. If i hard code to the junction fields each year i would have to change the code.

If this is not the right direction, what is the best direction.

Thanks for any help!!!

 

[spikepl]

What is your specific issue? I happen to be working on a training/SOP db, so just ask. But you need to ask something specific. "How do I build a house" cuts no ice...

Oh yeah, stories are really hard to assimilate. Show tables, relations, pictures ...

 

[spikepl]

As a hint: training requirement is bound to a position (which in turn is filled by one or more persons). Training history is bound to a person. A person possesses a training history, no matter which positions the person filled.

 

[kkpen]

I have attached a pic of the tables.

tblTrainList = is the master list of all training required.
tbltrainReqPerson = list of persons (duty positions) that req. training
tbltrainReq = is a junction table that list section, training req id, and person requiring training.
tblTrainingNew = is a normalize table that has brn(person), training req, date trained, and is it current.

I want to make a form were I can take tbltrainingNew and be able to update the dates people had their training on. I can get the info in a cross tab query but it can not be updated. I want to see the persons name and the training dates after it. it would look like:

Name "add user" "delete user" "update system" .......
--------------------------------------------------------------
joe 10/10/15 11/15/12 1/12/15
sam 1/15/15 5/8/14 4/14/15

The query data will change based off what section is selected. Also the training req thats listed across the top could change.

I know if I make a field in tbltrainingnew for each training req and then code a form to show them based off the tbl it will work. The drawback is everytime the training requirements changed I would have to change the table and recode the form manually. Which wouldn't be bad except the program is located in several different locations and some are stand alones and others go to a frontend/backend versions.


hope this helped some

 

[jdraw]

Further to spike's comment, I'm attaching a jpg that depicts the Employee, Position, TrainingRelatedTo Position and Training that Employee has.

Good luck.