Calculation based on multiple fields in unbound field

[kelkan]

I have a database regarding employee training. On a form, I want to have an unbound text field calculate Yes or No based on whether or not a certification is required or not. The criteria is based on employee classification (not all require every certification), certification type (there are several including CPR, TOC, etc), and expiration date (some expire yearly and some every other year). The form is based on a table called tblTraining. The fields on the form are:



EMPLOYEE (BOUND TO TABLE
CERTIFICATION (BOUND TO TABLE)
EXPIRATIONDATE (BOUND TO TABLE)
REQUIRED (UNBOUND)

Suggestions?

[jzwp11]

Where do you store the classification of each employee? Where do you store the certifications? How do you relate the necessary certification for each classification? Also, where do you store the expiration frequency for a given certification?

[kelkan]

Classification is stored in tblEmployees, Certifications is stored in tblTraining, Expiration is stored in tblTraining. PK in tblEmployees is EMPLOYEEID and PK for tblTraining is TRAININGID. EMPLOYEEID is FK in tblTraining. Frequency is stored in tblCourses with PK of COURSEID with FK of COURSEID in tblTraining.

[jzwp11]

...classification (not all require every certification)

Perhaps I am misinterpreting something, but it sounds like the classification (not the employee) is what requires particular certifications, is that correct? If so, what table defines which certifications are required for each classification?

Do you need a table like this?

tblClassificationCertifications
-pkClassCertID primary key, autonumber
-fkClassificationID foreign key to a table that holds all classifications
-TrainingID foreign key to tblTraining



Going back to your original question, if you have the expiration date of the certification as you indicated by the controls on the form wouldn't the required value be "No" as long as the current date is less than the expiration date?

EMPLOYEE (BOUND TO TABLE
CERTIFICATION (BOUND TO TABLE)
EXPIRATIONDATE (BOUND TO TABLE)
REQUIRED (UNBOUND)

[kelkan]

I hadn't thought to put the classifications into a separate table. I originally had the certifications in a separate table but was having difficulty getting other functions to work across so many tables (3, sometimes 4 tables with the data I needed). I combined the certifications so I could make other things easier. I was down to just a few things to figure out with my database and it seemed easier when I combined tblTraining and tblCertifications.

As far as the other part, I guess I need to add more info to what it is I wanted to do...I want to project training for the calendar year. We have class dates available 6 months in advance, and I wanted to be able to query based on who was going to need training in the calendar year so they could be notified/scheduled in advance. Therefore, if the expiration date is 6/10/2013, I could schedule them now for a class just before their expiration. If the Yes/No only worked for those already expired, I wouldn't have a quick reference for that future info.

[jzwp11]

was having difficulty getting other functions to work across so many tables (3, sometimes 4 tables with the data I needed

Typically you would use a query to bring the information you need together.

As far as the other part, I guess I need to add more info to what it is I wanted to do...I want to project training for the calendar year. We have class dates available 6 months in advance, and I wanted to be able to query based on who was going to need training in the calendar year so they could be notified/scheduled in advance. Therefore, if the expiration date is 6/10/2013, I could schedule them now for a class just before their expiration. If the Yes/No only worked for those already expired, I wouldn't have a quick reference for that future info.

This can be done provided you have the information in your tables.

I combined tblTraining and tblCertifications

If training and certification are two different things (but probably related) they should be listed in separate tables, and you would probably relate them in a third table. For example, a certification may require 3 or for training courses.