Employee Training Database - Help Required

[meeky]

I have been trying to create a database to hold training assessment results with limited MS Access knowledge.
I have been able to design queries to show if a member of staff is still with our company and if a version of an Assessment is the latest version (Active).

The problem I have is with the design of the last table, Table 5 – Training Course Results (See table structure below)
I will eventually be creating a Form from this table to enter the assessment results of each employee.

I’m struggling to find a way of the table pulling the “Pass Mark” from the Training Course Details Table and then showing if the employee has “Passed” or “Failed” the assessment based on the score the employee has achieved in relation to the “Pass Mark” of the training course.

Should these fields be ‘Calculated’ or ‘Lookup’. I have tried variations of both but each gave errors.

Table 1 – Assessment Groups
Assessment Group (Primary Key)
Short Code

Table 2 - Departments
Department Names

Table 3 - Employee Details
Employee ID (Primary Key)
First Name
Surname
Full Name (Calculated from First Name + Surname)
Department Name
Hire Date
Termination Date (if applicable)
Active member of staff (Calculated: Yes if no Termination Date has been entered)

Table 4 – Training Course Details
Assessment Group
Course Name
Course Code (Primary Key)
Date Course Created
Pass Mark
High Score
Course Active (Yes/No)


Table 5 – Training Course Results
Auto Numbered Key (Primary Key)
Assessment Group
Course Name
Course Code
Employment ID
Full Name
Date of Assessment
Score
Assessment Passed
Department at time of Assessment
Pass Mark


Any assistance would be much appreciated.

Thanks,

Scott.

 

[jdraw]

Welcome Meeky.

A couple of points to consider since this is your first post.

Access requires special syntax when processing field and object names that have embedded spaces.
So, adopt a naming convention that uses alpha names with no spaces to avoid frustrating syntax errors. The underscore character "_" is accepted by Access. eg FirstName or First_Name

Also, I suggest you drop the table1, table2... from tables. It may help you as you begin, but it is often better to name the table based on the Entity/Subject/Concept involved. eg Employee or Department

Avoid redundancy in tables -- eg you do not need FullName since you have FirstName and Surname
You can create the FullName via a query if and when needed.

Avoid lookups at the table field level. Use lookup tables in forms. see this

Since Department Names could change over time, I'd suggest you use a code/identifier to uniquely identify each Department.

It may just be me, but I think Training Course Results, may be named EmployeeTrainingAssessment to convey its purpose. You can name the table as you wish.

As you layout your tables and identify how they are related to one another, you will be adding Foreign Key fields.

A useful tutorial that will help you with your database design is here. You have to work through it, but you will learn a process that can be used with any database.

Good luck with your project.

 

[pbaldy]

FYI, both posts moderated, now approved.

 

[meeky]

Thanks for the assistance Jdraw... and the additional reading material