Training Database

[Del_Piero_3]

Hi guys,

Lab staffs are required to undergo 3 day training course within every 3 year period. The course options are 1 day course, 2 day course and 3 day course.

The course can be taken as:

3 continues days within 3 years
1 day course taken 3 times individually anytime within 3 years
2 continues days (meaning 1 more day required within 3 years to complete training)

The 3 year period starts again from the date when 3 days have been completed.

I.e. if the staff has completed 2 individual days in the 1st year than that mean 1 more day is required within the last 2 years to complete the training. However if the staff instead does a 2 continues days in the last 2 years then the 1st day of 2 continues days will complete course requirements and renew the 3 year period.

I have done the basics (see attachment, tables, relationship, forms) and now I need to implement the above feature but not sure how I am going to achieve this. I was thinking of creating a report that would list all staff that requires training or is expiring in near future. I would like to print letters to those labs where their staffs have failed to meet the training requirements.

Can someone give me some guidelines/hints/tips on how to achieve the above.

Sorry if this is confusing or if I haven’t explained it properly.

Thanks

 

[Del_Piero_3]

Anyone??? come on big guns.......help me out

 

[Pat Hartman]

Is each course always only 1 day. If not, you need to add the number of days to the course table so you know how to count this course.

You need a certified date or something to indicate the completion of the three courses. You can then count the number of courses or sum the number of days taken since the last completion date to determine if a new certification is due. If they haven't taken three days since the last certification date and the current date is more than three years past the last certification date, you can inform people that they are behind in their testing.

 

[Del_Piero_3]

Is each course always only 1 day. If not, you need to add the number of days to the course table so you know how to count this course.

You need a certified date or something to indicate the completion of the three courses. You can then count the number of courses or sum the number of days taken since the last completion date to determine if a new certification is due. If they haven't taken three days since the last certification date and the current date is more than three years past the last certification date, you can inform people that they are behind in their testing.

Hi Pat, thanks for the reply...

It is a single course which is duration of 3 days.....staff dont have to do 3 continues days to complete course instead they have the option of spreading the days like: 1 day, 2 continues days, 3 continues days. It doesnt matter which way they decide to complete training as long as they have done 3 full days within 3 years.

Ie.

1yr....................2yr....................3yr

1day.................1day.................1day - training complete

3 days - training complete in 1yr so new 3 yr period starts from last day

1day.................2day - training complete in the 2nd yr

2day.................2day - training complete, done 4 days so 3rd is where the next 3 yr period starts again and the extra day done counts towards this new period.

I hope this is bit more clearer....sorry

 

[Pat Hartman]

If there is a record in the training log for each day and you add a certification date to the staff table, then your query would look something like:

Return list of non-certified people:
Select t.StaffID, t.CourseID, s.CertificationDate Count(*) As DayCount
From TrainingLog as t Inner Join StaffDetails as s ON t.StaffID = s.StaffID
Where t.CourseDate > s.CertificationDate
Group by t.StaffID, t.CourseID, s.CertificationDate
Having Count(*) < 3;