Calculated Field

[NoviceIan]

Hi,

We have a database which keeps track of staff training. We have three
tables staff, course and training. The course tabvle stores details of the
individual courses and their compliancy periods.

Course

Course #
Course Name
Compliancy Period

Training

Attended #
Staff #
Date Completed
Compliant Until

The training table stores details of the staffs attendence or participation
in training. The compliancy period is currently a long integer which is
measured in months.

What I would like to do is create a automated field which will indicate
compliant or expired based on the date the course was attended and the
compliancy period of the course.

I do not know where to start. Please could somebody help me.

Many thanks

Ian

 

[Allen Browne]

Create a query.
Add both tables.


Type this into a fresh column in the Field row:
Expiry: DateAdd("m", [Compliancy Period], [DateCompleted])

Add whatever criteria you wish below this calculated field.

 

[NoviceIan]

Thanks I tested your criteria and it works. However i have two questions.

The first is that not all courses are compliancy based some are just one
off. With the code you give me the fields displays Error if the course is
not compiancy based. Is there any way to just leave the field blank if this
is the case?

The second is would I be able to specify this code in the table design in
order to basically replace the Compliant Until field?

Many thanks

Ian

Create a query.
Add both tables.


Type this into a fresh column in the Field row:
Expiry: DateAdd("m", [Compliancy Period], [DateCompleted])

Add whatever criteria you wish below this calculated field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

We have a database which keeps track of staff training. We have three
tables staff, course and training. The course tabvle stores details of
the
individual courses and their compliancy periods.

Course

Course #
Course Name
Compliancy Period

Training

Attended #
Staff #
Date Completed
Compliant Until

The training table stores details of the staffs attendence or
participation
in training. The compliancy period is currently a long integer which is
measured in months.

What I would like to do is create a automated field which will indicate
compliant or expired based on the date the course was attended and the
compliancy period of the course.

I do not know where to start. Please could somebody help me.

Many thanks

Ian

 

[Allen Browne]

How do you determine that the course does not need repeating? Is the
[Compliancy Period] null?

If so, you could use an IIf() expression and test if the field is Null. I
recommend you now wrap the expression in CVDate() to help poor ol' JET 4
understand what's meant to be returned:
Expiry: CVDate(IIf(Compliancy Period] Is Null, DateAdd("m", [Compliancy
Period], [DateCompleted]))

You could use an Update query (Update on Query menu, in query design), and
put the expression in the Update row under the field you want to update. But
I recommend that you do not do this, unless you have a need to be able to
modify individual cases so that a person gets an extended period or a
reduced period. If you will not be doing that (and I suspect that would be
unusual), then storing the calculated value breaks one of the most basic
rules of data normalization - don't store dependent values - and you are
creating a maintenance nightmare for yourself. Why not let Access do all the
hard work instead of taking that on yourself? You can use the query with the
calculated date in any context where you could use the table, and you never
have to worry about whether you have handled every possible situation where
the data could be changed to ensure your stored calculated values are
correct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks I tested your criteria and it works. However i have two questions.

The first is that not all courses are compliancy based some are just one
off. With the code you give me the fields displays Error if the course is
not compiancy based. Is there any way to just leave the field blank if
this
is the case?

The second is would I be able to specify this code in the table design in
order to basically replace the Compliant Until field?

Many thanks

Ian

Create a query.
Add both tables.


Type this into a fresh column in the Field row:
Expiry: DateAdd("m", [Compliancy Period], [DateCompleted])

Add whatever criteria you wish below this calculated field.

We have a database which keeps track of staff training. We have three
tables staff, course and training. The course tabvle stores details of
the
individual courses and their compliancy periods.

Course

Course #
Course Name
Compliancy Period

Training

Attended #
Staff #
Date Completed
Compliant Until

The training table stores details of the staffs attendence or
participation
in training. The compliancy period is currently a long integer which
is
measured in months.

What I would like to do is create a automated field which will indicate
compliant or expired based on the date the course was attended and the
compliancy period of the course.

 

[NoviceIan]

Hi again,

I tried your amended code and it keeps stopping on the first Compliancy
Period stating: The expression contains invalid syntax.

I see your point about the calculated fields and I will gratefully take your
advice thanks very much.

Ian

How do you determine that the course does not need repeating? Is the
[Compliancy Period] null?

