On Wed, 20 Jun 2007 02:41:51 -0700,
dy****@crosslan dtankers.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)
tEmployeeTraini ng
---------------------------
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 tEmployeeTraini ng, you'd use tTraining.dRene walPeriod 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.dRene walPeriod to the value stored in tEmployeeTraini ng.dDateOfTrain ing, 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.co m www.infotrakker.com 
