Question Designing Database Issue

[ramez75]

Hi,

I am trying to design a database (Access 2003) to use to track training. I just cant get the tables/forms/query to work like I want. I feel I am missing something.

As I said the database is mainly used to track training of individuals by being able to print reports and ensuring if all training is current, etc.

This what I have put together
tbldepartment - this table contains all the department, such as Company Core, Operation, Marketing, HR, RD, QA, etc. Company Core is the SOP/WI that will be required to be trained by all employees regardless of what department. Such as Safety SOP, Quality Policy, etc

tblFunction - this table contains the training modules within each department. Example QA dept has 6 modules, QA General, QA Manager, Auditor, Doc Control, etc

tblEmployees - this table contains all the employees First and last name, title, Employement Status.

tbldocuments - this table contains all the documents SOPs and WIs numbers, titles, revision. certain SOPs/WI will be part of certain modules.

Now the part that I cant get to work or what I am trying to accomplish. Ok so
1. all employees are assigned to different department so I will need to be able to track that. So if asked to show a report of all employees with lets say Operation, I need to be able to do that
2. Departments have specific function modules. Example
QA --> 1. QA General, QA Manager, Auditor, Doc Control. So certain employees will be trained on certain modules or a combination of modules. Example Quality Manager will need to be trained on Company Core, QA General, QA Manager, Auditor and Doc Control modules, where as a QA Tech will be trained on Company Core, QA General and so on so forth
3. Each module have SOPs/WIs. Example QA Manager has lets say 6 SOPs and 4 WIs like SOP1234, SOP4563, SOP7685, SOP0976, SOP5674, sop8765 WI5674, WI1237, WI7638, WI9345.

So I need to be able to somehow link all the above together,

So if John DOe is the Quality Manager. I need to be able to print a report showing something as follows
Fullname = John Doe
Department; tbldepartment = QA
Function Specific;tblfunction (documents; tbldocument) =
1. Company Core (SOPs/WIs = SOP1, SOP2, WI1, WI2, etc)
2. QA General (SOPs/WIs = SOP3, SOP4, WI3, WI4, etc)
3. QA Manager (SOPs/WIs = SOP5, SOP6, WI5, WI6, etc)
etc

IS the above possible. I been trying to creat forms/tables but I just cant get all the above output in one table that eventually i can manipulate using queries to create different reports

Any advice, ideas, is greatly appreciated

I can post what I have so far if needed

RB

 

[spikepl]

Attach a picture of your relations (and tables stretched so that all fields are visible). You have many many-to-many relations, but no mention of them in your narrative. It is also not clear what exactly tblDepartments contains.

 

[jdraw]

There are a number of existing data models associated with tracking at
http://www.databaseanswers.org/data_models/index.htm

You may find one that you can adapt in whole or in part to help with your design.
Good luck.

 

[ramez75]

Attached is the database i have so far. Let me know if the approach is wrong and what I need to change to get what I need to.

jdraw i will look at the link you provided

 

[jdraw]

I've just opened your mdb.
A few questions:

You mention SOP/WI a lot, but I don't see a reference to this in your tables and relationships????

I don't see a relationship between Employee and Department -- you may need a junction table for this (but I don't know your set up)

What does tblData represent?

What links Employee to Function?

How do you identify Training or TrainingReceived or TrainingRequired...?

 

[ramez75]

jdraw,

tblDoc contains the SOP/WI that i keep referring to "Document Number". tblFunctionDocument was my attempt to link the document to the function (modules) as you can see in the relationships. Again I dont if my approach is the right thing

I had a relationship DeptId in tblDepartment to DeptId in tblEmployees but I deleted before I attached the Database thinking that wasnt correct. What do you mean by junction table....something like tblFunctionDocument but call it tblDepartmentEmployees.

tblData was basically my attempt to create an output table. Also its the table where I link Employee to department and function using frmData.

I dont know what to do to link Employee to Function, i been trying different relationships but not getting a correct output.

Training Required is basically or I am hoping will be in tblData since (if i can figure out the relationships) it will be where every employee will be assigned to the appropriate modules (based on the department and function) which have the linked SOPs/WIs.

Training Received....didnt get to that yet.

Hope I answered all your questions.

Thank you for taking the time and looking at it

I've just opened your mdb.
A few questions:

You mention SOP/WI a lot, but I don't see a reference to this in your tables and relationships????

I don't see a relationship between Employee and Department -- you may need a junction table for this (but I don't know your set up)

What does tblData represent?

What links Employee to Function?

How do you identify Training or TrainingReceived or TrainingRequired...?

 

[jdraw]

