Training Database ideas

[ramez75]

Hi,

I am working on a training database and I just cant get the logic to work the way I want it. A couple of things that the database need to be able to handle is:
There is something called Modules. Each Module consist of certain documents/work instructions. Each employee will be required to train on certain modules depending on what is the department and job. The other thing is there are certain documents/work instructions that could be repetitive to more than one module. Also documents will be revised so the database should be able to save all revisions but should show the most current.....

Some documents might need an instructor to train while others will be readonly.

So an example:

Employees = John Doe, Ann Smith, Henry James, Mary Shelly, etc
Modules = Auditor, Company Core, QA General, etc
Docs = SOP123 revC, WI123 revA, SOP987 revA, SOP456 revB, WI402 revC, etc

Auditor = SOP123 + WI123
Company Core = SOP987 + SOP456
QA General = SOP123 + WI402 + SOP456

All employees need to be trained on Company Core Module

Hence, John Doe need to train on = Company COre + QA General
Henry James = Company Core + Auditor
etc

Now ofcourse document revision will change and when they do then the retraining will be required. So I will have a query to tell be who is deficient and who is not

So any ideas how to tackle this.

I created a database with tblEmployees (Employee Name, Department, HireDate, Active?), tblProcedure (Doc#, DocTitle, DocRev, DocEffectiveDate, Obsolete?), tblModules (ModulesName), tblRecords (I dont know what goes here yet)

So any input help on this is greatly appreciated

Thanks

 

[spikepl]

Why do you think that you are first with these issues? Search this site for Training, or SOP or GMP.

 

[ramez75]

I know I am not the first. I have been searching within the forum and still searching. I was trying to see what is the best approach out there from a relationship design.
Is it better to combine the Modules and Procedures under one table or separate them.

Why do you think that you are first with these issues? Search this site for Training, or SOP or GMP.

 

[ramez75]

So this what I have done so far.

I created the following tables:

1. tblDepartments (strDpt (Pk), strDeptHead)
2. tblEmployees( strEmpNum (Pk), strName, strDept, strDpt, dtmHiredate, ysnactive)
3. tblModules (strDept (Pk))
4. tblProcedures (strPRocName, strProcNum(PK), strProcRev(PK), dtmRevDate, ysnObsolete)
5. tblRequired (ID, strPRocNum(Pk), strRev(Pk), strDept(Pk))

So tblRequired I use to link the Modules to the Procedures (Num, Title, Rev). I have 56 modules and 2300 documents.

Now the 2 obstacles I am trying to tackle is
1. I have to link the emplyees to the appropriate modules and documents which based on my setup will be tblRequired. Ofcourse every employee have multiple modules to train too. So any ideas how to go about this. Do I create another table lets say tblRecords and If I do so how do I for each employee choose multiple modules from tblModules.
2. The other thing i want to account for in the table design is capturing the documents revision history. So with time documents revision will change and in tblProcedure I can only have the most current revision. Do I create another table to capture the older revisions and if so how will the table design look like.

Thanks in advance on any feedback

 

[jdraw]

It seems you have a need to record training for Employees. Often this is linked to a need to identify training for Positions. Then you have to determine if the Employee has achieved the courses/training required of the Position.

Here is a general data model for such a set up. It may give you a starting point.

Hope this helps.

 

[ramez75]

Thank you, i will look at it

It seems you have a need to record training for Employees. Often this is linked to a need to identify training for Positions. Then you have to determine if the Employee has achieved the courses/training required of the Position.

Here is a general data model for such a set up. It may give you a starting point.

Hope this helps.

 

[ramez75]

An update to post#4.

I created another table tblModEmp (strEmpNum, strDept) that links the employee to the module. So now the next thing is figure out how to update the document revision (strProcRev) in tblProcedures but keeping track of the older document revision. It seems I will need another table but cant figure it out

 

[jdraw]

Your approach confuses me. Why don't you build a data model based on your requirements, then validate the model with some test data?

I don't understand the logic of adding a table then saying

So now the next thing is figure out how to update the document revision (strProcRev) in tblProcedures but keeping track of the older document revision.

It seems you are in the middle of building a house, and still not sure how many rooms; where will the bathroom be; and geez I now have to think about the plumbing and a roof and doors.

But there are many ways to skin a cat, so they say........

 

[ramez75]

I am actually trying to build it on the requirements given to me. I am just trying to make sure I have the right tables to capture all the requirements/data.

Your approach confuses me. Why don't you build a data model based on your requirements, then validate the model with some test data?

I don't understand the logic of adding a table then saying


It seems you are in the middle of building a house, and still not sure how many rooms; where will the bathroom be; and geez I now have to think about the plumbing and a roof and doors.

But there are many ways to skin a cat, so they say........

 

[ramez75]

I have a form to allow the User to add new documents (strProcNum) to the database (tblProcedure). After adding the new document (strProcNum) the User must link it to the appropriate Modules (tblRequired). Hence, I created a button on frmDocNew if clicked will open frmDocLinkMod.

Below is what I have so far which I know is not correct as I dont have a way to open frmDocLinkMod to the new created strProcNum. How do I modify the below to address what I want. I am thinking something like DoCmd.GoToRecord or DoCmd.FindRecord commands but dont know how to add it to the below

Private Sub cmdLink_Click()
On Error GoTo Err_cmdLink_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmDocLinkMod"
  
    stLinkCriteria = "[strProcNum]=" & "'" & Me![strProcNum] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmDocNew"
    
Exit_cmdLink_Click:
    Exit Sub
Err_cmdLink_Click:
    MsgBox Err.Description
    Resume Exit_cmdLink_Click
    
End Sub

Thank you

 

[jdraw]

Do you have a list of requirements? Business facts.

Here are some sample facts from Barry Williams site
http://www.databaseanswers.org
These are associated with the Car Hire data model.
The model can be found here
http://www.databaseanswers.org/data_models/car_hire/car_hire_physical.htm

A. What are the Things of Interest ?
A.1 Bookings
A.2 Cars
A.3 Confirmation Letters
A.4 Customers
A.5 Invoices
A.6 Payments
A.7 Vans
A.8 Vehicles

B. How are the Things of Interest related ?
B.1 A Booking is for one Vehicle and one Customer.
B.2 A Customer can be associated with one or many Vehicles.
B.3 A customer cannot hire a car for longer than a week.
B.4 A Customer can receive one or many Invoices.
B.5 A Vehicle can be a Car or a Van.
B.6 An Invoice is for one and only one Customer.

C. What are the characteristics of the Things of Interest ?
C.1 Customer details include name,address, gender telephone number.
C.2 Each booking includes customer, car, date of hire and
date on which the vehicle is to be returned.

You should have details at this sort of level for your "business" if you plan on building a database to support it.


Good luck.

 

[ramez75]

jdraw,

I reviewed the attachment and I have a major difference. The organization doesnt want to use JobPositions/Titles as it changes so frequent that the current database wasnt able to keep up and the info is inaccurate. So as part of an improvement effort and to stramline line the process we decided to use Modules based on departments.

What I mean is for example QA Department will have 8 Modules and each module will have a number of Procedures, Work Instructions (WI), etc to train on. Ofcourse some modules might share the same procedure or WI. With this being said lets say the QA department has the following module
QA General, QA Manager, QA Director, QA Auditor, QA Batch Release, QA Document Control, QA Tech Service, QA Calibration, etc and each of these modules will have 5 to 15 procedures/WIs within. So a Quality Engineer or Senior QA Engineer will be trained on QA General, QA Auditor and QA Tech Service. As you can see we eliminated the job title/position.

So in the attached the employeeId will have one JobPositionId but in mycase the employeeId (strEmpNum) will have multiple JobPositionId (strDept) and each strDept is linked to a number of procedures/WIs (strProcNum). Hence the reason I setup the tables as in post#4 maybe not the best way.

So the next I was working on after I set up the table is how will I capture the document revision as they will change with time. Ofcourse when revision are changed I cant overwrite the older rev I will need to recreate the document name and title but then add the new rev. So tblProcedure with time will have to look something like this

SOP123 Core Rev A
SOP123 Core Rev B
SOP123 Core Rev C, etc

It seems you have a need to record training for Employees. Often this is linked to a need to identify training for Positions. Then you have to determine if the Employee has achieved the courses/training required of the Position.

Here is a general data model for such a set up. It may give you a starting point.

Hope this helps.

 

[jdraw]

All I can say is you should have a clear statement of your business facts.
And you can identify the subjects in the terminology that is meaningful to you.
You should have test data and be able to "test the data model". You compare the data with the evolving data model, and reconcile every difference.

You should not be in the middle of onClick custom procedures in vba, nor creating tables on the fly before you have a data model that supports your business.

Good luck with your project.