Date Calculation

[JMK]

Good Afternoon,

I have a DB which tracks training of employees. The grace periods allowed
with the training is that new training can be completed within 90 days of the
expiry date without changing the anniversary date (e.g. the training is due
on 1 April 2010, the employee conducts the training on 2 January 2010 but
gets to keep the 1 April anniversary date).

The table I am working with is mainly based on the date of training and the
training type (which determines whether the training expires on the 1st of
the 13th, 25th or 37th months or if it keeps the same date); what I would
like is for the end user to input the data into a form, have it autocalculate
the expiry date on the form so they can verify the information and then once
the form is closed, have this information fed into the table so that we can
print training reports for the managers.

Im not versed in VB and am reaching my limit of understanding with queries,
so any help is graciously appreciated.

Thanks in advance

 

[KARL DEWEY]

You might find this helpful --
How do you have your tables setup? Maybe like this --
Employee --
EmpID - Autonumber - primary key
LName - text
FName - text
MI - text
HireDate - DateTime
Depart - DateTime
....etc.

Training --
CourseID - Autonumber - primary key
Title - text
Period - text (m- Monthly, q- Quarterly, yyyy- Yearly)
ReCur - Number - long integer
ReqdBy - text (OSHA 1910, HR Manual 5.8.3, Finance 4.23.1)
Grace - Number - long integer – number of days grace period allowed

EmpTraining --
EmpID - number - long integer
CourseID - number - long integer
TngDue - DateTime
CompDate - DateTime
Pass - Yes/No

Employee.Depart Is Null AND EmpTraining.Max(CompDate) AND EmpTraining.Pass =
-1

Next_Training_Required: DateAdd([Period], [ReCur], IIF(Abs(DateDiff(“dâ€,
Max(CompDate), Max(TngDue))) <= Grace, Max(TngDue), Max(CompDate)))

You could append training due records following update of any due record
using the two fields TngDue and CompDate. Then run query with duedate
descending to show all next training.

Here are two post of mine on maintenace that might apply to training --
You need a ServiceReq table listing the services and interval. Then a
VehicleSvcReq the has vehicle ID, ServiceReq, Method. Method is whether
next service is the last schedule plus interval or last completion plus
interval.
The interval needs to be the lowest common denominator of all services such
as weeks if any one of the services is to be accomplished on a weekly basis -
bi-weekly - monthly - quarterly. All intervals will be multiples of the
selected interval. If fluid checks are weekly and oil change every three
months then oil change would be interval 13 - 13 weeks.
----------------------- ---------------
In a Task table have a field indicating interval number for the maintenance.
Use the lowest common denominator such as weeks, months or quarters. If you
can not make it work with the lowest common denominator then use two fields,
one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.