Complicated Query (well for me!)

[JMK]

Hi All,

I thought I had this figured out from a post I found when searching, written
by Dale Fye from 12/29/2004 (called Help on a complicated query!?).

The problem I have is the same basic problem, I need to have Access
calculate the expiry date for an employees training. My problem is that I
have 16 exams, with 5 different expiry timelines - for example training0 may
never expire, training1 could expire on the 1st of the 13th month, training2
could expire on the 1st of the 24th month, so on and so on.

At first I thought I had it with the formula
DateSerial(Year[Date]+1,Month[Date]+1,1), and then also tried the SQL query
in the post referred to above - my problem being that it was coding all
training as having that expiry timeline. I have thought of just creating a
query which calculates all the different timelines for each training type,
and then just picking which one I like, but there has got to be an easier way.

Please help!

 

[Duane Hookom]

Do you have a value stored in a table that represents the expiration period
related to each training? If not, you need to store this information so that
you can link it in and perform the calcs.

 

[John W. Vinson]

Hi All,

I thought I had this figured out from a post I found when searching, written
by Dale Fye from 12/29/2004 (called Help on a complicated query!?).

The problem I have is the same basic problem, I need to have Access
calculate the expiry date for an employees training. My problem is that I
have 16 exams, with 5 different expiry timelines - for example training0 may
never expire, training1 could expire on the 1st of the 13th month, training2
could expire on the 1st of the 24th month, so on and so on.

At first I thought I had it with the formula
DateSerial(Year[Date]+1,Month[Date]+1,1), and then also tried the SQL query
in the post referred to above - my problem being that it was coding all
training as having that expiry timeline. I have thought of just creating a
query which calculates all the different timelines for each training type,
and then just picking which one I like, but there has got to be an easier way.

Please help!

First off your syntax is wrong: if you have a datefield named Date (which is a
Bad idea as it is a reserved word) you would need Year([Date]). Year[Date] is
simply going to generate an error.

That said... it all starts with the data. How are your tables structured?
where in your tables (if anywhere) are the expiry dates stored, and how?

 

[JMK]

Good Morning John and Duane,

Thank you for the quick replys.

The information is currently stored in a field called "Expiry". The data
within is numbered, ie 12 for 12 months, 13 for 13 months. It does not,
however indicate if the 12 is to indicate the 1st of the 12th month or the
same date within the 12th month forward, which is what I would like to be
able to distinguish between. They are stored as Integers.

As for the Date Field, I have renamed it DateWritten.

Thanks!

Hi All,

I thought I had this figured out from a post I found when searching, written
by Dale Fye from 12/29/2004 (called Help on a complicated query!?).

The problem I have is the same basic problem, I need to have Access
calculate the expiry date for an employees training. My problem is that I
have 16 exams, with 5 different expiry timelines - for example training0 may
never expire, training1 could expire on the 1st of the 13th month, training2
could expire on the 1st of the 24th month, so on and so on.

At first I thought I had it with the formula
DateSerial(Year[Date]+1,Month[Date]+1,1), and then also tried the SQL query
in the post referred to above - my problem being that it was coding all
training as having that expiry timeline. I have thought of just creating a
query which calculates all the different timelines for each training type,
and then just picking which one I like, but there has got to be an easier way.

Please help!

First off your syntax is wrong: if you have a datefield named Date (which is a
Bad idea as it is a reserved word) you would need Year([Date]). Year[Date] is
simply going to generate an error.

That said... it all starts with the data. How are your tables structured?
where in your tables (if anywhere) are the expiry dates stored, and how?

 

[Duane Hookom]

As both John and I advocate, you need to store information in your tables
that provide the values needed in your calculations. Find the best place to
store the values and use it. You should maintain data, not code or
expressions.

--
Duane Hookom
Microsoft Access MVP

Good Morning John and Duane,

Thank you for the quick replys.

The information is currently stored in a field called "Expiry". The data
within is numbered, ie 12 for 12 months, 13 for 13 months. It does not,
however indicate if the 12 is to indicate the 1st of the 12th month or the
same date within the 12th month forward, which is what I would like to be
able to distinguish between. They are stored as Integers.

As for the Date Field, I have renamed it DateWritten.

Thanks!

Hi All,

I thought I had this figured out from a post I found when searching, written
by Dale Fye from 12/29/2004 (called Help on a complicated query!?).

The problem I have is the same basic problem, I need to have Access
calculate the expiry date for an employees training. My problem is that I
have 16 exams, with 5 different expiry timelines - for example training0 may
never expire, training1 could expire on the 1st of the 13th month, training2
could expire on the 1st of the 24th month, so on and so on.

At first I thought I had it with the formula
DateSerial(Year[Date]+1,Month[Date]+1,1), and then also tried the SQL query
in the post referred to above - my problem being that it was coding all
training as having that expiry timeline. I have thought of just creating a
query which calculates all the different timelines for each training type,
and then just picking which one I like, but there has got to be an easier way.

Please help!

First off your syntax is wrong: if you have a datefield named Date (which is a
Bad idea as it is a reserved word) you would need Year([Date]). Year[Date] is
simply going to generate an error.

That said... it all starts with the data. How are your tables structured?
where in your tables (if anywhere) are the expiry dates stored, and how?

 

[John W. Vinson]

Good Morning John and Duane,

Thank you for the quick replys.

The information is currently stored in a field called "Expiry". The data
within is numbered, ie 12 for 12 months, 13 for 13 months. It does not,
however indicate if the 12 is to indicate the 1st of the 12th month or the
same date within the 12th month forward, which is what I would like to be
able to distinguish between. They are stored as Integers.

Ok... so (as far as I can see) the information that one renewal is on the
first of the month and a different renewal is on the 15th of the month is not
stored in your database at all! How can Access possibly figure it out if the
information does not exist in the database? You clearly need another field!