Unanswered: Staff training, query list of required courses, taken and not yet taken.
Hello, please can someone point me in the right direction, have tried and failed on this and
googled galore to no avail.
I have a fairly straightforward Staff Database that includes a record of staff training.There are a variety of training courses available to staff some of which are mandatory, some advised etc.
To record training I have a simple table 'Courses' which is linked to the 'Staff Details' table by a staff id/code 'NI' 0n 'Staff Details' table and 'Nat Ins'on Courses table.
To enter training one can simply open the Staff Details table and expand the related Couses table against their name ,to enter the course name 'Course I' 'Date Completed' etc.
The course name field 'Course I' which is a lookup field from the related 'CourseList' is a simple list of available courses, in this same 'CourseList' table there are additional fields that allow me to indicate by a code if a course is mandatory, advised etc. this coding varies according to job title.
So far so good, I can easily query to get a list of staff and what training they have done. I can query and get a list of which staff have done a particular category or type of training. I can query to get a list of all staff with all their training and where it exists, a code that indicates which of the courses that people have taken are mandatory etc.
I have worked out how to query on any one course in such a way that I get a list of all staff with details of the course name and date for people who have been on the specific course ( say
First Aid ) as well all the other staff in the database who haven't been on that course. Course name and date completed fields for those people are blank but because it was a query for just one course ( First Aid) I know that means those people haven't yet done First Aid training.
What I want to do is query the database to get a list/report that shows all staff with all the training that they have done AND also includes the names of courses against each person that meet the criteria of mandatory/and/or advised etc ( depending on their job title) but which they haven't yet done.
So for a given person in the database the query might return
Course Name Course Code Date Completed
Basic IT 22/11/2009
Equality RAI 11/05/2008
First Aid RBM Empty/Blank
Fred has done two courses, one of which was a required course one of which wasn't but he hasn't yet done a First Aid course which is a required one.
Hope I've explained that ok , I suspect/hope that the solution isn't too complex.
The concept is one of getting a list of 'events required and not required that have happened' and 'required events that haven't yet happened but need to happen'.
My own steps along the way to deal with this are in the following queries qryRequiredCourses qryRequiredCoursesTaken qryRequiredCoursesTakenWithNames
Am a novice user, can cope with a few lines of SQL but not too much.
Thanks for any help , have uploaded a cut down version of my db with some fake staff if that helps.