Competency Database

[gawright]

Hi All,


I am trying to create a database to keep records of staff competencies i.e. a record of who is competent to perform tasks. I have a basic understanding of databases but have not done much development.

Here's what I have so far:

A table of staff details (Primary Key, Surname, First Name)
A table of tasks (Primary Key, Task Description)
A table of competency levels (Primary Key, Level Code, Level Description): For example basic, advanced, in training

A table called staff training records, with the columns: Staff Name (lookup from staff table), Task (lookup from task table), competency level (lookup from competency level table), Date assessed, Assessor (lookup from staff table), Comments (free text)

What I want to produce is a form for data input that allows me to select a member of staff and then lists all the tasks in the task table, with the competency level, date assessed, comments. I cannot work out how to produce such a form so that it will show ALL tasks even if I have not yet defined a competency level for that task for that member of staff. I want to use this form to populate the staff competency table for each member of staff and update as things change. Can anyone point me in the right direction?

When we have a new member of staff, I want their training record to be automatically populated with all the tasks in the task table but for the competency level to be blank by default.

Also, when I use a lookup table to select a member of staff, the drop down box contains the surname and first name. Once selected, I only see the surname. How can I show the full name of the member of staff on the form

Thanks

[June7]

I'll start with the last. I assume you have a StaffID field as primary/foreign key. Construct the combobox RowSource with query like:

SELECT StaffID, Lastname & ", " & Firstname As StaffName FROM StaffTable ORDER BY Lastname, Firstname;

Other combobox properties:
BoundColumn 1
ColumnCount 2
ColumnWidths 0";2.0"


Automatically populating training record can be accomplished by several methods. One is an INSERT SELECT sql action. This sql can be an Access query object or a statement in VBA. Like:

CurrentDb.Execute "INSERT INTO tablename(StaffID, TaskID) SELECT " & Me.StaffID & " AS ID, TaskID FROM TaskTable;"

The real trick is figuring out what event to put the code in, perhaps a button click, and also making sure duplication is not allowed.

[gawright]

Thank you for your very useful reply. I think I understand your suggestion and will give it a try. One thing I had hoped to be able to do was that if a new task is added to the task table, this would be automatically added to the training record of every member of staff but with the default competency of N (Not competent). Is this achievable?

[June7]

Certainly, just requires more code to run an INSERT SELECT sql action. As always, the real trick is figuring out what event to put code in.

[gawright]

Thanks. I'm starting to think a big excel table may be easier for this task!

[June7]

Well, yes, I guess if you just want to manually select and copy a row down the sheet. But what about report output?