Training reminder database

[woodgie]

Hi all,

I know this subject probably comes up alot but after searching through the forum and google I'm unable to find the answers I'm looking for.

What I'm trying to achieve:

I have created a table which contains name of staff member and position, their email address and the email address of their training coordinator.

I've also created a table containing training providers, contact details and links to relevant websites/documents. Linked to these records via lookup using the ProviderID, I've created a table containing details of courses available, their validity period (months) and a field to say if the course is mandatory.

I've then created a table which called employeetraining in which I link the course taken using the CourseID and the employee using employeeID. I've also added a field which shows the date the course was taken.

Now I'm stuck

What I want to be able to do is for access to use these details and check daily and see if training needs to be renewed within the next 90 days, if so, I'd like it to send an email to both the training coordinator and the employee to warn them to apply for training for that particular course. I'd then like it to do the same at 60 and 30 days before training expiry.

Any help would be gratefully received.

Many thanks

Paul

 

[Mr Smin]

If you create a query joining the employeetraining records to courses and have a column with a date function you can get pretty close.

employeetraining.empID, emptrg.CourseID, emptrg.CourseDate, course.validperiod,
MonthsElapsedSinceTrained: ((Date()-emptrg.CourseDate)/30)

the /30 is to roughly convert 'days since trained' into months, then adjust the criteria to restrict to records where Months elapsed is approaching course.validperiod

 

[woodgie]

OK,

so I have created the query you suggested to tell me how long has elapsed since training was taken. I've then created a further query which calculates the difference between the the elapsed time and the course validity period.

 

[woodgie]

Is anybody able to help further?