Traiing DB

[tmarik]

I am trying to create a DB to track the training of employees on when they took the course and when the course will need to be renewed. So far I have 3 tables, one with the employee information, the training information, and the last one for supervisor.



The queries I would like to build would be to query a list to let each supervisor know which of their employees would need to be scheduled for a training course if it is to be renewed.
My mind is just boggled right now.

Any help on this would be appreciated!!!

I have attached the DB file to take a look at it.

[RayMilhon]

You need 1 additional table to link the employees with the Training. I recommend the following fields

EmployeeID to link to the employee
TrainingId to link to the Training table
TrainingDate Date the training was started.

These 3 fields would make up your primary key

Other fields as required
Depending on what you want to track.

[tmarik]

TrainingDate - which training date are you referring to? I wld like a date that the actual training took place, but then i need it to calculate when it would be renewed.

Example - CPR/FA is good for 2 years - so I have length set to 24, for 24 months.
So if they took the training on 12/10/11 - then they would have to renew it by 12/10/13.

I have added the 3rd table but add Supervisor, because I also need to query it by supervisor.

Just not sure what to do with the dates and if I did the date of length correctly.

[RayMilhon]

you would put the actual training date that was attended the renewal is calculated so does not need to be stored.

[tmarik]

I have added the query to calculate the date, but it is giving me a mismatch expression. I am not that good with Access or coding, would someone please take alook?
TY!

[RayMilhon]

Your Data Structure is all wrong.

Look at this structure

tblEmployees
EmployeeID Autonumber
Lname Text
fname Text
Dept Number
SupervisorID Number

TblSupervisors
SupervisorID Autonumber
Supervisorlname Text

tblDepartment
DeptID Autonumber
DeptName Text

training
TrainingID Numeric
EmployeeID Numeric
SupervisorID Numeric
TrainindDate Date/Time

tblTraining
TrainingID AutoNumber
TrainingDesc Text
TrainingLength Numeric

Here you have 2 options
Option 1 is all training is defined as the same time period based on the smallest offered For example training1 is in hours, Training2 is in days and training 3 is in weeks. Training 1 = 8 hours Traininglength = 8, Training 2 is 2 days TrainingLength = 16 hours and Training 3 is 1 week Traininglength = 40. You do not need any additional fields

Option2
Add a field called traininglenID

Add another table tblTraininglen

Traininglenid AutoNumber
TrainingLendesc Text

example

(1 = hours, 2 = days, 3 = Weeks, 4 = Months)

[tmarik]

I'm not sure you understood what I meant by training Length. I understand what youa re saying when you say Training 1 and then length = 8 hours. Meaning its 1 day long and will last 8 hours or if you do training 2 for 16, its 2 days long and will last 16 hours. This is the type of training we do and have to keep track of.

Agency Orientation - is 6 hours long and never expires
Dept Orientation - 4 hours long and never expires
Safety training - 3 hours long and needs to be renewed annually
Management training is 2 days for 12 hours total, and needs to be renewed annually
CPR is 4 hours long and needs to be renewed ever 2 years.

So Im not necessarily trying to figure out how many hours or days the training is - I need to be able to enter the date the gtraining was completed if it was over a time period and then by length I wanted to enter the number of years it would be renewed in to give me a renewal date.

Does that make more sense?

[RayMilhon]

Actually I wasn't quite clear enough either. What I meant was that you can use a specific time period and then make your times in that period. My example used hours and how you would convert days, months, weeks to hours. But I do understand what you're saying. The principle still holds though. you simply change the purpose of the field to renewal length and give it a period of years instead of hours. For those that do not need to be renewed make it 100 years. All others would be the actual # of years to renewal.

[tmarik]

I am getting more and more confused.
I'm not sure how the query should go to get the information I want.
I have popluated some fo the tables to get a better idea.

[RayMilhon]

SELECT tblEmployees.LName, tblEmployees.FName, tblEmployees.Dept, tblSupervisors.SupervisorLName, tblTraining.Training, tblTraining.Expires, Training.TrainingDate
FROM (tblSupervisors INNER JOIN (tblEmployees INNER JOIN Training ON (tblEmployees.[EmployeeID] = Training.[EmployeeID]) AND (tblEmployees.[Supervisor] = Training.[SupervisorID])) ON tblSupervisors.[SupervisorID] = Training.[SupervisorID]) INNER JOIN tblTraining ON Training.[TrainingID] = tblTraining.[TrainingID]
WHERE (((tblTraining.Expires)=DateAdd("""12""",[TrainingDate],[Expires])));

Your date Add function is wrong format is DateAdd(interval,Number,Date) In your query that would be =DateAdd("yyyy",[Expires],[Trainingdate])

also your Training table is empty and you're using an INNER JOIN so you will get 0 Records everytime

Do some reading on Inner and Outer Joins you will need both. To get the data you want.