New employee database with training table(s) - not a very experienced user needs help please!

[RachaelB]

Hello all and thank you for taking your time to read this and hopefully help me!
I am looking to create a new employee database (using Access 2007) which will store employee personal information, sickness, performance data and training.
I've got the basic plan of having a main employee info table (with autonumber primary key EmpID) then other one-to-many tables for sickness and performance information.


The issue that I'm struggling with is training - I can create a linked table as per the sickness and performance tables no problem, but the issue is that all employees have to do certain mandatory training, some employees have to do role specific training, then all employees can do additional training.
So if I just do one training table, is there a way to add in all the mandatory and role specific to each person's record (with a date field to fill in once the training is done) and then have the option to add additional training too?
Or would it be better to have 3 separate tables? I wondered whether I could maybe use an append query to add the training to each record but that seems like a long way round
Any help or advice would be much appreciated - This is the only bit I can't figure out!
Thank you again
Rachael

[rpeare]

I would have something like this:

tblClass
C_ID  C_Description ---> other class specific fields like how often it needs to be renewed
1     Class 1
2     Class 2

tblPosition
P_ID  P_Description ---> other position (employee position) related fields
1     Position A
2     Position B

tblPositionClass
PC_ID  P_ID  C_ID ---> other position & class related fields
1      1     2
2      2     1
3      2     2
this is a junction table of tblClass and tblPosition that helps define which classes are required for which employee positions

tblEmployee
E_ID  E_FirstName  E_LastName  E_Number ---> other employee related fields
1     Sam          Yosemite    10001

tblEmployeePosition
EP_ID  E_ID  P_ID  E_StartDate  E_EndDate ----> other employee postition related fields
1      1     2     1/1/2017

this is a junction table between employees and positions to handle a person holding multiple positions within the company over time
the position foreign key will also allow you to determine which classes are required
In this case Sam Yosemite holds the position Position A, from there you can determine that he's required to take Class A and Class B as defined in the table tblPositionClass

[RachaelB]

Thank you rpeare, I will have a go at setting it all up as a test database :-)
I don't think I will need the second junction table as if an employee changes role then that would just be changed (we don't need to keep historical data of roles held; just the current active role). Unless we do need it???
Please can you offer a little bit more advice? Once I have set up all the tables and done the relationships, if I then create a form with a subform will I then be able to search for the employee and see at a glance what classes they have and have not attended? Thanks again for all your help and apologies if I am being a bit dim!

[rpeare]

Personally, when I build a database if there's the slightest chance of needing to view a history I plan the database around that. In your case it may be relevant to know the history of the person in your company (what positions they held), that's your decision not mine but I tend to err on the side of caution where data is concerned.

If you set up your data structure correctly then yes, you can look at your data any way you want, you just have to set up the appropriate queries/reports/forms. You are mentioning forms/subforms which I do not use (read about bound forms vs unbound forms), but there is no reason you can't build the utility you need into your database/user interface.

[orange]

RachelB,

I suggest you make a data model before getting too deep into Access physical database.
Since you are new to the forum, here are a few links related to Database and Data Models.

You might find that watching these free video tutorials by Dr.Daniel Soper will help with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model



Here is a jpg of some considerations for Employee and Training and Position.

[RachaelB]

Thank you both rpeare and orange (great pic by the way, cats are my favourite)
I think I am maybe too much of a novice to put this together how I want it (I used access a long long time ago in a job far far away) and now seem to have lost a lot of my skills!
I will have a look at the tutorials and see if it helps, I kind of understand your table structure but need to get my head around it a bit more!
And this is only a tiny part of the work I have to do so it's going to have to be a longer term project I think! Was hoping I could put something together quickly but it's turning out to be a much larger job than anticipated (you probably hear that a lot from novice users!)
thanks you for all your help so far, it's much appreciated!