Normalization of DB Tables (1 Viewer)

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
Let me demo the training form. If I am understanding things correctly it will be a much easier way to track training than Excel.
Now this is exactly what I'm looking for. Once a a 2-year period is done for staff llicensed to certain bldgs I can do away with all trainings from their log once their 2 yr has past. I've had to go into every individual staff to go through each log and delete accordingly. took so much time.
 

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
I did an import from the Excel sheet. It I may not be 100% based on some issues in the data.

There is a form to enter/edit a course and assign it the related credits. That is done once per course.
Then on the main form you choose from the list of course to assign it to an employee on the date given. All the credit hours are related "automatically". You can see this on the second tab. This is read only. You never have to log credit hours. If a course is required you can track which employees have taken all the required courses. No seperate table is needed. IMO this is a correct design, but it is not trivial to make the user interface for this. The credit hour tab requires a crosstab query and defined columns.
I did have some errors in my documentation. I really believe this reduce typo. I really like the way it looks so far.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:57
Joined
May 21, 2018
Messages
8,463
I will post an update this evening. I played with this some more.
 

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
I got a chance to really look over the trail db you created. Instead of having the course name as a drop down could we just to have a drop down with the main courses that we take regularly and be able to add our own that is not part of the drop down? Also, where would the total of all trainings hours be for each staff?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:57
Joined
May 21, 2018
Messages
8,463
I added courses of study and classes. In my mind someone can have multiple courses of study and for each course of study they can have multiple classes. This becomes a 1 to many to many. There is two subforms in that tab. You can add different classes to each course of study. I added waivered sites. So you add a waivered site.

Also, where would the total of all trainings hours be for each staff?
This could be a simple query and put into a subform. Then you can show the totals above the details for each staff member. I can demo that.

could we just to have a drop down with the main courses that we take regularly and be able to add our own that is not part of the drop down?
All courses need to get entered in the training table to track their credit hours. However that makes the pull down very long. There are lots of ways to get fancy to make it easier to find a course. One could be a find as you type combo. If you type "child" it will filter the list to any course with "child" in the name. You could add some buttons for "Mandatory", "Reoccuring", "This year", etc to filter the list further. My guess is you have a course and a bunch of people take it. In that case I would build another form that presents the data in a different way to make data entry faster. The Main form is a course and then you assign people to it. Because of the many to many relationship you can present data from both directions. Assign courses to a person or Assign people to a course.
 

Attachments

  • MajP_TrainingDB V4.accdb
    1.5 MB · Views: 137

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
I added courses of study and classes. In my mind someone can have multiple courses of study and for each course of study they can have multiple classes. This becomes a 1 to many to many. There is two subforms in that tab. You can add different classes to each course of study. I added waivered sites. So you add a waivered site.

This could be a simple query and put into a subform. Then you can show the totals above the details for each staff member. I can demo that.


All courses need to get entered in the training table to track their credit hours. However that makes the pull down very long. There are lots of ways to get fancy to make it easier to find a course. One could be a find as you type combo. If you type "child" it will filter the list to any course with "child" in the name. You could add some buttons for "Mandatory", "Reoccuring", "This year", etc to filter the list further. My guess is you have a course and a bunch of people take it. In that case I would build another form that presents the data in a different way to make data entry faster. The Main form is a course and then you assign people to it. Because of the many to many relationship you can present data from both directions. Assign courses to a person or Assign people to a course.
I like it so far. What if there is a new training course that we've never taken, how would I enter that. Also. Once completed will I be able to run a query to list all trainings staff has had and put a date more than a 2 yr period and delete those records? Because when an inspection gets done then we star with a new period of dates for trainings. Can I go into the table design veiw to add and remove certain trainings?(for example 2019 Summer Institute that will never happen again) and add different trainings. Some trainings have expiration dates ( can we add that to some trainings ex required trainings - CPR/MAT and there's another one TSG-Interrater Reliability also has an expired date that I've never tracked before). I've been doing that one manually until I could add to db.
 

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
I added courses of study and classes. In my mind someone can have multiple courses of study and for each course of study they can have multiple classes. This becomes a 1 to many to many. There is two subforms in that tab. You can add different classes to each course of study. I added waivered sites. So you add a waivered site.

