Database for tracking employee training and expiry per training

You need:

Table for employees

Table for courses

Table for specific course (with a date)

Table to combine the specific course with employee (attend).

An employeeiID and specific-courseID is then added to tblAttend to hold the relationship of 1 employee attending 1 specific course. Those 2 make up the combined primary-key for that table so the same employee cannot be registered twice for the same specific course.

The information in the specific course-tabel (attend) holds every information you can think of for what will happen on that specific course that specific date (like instructor)

You then need to create an employee-form, a course-form and a specific-course form with a subform for the attend-table - linked with the courseID through the link-fields (you find it under Data in properties for the subform). You set the name of the ID in the main-form and the name of the ID in the subform.

The mainform is where you register a specific course on a specific date and the subform is where you select employees (using a dropdown-control). Attend is then filled with courseID through the link-fields and employeeID by your dropdown and if needed any other information in the attend-table which is limited to information that has to do with an employees attendance to a course on a specific date.

When all this is playing you can start thinking about what to get out of this, but now things are much easier. E.g. create a query with all the tables and count how many attend-records that employee has...which tells you how many times he/she has attended. Us that query as a source for a report and you can show each employees attendance...and attendance divided on number of courses in % will show the frequency of attendance.

A general rule is never save data in a database which is summed up somehow. Always calculate when you need it...or else you can end up with a situation where the number has not been updated and thereby is wrong....

Good luck! You have a lot of fun and frustration waiting for you :)

Start out by designing your table structure. Here an example of what you might need.

Training Table: Lists all require/optional training available

Frequency Table: Lists all possible training frequencies/intervals

Employee Table: Lists all company employees or otherwise need to have the training

Employee Training Table: Cross reference to show which employee completed which training

Hope that helps...

Ok, first there are some problems with your table structure. Its a good start but there are some issues.

You have DepartmentName in both the employee table and the Training table. Do you need to record the training for the Department the employee is currently in, though it might change? That would be the only valid reason for having it both tables.

Your object naming is inconsistent. You use mostly Camel case (as in FirstName) but also use spaces and underscores. I would stick to Camel Case.

I don't understand the need for 2 tables Course List and FrequencyList. You should only need one Courses table with the Frequency stored as a number of months (if the minimum frequency is a year, then it can be the number of years).

You also don't need a Next Renewal. This can be calculated by adding the Frequency to the Course Taken Date.

So you would then use a query and report to display the Status. The query would calculate the expiry date using the Expression:

DateAdd("m", [Frequency], [Date Course Taken])

i guess you want to query some information from your database tables.

if so, privide your source tables and result tables you want with both fields and data in excel sheets or screen shot may helpful to get your situation.

if possible all source information in one table will avoid selecting join .