Take a look at this http://www.rogersaccesslibrary.com/Tutorials/The Hernandez Process In A Nutshell.doc

and see if it helps in understanding how the pieces fit together.

Good luck, let us know what you find.

 

[ramez75]

Thanks jdraw, i will read it.

The difficultly or complexity I am running into is I currently have 2 buckets of information that I dont know how to relate them.

1. I have tblFunction which links the modules (function) to the appropriate Documents (SOPs/WI).

2. I have tblEmployees and tblDept linked/related

I need to figure out away to link bullet "1" to "2" in a way that if I for example click on Employee I should be able to see all the modules assigned and if I can click on a specific module I should be able to see all the SOPs/WIs assigned.

But at the same time if I click on a SOP or WI I want to be able to see who are all the employees required to take it. If I click on a specific module I want to be able to see who are the employees assgined to and what are the SOPs/WIs comprising the modules

This is were i am now

 

[jdraw]

Lets look at

if I click on a SOP or WI I want to be able to see who are all the employees required to take it. If I click on a specific module I want to be able to see who are the employees assgined to and what are the SOPs/WIs comprising the modules

If an Employee is required to take it, what exactly is involved? Is the Employee required to take a certain training because of the Position/Job Function they occupy? Is this some sort of Company policy or professional rating requirement?

There are business rules within your organization that determine the relationships between the "things" (entities) in your business.

 

[ramez75]

Yes employees are required to take certain training because of the job function they occupy. Its both a company policy and requirement.

Example:
So John Doe (Quality Manager)
He belongs to the Quality Assurance department. Because of his function and department he is required to train on the below modules
1. Compant Core (10 SOPs)
2. QA General (contains lets say 10 SOPs and 15 WIs)
3. QA Manager (4 SOPs, 3 WIs)
4. QA Auditor (4 SOPs)

On the other hand Ann Doe (Process Tech)
She belongs to the Operation department. Because of her function and department she is required to train on the below modules
1. Compant Core (10 SOPs)
2. Ops General (5 SOPs and 20 WIs)
3. Ops Tecnical (15 WIs)
4. QA Auditor (4 SOPs) since she is a certified Auditor though she is not in the QA department she will still need to train on this module to be able to maintain the auditing status

Lets look at


If an Employee is required to take it, what exactly is involved? Is the Employee required to take a certain training because of the Position/Job Function they occupy? Is this some sort of Company policy or professional rating requirement?

There are business rules within your organization that determine the relationships between the "things" (entities) in your business.

 

[ramez75]

Attached is the image of how i did the relationships. Maybe not the best approach but I am if I open tblFunctionDocument it will show me everything. Huge table since alot of repetition. So if John Doe end up with 3 modules which translates to lets say 30 SOP and 20 WIs then the name will be shown 50 times. So I think its going to be massive since there are arond 260 employees and most probably everyone will have minimum of 50 SOPs/WIs.

I am hoping the report will make it look cleaner.

I couldnt do the cascade like I wanted to.

Now I have to figure out how to incorporate to the way I have it set up how I will be able to track training received, etc

 

[spikepl]

A lot of work remains. Start building from top down and forget, for the moment, tblFunction Documents (that table is no good, because what happens if an employee migrates from one dept to another?). Besides, the extraction of information from the DB will be based on queries from multiple tables, not on one table.

• You have a Department.
• The Department has functions
• A department has employees
• Each function is filled by one or more employees.
• Each function has some required modules
• A required module consists of a number of SOP/Wi's
• A module is unique for Department or for Company?
• An employee, because of his function, must take modules required by the employee's function
• A SOP/WI has versions. If you are in a GMP environment, retraining is required on existing SOPs - is it 2 years?- A new version of a SOP requires retraining on the new version.

Note that the module requiremnet of training in general pertains to a SOP/WI, not to a specific version of it. This may be designed into two tables: one with the SOP/WI number/title, and one with version information and issue date. This obviates the need of a lot of relinking, if a new version arrives for a given SOP/WI

An employee once trained on a SOP does not lose this training just because of change in function or department. Under GMP you also need to keep records of past training.

A few mental tests of your structure are these:

1. what happens if you delete an employee?
2. What happens if you shift an employee or add a new one?
3. What if a new requirement arises for a module?
4. A new module?
5. What if a requirement disappears? For a module, or for a given SOP/WI in the module?

 

[ramez75]

Spikepl,

I totally agree with you you hit the points. I been looking att he table at the output doesnt make sense. The reason I said that because I have employees in the department and the department has function and certain employees will have to be trained on modules from one or more departments. The modules are unique for departments and each module will have a set of SOPs/WIs but that doesnt mean that a certain SOP/WI will not be seen in a number of modules.