This could be a simple query and put into a subform. Then you can show the totals above the details for each staff member. I can demo that.


All courses need to get entered in the training table to track their credit hours. However that makes the pull down very long. There are lots of ways to get fancy to make it easier to find a course. One could be a find as you type combo. If you type "child" it will filter the list to any course with "child" in the name. You could add some buttons for "Mandatory", "Reoccuring", "This year", etc to filter the list further. My guess is you have a course and a bunch of people take it. In that case I would build another form that presents the data in a different way to make data entry faster. The Main form is a course and then you assign people to it. Because of the many to many relationship you can present data from both directions. Assign courses to a person or Assign people to a course.
Once we get the entire DB going strong would I be able to put it on Access web? I have MS program I've purchased and pay it yearly, but never really used it for anything this important. I would like managers be able to look at anytime regarding their staff they supervised to see where they are at in our program - all sites they are waivered to/training hours/what trainings they need or taken. But set it where they can change anything tjust to look at it. I know I'm asking for miracle - but if anything I ask it's coming to me - to know can/cannot be done/edited once complete.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:57
Joined
May 21, 2018
Messages
8,463
This update has the feature to add/edit courses and assign credits to the course. And it has the complimentary view where you pick a class and assign the students. You get to those features here through the two buttons.
StaffCourse.png


Here you can see the complimentary views. In the main form you assign a person to their courses. In the other form you assign people to a course. All data is saved in the same location. Every many to many can be done like this with two ways to think about the data.

StaffCourse2.png
 

Attachments

  • MajP_TrainingDB V5.accdb
    1.6 MB · Views: 135

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
This update has the feature to add/edit courses and assign credits to the course. And it has the complimentary view where you pick a class and assign the students. You get to those features here through the two buttons.
View attachment 85013

Here you can see the complimentary views. In the main form you assign a person to their courses. In the other form you assign people to a course. All data is saved in the same location. Every many to many can be done like this with two ways to think about the data.

View attachment 85014
This is going to be GREAT!! Once I can add all out staff to this database and start using it - I may have more questions. Once you've got it the way you think it's ready to use I want to add all my staff data to see how it will work and to see what I need to do to keep data up-to-date. When I'm adding a new employee - is the PK auto number so I do not need the employee # when first added? Or will need the employee # of new employees before adding them to DB?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:57
Joined
May 21, 2018
Messages
8,463
When I'm adding a new employee - is the PK auto number so I do not need the employee # when first added?
Normally an employee number is a fine PK. It is simple, unique, and unchanging. Unless you are my company that gets sold multiple times and you end up with a new number each time. Or you are in the admin office trying to log a new employee and waiting a couple of days for corporate to assign a new number. That is why an autonumber is always a good choice. In this case you can add a new employee without an employee number. However you can force it to be unique, but not required. That way you can add someone without an employee number, but it will not allow you to assign duplicates.

Normally you would not delete records. Lets assume you only count training that has taken in the last year. You can now show that and calculate the training by querying the records with those criteria. You would not go back and delete the training because it may have historical value. Even if different course had different expiration you could handle that. In the course table you would add a valid duration field (12, 24, 36 .... months). You would then be able to show current training based on varying duration.
 

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
Normally an employee number is a fine PK. It is simple, unique, and unchanging. Unless you are my company that gets sold multiple times and you end up with a new number each time. Or you are in the admin office trying to log a new employee and waiting a couple of days for corporate to assign a new number. That is why an autonumber is always a good choice. In this case you can add a new employee without an employee number. However you can force it to be unique, but not required. That way you can add someone without an employee number, but it will not allow you to assign duplicates.

