Creating automatic dates for renewal from 2 possible fields

  • Thread starter Thread starter Beech
  • Start date Start date
B

Beech

I am setting up a training database. I am trying to create a query from
which to run a report that will show 3 columns next to staff names; one will
show the date the training was completed, the second will show the date the
refresher course was completed and I would like the third colum to calculate
the date the training needs to be renewed. So the final column should show
the date the refersher course was completed plus 36 months, unless the
refresher course column is blank in which case it should show 36 months from
the date the training was completed (first column). Can anyone help me with
creating the calcualtions?

Thank you
 
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
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))
 
Save yourself some work. There's no need to store the third date, since
you've given the 'formula' for calculating it.

Use that formula in a query and generate it as-needed.

Regards

Jeff Boyce
Microsoft Access MVP
 
Back
Top