Adding a set number of days to a given date based on criteria in another field

[dympna]

Hi can anyone suggest a fix for this... as I am a novice in access.

I have created a training table with the following fields

Employee Name - joe
Training Received - Fork lift
Date Received - 20/6/07
Certificate - yes

What I want to create is a renewal date for this training, there are
numerous training courses being delivered so the data in the training
received field will vary.
So the renewal date will vary also as some training will be renewed in
1 years time or 6mths or 2 years time, but this is purely based on the
type of training received.
Is there a way that this can be calculated in a query so that i can
then point a form to this query and when the information is input for
a new batch of training then it will give me the renewal date based on
what has been input inthe date received field and the training
received field.

Is this very complex, as I am finding it very difficult.
Any help would be much appreciated

[Scott McDaniel]

On Wed, 20 Jun 2007 02:41:51 -0700, dy****@crosslandtankers.com wrote:

>Hi can anyone suggest a fix for this... as I am a novice in access.

I have created a training table with the following fields

Employee Name - joe
Training Received - Fork lift
Date Received - 20/6/07
Certificate - yes

What I want to create is a renewal date for this training, there are
numerous training courses being delivered so the data in the training
received field will vary.
So the renewal date will vary also as some training will be renewed in
1 years time or 6mths or 2 years time, but this is purely based on the
type of training received.
Is there a way that this can be calculated in a query so that i can
then point a form to this query and when the information is input for
a new batch of training then it will give me the renewal date based on
what has been input inthe date received field and the training
received field.

You can use Access functions in a query, but it seems to me that your Renewal date will be different based on the type
of training received ... in this case, you'd probably need to store the Renewal date somewhere. I don't know what your
database strucutre is, but basically you'd store it in the same table where you'd store the training + Employee info ...
I'd presume you have something like this:

tEmployees
------------------
lEmployeeID [PK]
sFirstName
sLastName
sEmpNumber
etc

tTraining
---------------
lTrainingID [PK]
sDescription
dRenewalPeriod (i.e. # of months for renewal)

tEmployeeTraining
---------------------------
lEmployeeID
lTrainingID
dDateOfTraining (stores the date training completed)
dRenewalDate (stores the date the renewal is needed)

From this point, when you add a new record to tEmployeeTraining, you'd use tTraining.dRenewalPeriod to calculate the
value to store in dRenewalDate. You'd then build a query that joins the three tables above to display your data and show
what employees have taken various Training courses, and when their Renewal dates for those training courses are.

IN general, it's not a good idea to store calculated values (the dRenewalDate) since you could easily calculate the
Renewal date by adding the tTraining.dRenewalPeriod to the value stored in tEmployeeTraining.dDateOfTraining, but in my
experience these things tend to be somewhat fluid ... for example, this year the company may require that all Employees
be retrained on the forklift every 2 years, but thier insurance company may come in and audit the training and require
that forklift operaters be retrained every 6 months ... in this case, if you change the dRenewalPeriod to 6, you'd
invalidate every legacy record that depended on that value. This may not be applicable in your case, and you may need a
different set of tables and/or relationships.

>
Is this very complex, as I am finding it very difficult.
Any help would be much appreciated

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

[dympna]

On 20 Jun, 11:23, Scott McDaniel <scott@NoSpam_Infotrakker.comwrote:

On Wed, 20 Jun 2007 02:41:51 -0700, [e-mail] wrote:

Hi can anyone suggest a fix for this... as I am a novice in access.

I have created a training table with the following fields

Employee Name - joe
Training Received - Fork lift
Date Received - 20/6/07
Certificate - yes

What I want to create is a renewal date for this training, there are
numerous training courses being delivered so the data in the training
received field will vary.
So the renewal date will vary also as some training will be renewed in
1 years time or 6mths or 2 years time, but this is purely based on the
type of training received.
Is there a way that this can be calculated in a query so that i can
then point a form to this query and when the information is input for
a new batch of training then it will give me the renewal date based on
what has been input inthe date received field and the training
received field.

You can use Access functions in a query, but it seems to me that your Renewal date will be different based on the type
of training received ... in this case, you'd probably need to store the Renewal date somewhere. I don't know what your
database strucutre is, but basically you'd store it in the same table where you'd store the training + Employee info ...
I'd presume you have something like this:

tEmployees
------------------
lEmployeeID [PK]
sFirstName
sLastName
sEmpNumber
etc

tTraining
---------------
lTrainingID [PK]
sDescription
dRenewalPeriod (i.e. # of months for renewal)

tEmployeeTraining
---------------------------
lEmployeeID
lTrainingID
dDateOfTraining (stores the date training completed)
dRenewalDate (stores the date the renewal is needed)

From this point, when you add a new record to tEmployeeTraining, you'd use tTraining.dRenewalPeriod to calculate the
value to store in dRenewalDate. You'd then build a query that joins the three tables above to display your data and show
what employees have taken various Training courses, and when their Renewal dates for those training courses are.

IN general, it's not a good idea to store calculated values (the dRenewalDate) since you could easily calculate the
Renewal date by adding the tTraining.dRenewalPeriod to the value stored in tEmployeeTraining.dDateOfTraining, but in my
experience these things tend to be somewhat fluid ... for example, this year the company may require that all Employees
be retrained on the forklift every 2 years, but thier insurance company may come in and audit the training and require
that forklift operaters be retrained every 6 months ... in this case, if you change the dRenewalPeriod to 6, you'd
invalidate every legacy record that depended on that value. This may not be applicable in your case, and you may need a
different set of tables and/or relationships.

Is this very complex, as I am finding it very difficult.

Any help would be much appreciated

Scott McDaniel
scott@takemeout_infotrakker.comwww.infotrakker.com- Hide quoted text -

- Show quoted text -

I am sorry but I don seem to be fully understanding what you have
explained.
Do I create a new field in the employee training table called renewal
date? if so how do I get it to add the renewal period field to the
date of training or do I do this in a query and if so how...

Sorry...any detail would be much appreciated.