Due Dates at Different Intervals

[Ron A.]

Hi all,

I have an Access 2007 database that tracks employee training. I would
like the due dates to auto populate based on the frequency of the training. I
thought about the DateAdd, but how could I do this for several training
events due at different intervals?

Thanks,

 

[Jeff Boyce]

If you know when it happened before and how long until the next time, why do
you need to store a "due date"?

That sounds like something you could use a query to calculate "on the
fly"...

(hint: storing a 'calculated value' like this is not a good design...)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

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)

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], 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.