Updating the Database 1

[uapak90]

This is for my job, I am not an expert in database but here is my problem:
I have a main table that has the name, number and version of training documents. I have to link that to different employees, the only problem is that every employee has different documents they needed to be trained on and when they did. But hwen i make a form, and add the main table as a subform, every employee has the same information. Anyway i can have separate information for each employee, but whenever i have to update anything on the documents, i can go right into the main table update it and it will update for everyone?

 

[lespaul]

you should have at least these three tables:

Employees
EmpID (PK)
Name
(other info about employee)

Documents
Number (PK)
Name
Version
(other info about document)

EmployeeDocTraining
EmployeeID {Composite
DocumentID PK}
TrainingDate



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[uapak90]

The first two tables, i have them created. The last one, i like your idea with the employee id, but the thing is liek some employees would be trained on the same document, for example, some documents are necessary for all employees. Would creaitng a primary key on doc id work when i would have to repeat it with some employees? Also, when i am talking about the same employee, would i have to repeat the Employee ID for every record i put in for them, because some employees have to be trained on about 100 or more documents.Thanks so much for your help

 

[uapak90]

And also i have to report the document version every employee is trained on, is there a way i can add that information on the third table, and whenever a versino updates, all i have to do is go in the 2nd table nd update the version and it would be updated or added on the third table also?

 

[lespaul]

How do you determine which documents an employee has to be trained on?

would i have to repeat the Employee ID for every record i put in for them, because some employees have to be trained on about 100 or more documents.

you would have to enter a combination of EmployeeID and DocumentID for each document that employee needs to be trained on.

[tt]
Employee
ID Name Department
1 Jane Doe Adminstration
2 John Smith Sales
3 Bob Jones Accounting

Documents
ID Name Version CurrentVersion
1 Company Policy 2.0 Y
2 GAAP Guidelines 1.0 Y
3 Collection Calls 1.1 Y
4 Sales Scripts 3.5 Y

EmployeeDocTraining
EmpID DocID TrainDate
1 1 1/1/2007
2 1 2/15/2007
2 4 2/15/2007
3 1 3/18/2007
3 2 3/18/2007
3 3 3/18/2007[/tt]

So Jane Doe only had to see the Company Policy document since she's in adminstration. The person from the Sales Dept had to train on the Company Policy AND the Sales Scripts. Bob from Accounting had to read the Company Policy, the Collection Calls document AND the GAAP (Generally Accepted Accounting Principles) guidelines.

By creating a composite primary key of EmpID AND DocID the combination of those two fields has to be unique. So you couldn't enter Jane Doe to have to read the Company Policy again (until there's a new version which would have a new DocID).

If you had another table that listed what documents each department was required to train on (or some other criteria), you could use that list to populate the EmployeeDocTraining table.

Be sure to read the fundamentals document linked below...it will really help you understand the concepts that I'm applying to your problem.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[uapak90]

By creating a composite primary key of EmpID AND DocID the combination of those two fields has to be unique. So you couldn't enter Jane Doe to have to read the Company Policy again (until there's a new version which would have a new DocID).

If you had another table that listed what documents each department was required to train on (or some other criteria), you could use that list to populate the EmployeeDocTraining table"

Can you please explain the first paragraph.. liek how would i be able to make composite primary keys? For my document database, some of my doc Id's repeat because they have been put in more than once because the version updated.. i dotn know if that would affect anything.

And the other table you are talking about, i have that table made too, according to the employee title. How would I populate the employee doc training through that? Ill show you teh diffenert tables I have:

Master Document Database:
DocID DocVersion DocTitle EffectiveDate

Employee Table
EmployeeID EmployeeName JobTitle JobCode Hiredate TerminationDate

Employee Training Record
JobCode JobTitle DocID DocVersion DocTitle Employee DateTrained


in the employeee training record, i added another yes/no column that i named needtraining. I was trying to make it as though whenever a version updated, nd it did not match the version they were trained on, it would say they need training..

But allt he help you provided is great! I am realy stuck at this.. i suck at access.

 

[uapak90]

i am also reading that document.. hopefuly that helps too.

 

[lespaul]

like how would i be able to make composite primary keys?

In the table design view, highlight the two fields and select the 'Key' button. This will create a composite PK.

The document will definitely help!

So, if you know what documents someone has to be trained on based on title, then you would have this table:
[tt]
TrainingReq
DocName EmpTitle
Company Policy All
Sales Scripts Salesman
GAAP Guidelines Accountant
Collection Calls Accountant
[/tt]

then you can use this table to insert the required information for each title into the EmployeeTraining record. Additionally, you need to take the document version number and title OUT of the EmployeeTraining record table. You only want to store the DocID. If in a query or report you need to display the title and version, you would join into the Document table. Storing the same information in multiple locations breaks the rules you just read about!

Leslie

 

[uapak90]

thanks alot! that definitely helped! wow.. thigns are so much easier noww

 

[uapak90]

I have one more question... I liked your idea at end about the training required by job title. How would I go about linking it to the whoel database including the employees.

If i have this table
Doc ID JobTitle JobCode


how woudl i link it to the others, and right now i have

the document table
employee table
employee training record

 

[lespaul]

You'll have to set up a process that identifies new employees and depending on their job title, get the documents required from the table and insert new records in the Employee Training Record table.

Once you get all the tables set up, you'll have to develop some forms that allow the users to enter the date the training was completed. I would switch to Forum702 and get some help on setting up a form that inserts the records and allows the update of the training date.