Normally you would not delete records. Lets assume you only count training that has taken in the last year. You can now show that and calculate the training by querying the records with those criteria. You would not go back and delete the training because it may have historical value. Even if different course had different expiration you could handle that. In the course table you would add a valid duration field (12, 24, 36 .... months). You would then be able to show current training based on varying duration.
That is perfect for entering new employees. It get me ahead of the game when they are hired, but it takes some time for HR to get me the employees employee #. As for trainings, that sounds ok. There really is no need to keep trainings on record once they licensing period is complete expect for the once that are required and/or have an expiration date(sometimes a certain training expiration may expire before / or after an review time frame). Otherwise we no longer go back to their previous trainings. I have a folder fore every employee where I keep all certificates that is for the employee whenever they need proof of having a trainings. I am starting to feel pretty excited getting this DB running like it should. It should've from the beginning, but I didn't know who to get it to where it needed to be. 20 yrs ago I created the DB when I was in transportation ( it worked very good) but I had 1 table to work from because that's all I knew. I made it work. However, when I went to different trainings; they told me it would've worked better with breaking the information I had (lots of info) into different tables. so I didn't ever deal with relationships. You have helped me so much with this section, I can't even begin to express the relief I feel getting this done.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:57
Joined
May 21, 2018
Messages
8,463
As I said this is not the simplest database to properly design. You have multiple many to many relations and also several 1 to many to many. The thing is once the table design meets your data requirements, building the features is easy.
Here is one more update. I added certificates, but this may or may not fit your needs. I tried to make a certificate more general, since you had fields for different types of certificates. I still trying to understand if IstAid MAT can be handled like other training courses or is it handled differently.
 

Attachments

  • MajP_TrainingDB V6.accdb
    2.6 MB · Views: 141

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
As I said this is not the simplest database to properly design. You have multiple many to many relations and also several 1 to many to many. The thing is once the table design meets your data requirements, building the features is easy.
Here is one more update. I added certificates, but this may or may not fit your needs. I tried to make a certificate more general, since you had fields for different types of certificates. I still trying to understand if IstAid MAT can be handled like other training courses or is it handled differently.
The First Aid/CPR/AED and MAT can be with the other trainings however i will need a Never Taken field and an expiration date. MAT training I will need an expiration date also. I can do a query to show staff who's never taken it and to show expiration dates.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:57
Joined
May 21, 2018
Messages
8,463
Code:
The First Aid/CPR/AED and MAT can be with the other trainings however i will need a Never Taken field and an expiration date. MAT training I will need an expiration date also. I can do a query to show staff who's never taken it and to show expiration dates

Do all training courses have an expiration? I think you mentionded something about two years for most of them. If that is the case I would put an expiration duration in the Courses table. (values would be like 1 year, 2 years,...,Indefinite). This would be from a related table so 1 year would really be related to a numeric value of 365. Then you can show current and expired training in a simple query. Also in the Courses table there is a field that shows if a course is mandatory, so a query can also show the required training and what has been completed. I added this for an example
 

Attachments

  • MajP_TrainingDB V7.accdb
    1.7 MB · Views: 125

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
Code:
The First Aid/CPR/AED and MAT can be with the other trainings however i will need a Never Taken field and an expiration date. MAT training I will need an expiration date also. I can do a query to show staff who's never taken it and to show expiration dates

