Tracking Recurring (Yearly) Training - Table?

[rolandrav]

Good day all! This is my first post am I am excited to be a part of this community. I have been teaching myself Access, so am sure there is an easier way to accomplish this all.:banghead:

Here is my dilemma. I am trying to track a yearly training in Access 2010 but am not quite sure how to accomplish it. The training originally was just a one-time thing, due within two weeks of a new hire's start date so I currently have it displayed as three fields in my employee table (Due Date [Calculated], Completed [Date/Time], Paid [Yes/No]). Now, employees will be required to complete this training every year (beginning January 1st, not from employee's start date) and I don't know how to accomplish the tracking it.

I need to keep each year's completion date and whether or not the employee was paid. Currently, the "Paid" field is linked to a query that populates a list of employees that have not completed the training. If the "Paid" Yes/No box is unchecked, the employee's name will be listed in the query; once I check the Yes/No box then the employee's name is removed.

What would be the easiest way to track this training for each year? There are several other trainings that I am tracking but are one-time only events; these too are separate fields in my Employee table.

If anyone can help me out, I would be forever grateful! I am a novice to be sure, but will try to answer any questions to the best of my abilities. THANKS!

 

[Old Man Devin]

Here is my suggestion:
You could start a new table called 'YearlyTraining', and at the beginning of each year you run an append query that adds all of your staff to this table, along with the year it's for e.g. a field called TrainingYear is set to the current year. The table could have the due date and date completed information just like your initial hire training fields.

Then you could make a query that looks at all the incomplete yearly training records where TrainingYear=Year(Date()) i.e. all the ones for this year, showing you who needs to do their yearly training, and then you tick off complete for that year's entry when done. After that the information will still be there in the table but not in the query. Once the next year comes around just append all the staff again with the TrainingYear field moved forward by one, and process can be repeated.

This way you'll have a log of all the past training available in that table, and also be able to narrow things down to see what needs to be done now via the query. You could probably even set your database to check the date when it opens, and if it's a new year that isn't already in the table, run the append query to get all the staff in there or the new year. Avoids the issue of having to remember to add people to the list each year!

Let me know if you have questions on the specifics of this suggestion!