473,289 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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

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

Jun 20 '07 #1
2 2539
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
Jun 20 '07 #2
On 20 Jun, 11:23, Scott McDaniel <scott@NoSpam_Infotrakker.comwrote:
On Wed, 20 Jun 2007 02:41:51 -0700, dym...@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.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.

Jun 29 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Rimdur | last post by:
I am trying to get a difference between two dates calculated in a query. I have Sales_Date as the first one, and Date() as the second one. How would I put the difference of these two days into a...
2
by: dixie | last post by:
I have a report which is printed daily. It is a list of people. A person can be put on this list for one day, which is easyily achieved from a simple form. My problem is that a person can also...
6
by: Robin S. | last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope I've done a better job of explaining myself below. I am trying to produce a form to add products to a table (new...
12
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date...
1
by: KEVIN97810 | last post by:
hello to all, I have a date field in my table (last, first, address, contact, Date) and i want to write a select statement example based on the date field. Give me 30 days, 30 to 90, 90 to 100...
29
by: james | last post by:
I have a problem that at first glance seems not that hard to figure out. But, so far, the answer has escaped me. I have an old database file that has the date(s) stored in it as number of days. An...
1
damonreid
by: damonreid | last post by:
Access 2003 Microsoft Windows XP Pro Hey, I am currently pulling my hair out here. I have a Form for adding new projects to a database, the only problem is that when I close the form it doesn't...
2
by: srusskinyon | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for...
1
by: swethak | last post by:
Hi, I am desiging the calendar application for that purpose i used the below code. But it is for only displys calendar. And also i want to add the events to calendar. In that code displys the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.