If so, you could use an IIf() expression and test if the field is Null. I
recommend you now wrap the expression in CVDate() to help poor ol' JET 4
understand what's meant to be returned:
Expiry: CVDate(IIf(Compliancy Period] Is Null, DateAdd("m", [Compliancy
Period], [DateCompleted]))

You could use an Update query (Update on Query menu, in query design), and
put the expression in the Update row under the field you want to update. But
I recommend that you do not do this, unless you have a need to be able to
modify individual cases so that a person gets an extended period or a
reduced period. If you will not be doing that (and I suspect that would be
unusual), then storing the calculated value breaks one of the most basic
rules of data normalization - don't store dependent values - and you are
creating a maintenance nightmare for yourself. Why not let Access do all the
hard work instead of taking that on yourself? You can use the query with the
calculated date in any context where you could use the table, and you never
have to worry about whether you have handled every possible situation where
the data could be changed to ensure your stored calculated values are
correct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks I tested your criteria and it works. However i have two questions.

The first is that not all courses are compliancy based some are just one
off. With the code you give me the fields displays Error if the course is
not compiancy based. Is there any way to just leave the field blank if
this
is the case?

The second is would I be able to specify this code in the table design in
order to basically replace the Compliant Until field?

Many thanks

Ian

Create a query.
Add both tables.


Type this into a fresh column in the Field row:
Expiry: DateAdd("m", [Compliancy Period], [DateCompleted])

Add whatever criteria you wish below this calculated field.


We have a database which keeps track of staff training. We have three
tables staff, course and training. The course tabvle stores details of
the
individual courses and their compliancy periods.

Course

Course #
Course Name
Compliancy Period

Training

Attended #
Staff #
Date Completed
Compliant Until

The training table stores details of the staffs attendence or
participation
in training. The compliancy period is currently a long integer which
is
measured in months.

What I would like to do is create a automated field which will indicate
compliant or expired based on the date the course was attended and the
compliancy period of the course.

 

[Allen Browne]

Bracketing wasn't right. This is all one line:

Expiry: CVDate(IIf(IsNull([Compliancy Period]), Null,
DateAdd("m", [Compliancy Period], [DateCompleted])))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi again,

I tried your amended code and it keeps stopping on the first Compliancy
Period stating: The expression contains invalid syntax.

I see your point about the calculated fields and I will gratefully take
your
advice thanks very much.

Ian

How do you determine that the course does not need repeating? Is the
[Compliancy Period] null?

If so, you could use an IIf() expression and test if the field is Null. I
recommend you now wrap the expression in CVDate() to help poor ol' JET 4
understand what's meant to be returned:
Expiry: CVDate(IIf(Compliancy Period] Is Null, DateAdd("m",
[Compliancy
Period], [DateCompleted]))

You could use an Update query (Update on Query menu, in query design),
and
put the expression in the Update row under the field you want to update.
But
I recommend that you do not do this, unless you have a need to be able to
modify individual cases so that a person gets an extended period or a
reduced period. If you will not be doing that (and I suspect that would
be
unusual), then storing the calculated value breaks one of the most basic
rules of data normalization - don't store dependent values - and you are
creating a maintenance nightmare for yourself. Why not let Access do all
the
hard work instead of taking that on yourself? You can use the query with
the
calculated date in any context where you could use the table, and you
never
have to worry about whether you have handled every possible situation
where
the data could be changed to ensure your stored calculated values are
correct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks I tested your criteria and it works. However i have two
questions.

The first is that not all courses are compliancy based some are just
one
off. With the code you give me the fields displays Error if the course
is
not compiancy based. Is there any way to just leave the field blank if
this
is the case?

The second is would I be able to specify this code in the table design
in
order to basically replace the Compliant Until field?

Many thanks

Ian

:

Create a query.
Add both tables.


Type this into a fresh column in the Field row:
Expiry: DateAdd("m", [Compliancy Period], [DateCompleted])

Add whatever criteria you wish below this calculated field.


We have a database which keeps track of staff training. We have
three
tables staff, course and training. The course tabvle stores details
of
the
individual courses and their compliancy periods.

Course

Course #
Course Name
Compliancy Period

Training

Attended #
Staff #
Date Completed
Compliant Until

The training table stores details of the staffs attendence or
participation
in training. The compliancy period is currently a long integer
which
is
measured in months.

What I would like to do is create a automated field which will
indicate
compliant or expired based on the date the course was attended and
the
compliancy period of the course.