The way I built tblDoc has the doc #, title, rev, status (could be effective or obsolete) based on the choice the form will enable the appropriate field. So if status is effective then the field effective date will become visible and if status is obsolete then Obsolete date will be visible. So thats way I can be specific in my queries.

WE are GMP environment we follow 21 CFR 820. We retain for 3 years. And of course the database that I am trying to build I will definitely need a way to track if a new version of SOP/WI is approved then a retraining will be necessary.

I will be keeping records. Currently we have a paper form that individuals fill out everytime they go through training and in the form the record the Doc#, title, version, date of training. This paper document is kept on file.

tblEmployee has a field so I can track Status of the Employee (Active, Terminated, Leave Of Absence). Again I will be interested in the Active ones but I still want to have the terminated, LOA on file based on the retention policy.

So you suggest to build from top down. What is top though is it Employee, or is it Department, I just cant get the relations of the tables worked out.

I know I need tblEmployee, tblDepartment, tblFunction, tblDocuments, tblRecords (an output table) but I dont now how they should be linked and if I need other tables

 

[ramez75]

Another thing thats driving me crazy and I cant think of way around it.

So lets say we have a Department for example "Quality Assurance" and "Quality Control". So "Quality Assurance" Department has the below modules
1. QA General (consist of SOPs/WIs)
2. QA Manger (consist of SOPs/WIs)
3. QA Auditor (consist of SOPs/WIs)
4. Document Control (consist of SOPs/WIs)
5. etc

And "Quality Control" Department has the below modules
1. QC General (consist of SOPs/WIs)
2. QC Manger (consist of SOPs/WIs)
3. IQC (consist of SOPs/WIs)
4. etc

So lets say I have John Doe belongs to the "Quality Control" department and he is a manager and an auditor.
HE will have to be trained on the below modules
1. QC General (consist of SOPs/WIs)
2. QC Manger (consist of SOPs/WIs)
AND
3. QA Auditor (consist of SOPs/WIs)

Now here where I cant get my tables relations to work

So if I have an output table with Department, Function, Employee NAme, SOP/WI title
What will be the department = "Quality Assurance" or "Quality Control". The reason I say that is "QA Auditor belong to "Quality Assurance" Dept on the other hand the employee belongs to "Quality Control" dept so ????? I dont know what my table should say

 

[spikepl]

You'll need some more tables for creating many-to.many relationships. By top-.down I meant start with department, then the requirements: functions, modules then sops. Think about what I wrote how to store SOP info - one table may seem simple, but may not be the best way.. Also, from what you say one sop can belong to more than one module - another many-to-many relation for which you'd ned a junction table.

Then you have the employees and and the training requirement, derived from their function that gives the relevant moduels and the sops.

When you can express all that sensibly, then it's time to construct the training records.

The questions at the end of my previous post are logical tests of your data stucture - they don't mean you'd actually do it (eg. "delete an employee"). Such questions oftentimes reveal some deficiency in the design - you can make almost any design "work", but not all of them wil be equally sensible.

As to "I don't know how" - sit and think, draw, get going and check in here from time to time for a review or for specific questions.

 

[spikepl]

I just saw you new post - you linger in "output" and that limits your thinking. Forget that but start with eg the structure for requirements. Did you read the link of jdraw's further up? And you have just revealed another many-to-many relation: one employee can apparently fill more than one function in a given department.

 

[ramez75]

What do you mean by Junction table....

If I had to guess lets say I create a table with EmpID ( from tblEmployee) and DeptID (from tblDepartment) will that table me a Junction table?

 

[ramez75]

I just saw you new post - you linger in "output" and that limits your thinking. Forget that but start with eg the structure for requirements. Did you read the link of jdraw's further up? And you have just revealed another many-to-many relation: one employee can apparently fill more than one function in a given department.

I am looking at the link right now...lots of databases. I will start from scratch. I will have a couple of many-to-many relations thats for sure

 

[spikepl]

With a junction table I mean storage space for a mnay-to-many relation.

Eg
tblStudents
------------
StudentID (PK)
Name
Other data

tblCourses
-----------
CourseID (PK)
CourseName


tblStudentsTakingCourses
-------------------------
StudentID (FK)
CourseID (FK)

the junction table allows one student to take many courses, and one course being attended by many students a many-to-many relation.

Every time, you need to ask whether entity A can be related to only one entity B , whereas entity B can be related to many A' (this is a one-to-many relation)or whether any number of A's can be related to any number of B's (this is a many-to-many relation).

 

[spikepl]

With the link I meant jdraw's post #7 - a good list of sensible things to ask