Can't wrap my head around it...

[Zippersabrat]

I am having some issues with a database...I realize that is kinda of dumb...if I didn't have any issues why would I be here??

Anyway, I am working on a database to track staff training. I have a list of skills (with a trainer level code for each) and a list of trainers (also with trainer level codes). The idea is that a trainer can train on a skill equal to or less than their level (e.g. A level 4 trainer can train on any skill level 1-4). My issue is how in the world do I set up a table showing the trained employee, the skill trained, the date, and the trainer while also controlling which employees can be put in the trainer field.

I will not bore anyone with the things I have tried...please help!

 

[PNGBill]

Haven't got the "In a Nutshell Answer" but first I would imagine you would have more then 1 table.

TblStaff would include a field with Current Skill Level along with name address etc. - Skill level could be calculated at the time - refer TblTrans - selecting last course passed.

TblTrainer would include a field with SkillLevel (Assuming there is only one stream of skills ie not cookery and welding levels but just levels)
If more complicated then maybe a TblSkillLevel so each trainer can have a record entry regarding details of skills they attained - a highly qualified trainer would have a lot of entries.

TblCourse will hold CourseID, Date, Description(level, subject etc), Venue.

TblTrans Would hold a record of each Instance of Training. ie combination of CourseID, StaffID, Result/Mark Notes and SkillLevelGained.

Queries will allow you to produce reports on each Course with Trainer Name and Attendees list c/w pass marks and any levels attained by attendees etc.

When a course is complete you could have a report/form indicating where Staff have gained an improvement in their skills and allow you to change their level. Automatically this could be done by query that compared current skill to new skill and if current skill is higher then update the record - or, as above just leave in TblTrans and query result when req'd.

How to setup the proposed class....

Trainers have their skills set and I guess manually updated as they improve.

Staff would need a flag of some sort to show they are ready for another course - could be 3 months after passing grade 3 they are eligible for a grade 4 course??
A query would select all eligible staff - have passed a course 3 months or more ago and not attended any courses in last 2 months. Could have max number of attempts at course 3 (eg) and not able to attend again.
You would then have a list of all available course attendees indicating the next course for them ie last course level2 4 months ago, next course level 3

You would allocate the available trainers from the top down. ie, most qualified trainer (level 6) would take top 10 level 6 attendees.

gets complicated when you have max number for class. this could be covered in the Staff selection ie DateDiff will give you the num of days since last course so top 10 or 15 staff would be selected for level 6 course and the balance will wait for next selection.
Next time around, anyone that failed this course would be at the bottom of the list for the next selection as their days number would be low.

Depending on your level of vba you may have a few temp tables in the sorting and filtering process to get the list of attendees for each course and then to allocate trainers.

Trust above is of assistance.

 

[Zippersabrat]

PNGBill-

Sorry to take so long to get back to you but your help was invaluable!

Thanks!

 

[NigelShaw]

So why does valuble and invaluble mean the same thing? It's like flammable and inflammable! Both mean exactly the same soluble and insoluble..... The list goes on lol


Nidge

 

[LPurvis]

Surely soluble and insoluble are antonyms.

I agree though, the tendency for redundant words in English is peculiar - but fun. (I wonder how illogical it must seem to those learning it for the first time).

Nothing to say on a technical front on this thread...
Hmm does that make me redundant?