Query to change record based on calculation

[Sabosis]

Hello-

I have been working on an attendance report for my department and
have
one final hurdle. My query will show any rep on a write up for
attendance along with the level of the write up (severity) and what
has happened since the last write up took place. There are 5 write up
levels, 1 being the first step, 5 being termination. We track write
ups in 6 month intervals, meaning that if you go on a write up and
don't hit the next level in 6 months, you will revert to the previous
level, 4 to 3, 2 to 1, etc. My query will show the rep, the date of
last write up, the severity level, and a total of attendance
occurances in the past 6 months. If the total attendance occurances
is
less than 6, I would want the severity level to be reduced by 1. Is
this possible? Is it something that can be done through code or an
action query?


Ex:


Rep Name CorrDate Level AttTotal
Smith, John 06/21/2008 3 4


Since John has gone 6 months without reaching the 6 occurances that
would take him to level 4, he should be dropped to level 2. If john
was on level 1 and he went 6 months without going to level 2, his
record should be deleted so he has a clean slate.


Thanks for any help or insight that might be passed on to me. This
has
me a bit stupmed.


Scott

 

[Jeff Boyce]

It sounds like you are saying that if the sum of attendance occurances is <
6, then you want to calculate [severitylevel]-1.

I believe you can do that in a query, using an IIF() statement...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sabosis]

It sounds like you are saying that if the sum of attendance occurances is<
6, then you want to calculate [severitylevel]-1.

I believe you can do that in a query, using an IIF() statement...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello-

I have been working on an attendance report for my department and
have
one final hurdle. My query will show any rep on a write up for
attendance along with the level of the write up (severity) and what
has happened since the last write up took place. There are 5 write up
levels, 1 being the first step, 5 being termination. We track write
ups in 6 month intervals, meaning that if you go on a write up and
don't hit the next level in 6 months, you will revert to the previous
level, 4 to 3, 2 to 1, etc. My query will show the rep, the date of
last write up, the severity level, and a total of attendance
occurances in the past 6 months. If the total attendance occurances
is
less than 6, I would want the severity level to be reduced by 1. Is
this possible? Is it something that can be done through code or an
action query?

Rep Name        CorrDate       Level       AttTotal
Smith, John    06/21/2008         3             4

Since John has gone 6 months without reaching the 6 occurances that
would take him to level 4, he should be dropped to level 2. If john
was on level 1 and he went 6 months without going to level 2, his
record should be deleted so he has a clean slate.

Thanks for any help or insight that might be passed on to me. This
has
me a bit stupmed.

Scott- Hide quoted text -

- Show quoted text -

Joyce-

Would this be some sort of update query based on the original query?
I'm not sure exactly what this involves...

 

[Jeff Boyce]

No. An update query changes the actual values. A select query allows you
to keep your existing data and re-calculate/reformat it as needed.

Regards

Jeff Boyce
Microsoft Office/Access MVP

It sounds like you are saying that if the sum of attendance occurances is
<
6, then you want to calculate [severitylevel]-1.

I believe you can do that in a query, using an IIF() statement...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello-

I have been working on an attendance report for my department and
have
one final hurdle. My query will show any rep on a write up for
attendance along with the level of the write up (severity) and what
has happened since the last write up took place. There are 5 write up
levels, 1 being the first step, 5 being termination. We track write
ups in 6 month intervals, meaning that if you go on a write up and
don't hit the next level in 6 months, you will revert to the previous
level, 4 to 3, 2 to 1, etc. My query will show the rep, the date of
last write up, the severity level, and a total of attendance
occurances in the past 6 months. If the total attendance occurances
is
less than 6, I would want the severity level to be reduced by 1. Is
this possible? Is it something that can be done through code or an
action query?

Rep Name CorrDate Level AttTotal
Smith, John 06/21/2008 3 4

Since John has gone 6 months without reaching the 6 occurances that
would take him to level 4, he should be dropped to level 2. If john
was on level 1 and he went 6 months without going to level 2, his
record should be deleted so he has a clean slate.

Thanks for any help or insight that might be passed on to me. This
has
me a bit stupmed.

Scott- Hide quoted text -

- Show quoted text -

Joyce-

Would this be some sort of update query based on the original query?
I'm not sure exactly what this involves...