well there is more then one way to approach this. fundamentally though it is impossible to query on something that isn't entered - - so given that - one approach would be to set up an AppendQuery that will add all the classes when you set up a new employee. But all those new training class records will have a blank in the ClassDate field to start.
So now when you go to report - you can pull all training records that have a blank ClassDate.
In your Classes table you probably want to add a field 'RequiredBy' and populate that with 30, 60, 90 as appropriate. This may help with sorting in some of your reports.
actually a school is one of the classic db exercises that one often sees when studying database design. the proper design is to have a table for 'students' have a table for 'classes' and then have a table - which is called the transaction table generically - but in your case something like 'training'. In your case it would be employees rather than students but the idea is the same.
[another classic example is a company with recurring customers. it has a 'products' table, a 'customer' table and then the transaction table typically called 'sales'.]
In form view you would have your main form sourced on the employees table and select an employee - Jim . You would have a subform sourced on the Training table. when in the subform you would select a class - and this field would be a lookup field sourced from the classes table.
You don't want to design with classes going left to right as columns - that will put you in a box as things change and make queries more difficult. It is call not normalized. You want to think thru the classic 3 table concept as this really holds the fundamental of the correct design for you.
I think that makes sense to me. So the table for classes is simply a list of the course names that will be a drop down in the training form and in the training form is where I will enter the completion dates for each class.
Is the training form where I would also put the "required by" date? If so, am I doing a calculation or expression or code in the form to get the 30, 60, 90 day date or is this done in a query?
Create a Training table with the following fields:
TrainingID (autonumber) - primary key
ReportBasicUserID (number) - links to main employee table/form
ClassID (number) - links to class listing table
ClassRequiredBy (date/time) - to calculate 30, 60, 90 day from HireDate
ClassCompletion (date/time) - to be entered upon completion
Create a Class table with the following fields:
I am very confused on how to link the hiredate (from employee table) to the requiredby date (from training table). I've attached what I've done so far. Is this close??
Somehow I will need to figure out how to have all the mandatory training classes automatically add upon entering a new employee and then automatically enter the "requiredby" date in the next column. Is this possible?
I would put the 30,60,90 in the Class table as the 'RequiredBy' field. When you select the class you thereby select that number too - which you can use to add to the HiredDate value.
You would be well served to browse around for an Access text book - easily found at Amazon or a large book store - - some of them besides having all the basic instruction may well have very similar examples to what you are doing.