Staff training, query list of required courses, taken and not yet taken (1 Viewer)

[kedunc]

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

Fred Smith
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.
Keith

 

[John Big Booty]

Firstly welcome to the forum.

Having had a browse through your DB, I notice that you have partially implemented a naming protocol for your DB objects, however to be of benefit you need to be consistent in using it.

I also notice that you are using Table Level Look ups you may wish to reconsider their use after you have read the link.

You might also benefit from reading up on normalisation.

Having done a little reading you may want to re-think your DB design. Having done that, progress toward you ultimate goal should become easier.

 

[Rik_StHelens]

What you could do is use the following as a criteria for staff name (or the unique identifier NI?)

Like "*" & [Staff NI (Leave Blank For All)] & "*"

And then if you output that to a report, and group the report by Staff NI (or name as you wish) it should list all training courses for that staff member, then list the next staff member.

Someone on this forum helped me to do something similar to run off all our customer invoices.

 

[Rik_StHelens]

Try report grouping

I've attached an example to your db file.

Just run the "Courses" report and see if its more of what you're looking for

hope this helps.

 

[kedunc]

Firstly welcome to the forum.

Having had a browse through your DB, I notice that you have partially implemented a naming protocol for your DB objects, however to be of benefit you need to be consistent in using it.

I also notice that you are using Table Level Look ups you may wish to reconsider their use after you have read the link.

You might also benefit from reading up on normalisation.

Having done a little reading you may want to re-think your DB design. Having done that, progress toward you ultimate goal should become easier.

Thanks John , have looked over the links and am digesting what I can understand. Things is what is a simple alternative to table level look ups. If there is a simple list of choices for a field, a lookup table seems to work well, though I take the point that thi smay not work well outside of Access.
In my own DB I need some way to list the managers of staff who can then be input in the staff records according to who each individual is managed by. The managers themselves are in the staff DB as well so it is helpful/essential to make sure that their reference ties/relates them back to additional details about themselves which are held in the same fields as all other staff.
Probably haven't explained that very well !

Am addressing the naming convention in the main DB that I extracted my sample.
Thanks for your input
Keith

 

[kedunc]

Try report grouping

I've attached an example to your db file.

Just run the "Courses" report and see if its more of what you're looking for

hope this helps.

Rik , thanks for your time. I may not have explained myself well enough in my initial post but your reply whilst neatly laying out training that staff 'have' done from the required list doesn't list other training on teh required list that people need to do but 'haven't' yet done. That's my problem, proving/reporting in detail required courses taht individuals haven't done.
Thanks
Keith