Do all training courses have an expiration? I think you mentionded something about two years for most of them. If that is the case I would put an expiration duration in the Courses table. (values would be like 1 year, 2 years,...,Indefinite). This would be from a related table so 1 year would really be related to a numeric value of 365. Then you can show current and expired training in a simple query. Also in the Courses table there is a field that shows if a course is mandatory, so a query can also show the required training and what has been completed. I added this for an example
Ok... I know this is so complicated. Let me explain what counts for our trainings. No.... all trainings do not have expiration dates. OUr required trainings must me done every 2 yrs. First Aid/CPR is every years (I believe if I remember right-i'm home right now) MAT trainings are every 3 years. There there's a training TSG-Irregulatory Reality training is good for 3 yrs. But each staff is waivered to different sites *this means they can work in a classroom at all sites they are waivered to) this includes their home site. Each site has different review periods that it requires for all staff who is waivered for site that is going through a review has to have 30 hours of trainings. The only trainings that have been completed (like First Aid/CPR) it counts toward training hours is when it was completed and it has to be valid. (ex. if 1st Aid/CPR training was complete 1 week before review cut off day-it counts for those hours) but if this staff was waivered to another site the following year and she sill needs her 30 hours just in that sites review period. Even though her 1st Aid/CPR card is still valid - she will not get credit for the 3 hours of trainings because she didn't take the training within the licensing period of the site that is being reviewed. Every site has different site licensing review dates. I'm in the process right now with CN the mid-point review period is 9.30.18 thru 9.30.20. Bush is going on now 9.8.18 thru 9.8.20. I need to show our licensor all staff who are waivered for both those sites and give her certificates of all trainings that was done by staff. The licensing periods do not change for all sites. I don't know if this may help. Once, the mid=point / relicensing periods are complete we don't need to see their trainings. However, I do keep every staffs certicates just in case they need them of their own personal use. I'm not sure if I explain this process so you can understand it.
 

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
Ok... I know this is so complicated. Let me explain what counts for our trainings. No.... all trainings do not have expiration dates. OUr required trainings must me done every 2 yrs. First Aid/CPR is every years (I believe if I remember right-i'm home right now) MAT trainings are every 3 years. There there's a training TSG-Irregulatory Reality training is good for 3 yrs. But each staff is waivered to different sites *this means they can work in a classroom at all sites they are waivered to) this includes their home site. Each site has different review periods that it requires for all staff who is waivered for site that is going through a review has to have 30 hours of trainings. The only trainings that have been completed (like First Aid/CPR) it counts toward training hours is when it was completed and it has to be valid. (ex. if 1st Aid/CPR training was complete 1 week before review cut off day-it counts for those hours) but if this staff was waivered to another site the following year and she sill needs her 30 hours just in that sites review period. Even though her 1st Aid/CPR card is still valid - she will not get credit for the 3 hours of trainings because she didn't take the training within the licensing period of the site that is being reviewed. Every site has different site licensing review dates. I'm in the process right now with CN the mid-point review period is 9.30.18 thru 9.30.20. Bush is going on now 9.8.18 thru 9.8.20. I need to show our licensor all staff who are waivered for both those sites and give her certificates of all trainings that was done by staff. The licensing periods do not change for all sites. I don't know if this may help. Once, the mid=point / relicensing periods are complete we don't need to see their trainings. However, I do keep every staffs certicates just in case they need them of their own personal use. I'm not sure if I explain this process so you can understand it.
I also started entering more staff in the last DB you sent. I really like how to enter trainings. This will definitely help with typos and other errors that can occur the old way. I was playing around with adding new trainings and such. Looking at the the breakdown of hours the be placed within the nine categories. I wasn't understanding how to see what total hours each staff has in total hours and each category as well. I've been so busy with the 2 sites reviewing periods that I haven't had the time I've wanted to work with it. This weekend I'm gong to really get into it and work with it much more. I know this is so challenging, but I so appreciate your working on this with all the quirky data and times that I need.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:57
Joined
May 21, 2018
Messages
8,463
On the required training tab I also put the totals. I did not get the query quite right so need to work on it. It shows all the totals, but does not show the credit category where the total is zero.
I need to digest the rest of what you wrote.
 

Attachments

  • MajP_TrainingDB V8.accdb
    1.7 MB · Views: 125

sribblett

Member
Local time
Today, 09:57
Joined
Aug 24, 2020
Messages
89
On the required training tab I also put the totals. I did not get the query quite right so need to work on it. It shows all the totals, but does not show the credit category where the total is zero.
I need to digest the rest of what you wrote.
I hope I explained it good enough for you to understand. It is quite cumbersome. I'm so sorry about that.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:57
Joined
May 21, 2018
Messages
8,463
This has the fixed query so the Required tab shows totals for all credit areas including 0 and status of required training.
 

Attachments

  • MajP_TrainingDB V9.accdb
    1.7 MB · Views: 129

Users who are viewing this thread

Top Bottom