One fourth of running total

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I have a database to keep track of training time. It
contains an Employees table, a Training Session table (for
subject, instructor, hours, etc.), and an Enrollment table
as a junction table. There is a many-to-many relationship
between Employee and Session (each employee can attend
many sessions, each session can include many employees),
thus the junction table, which contains employee name and
date.
A report summarizes the information for each employee. It
is based on a parameter query that includes the three
tables mentioned above, and is grouped by employee. There
is a text box for Training Time (from the Training Session
table), with its running sum set to Over Group. This
works as intended to total the training time for each
employee.
However, some training is on-the-job (OJT), which counts
as one quarter of the the time recorded. Eight hours of
OJT = 2 hours of training. What I need to do is to add
regular (not OJT) training plus one quarter of OJT, and to
have a running sum on the report. How can I do this?
 
Without having more specififc data I would suggest to use

IIF([TraningType] = "OJT",[TrainingTime]/4,[TrainingTime])

this IIF statement would devide the time by 4 if it is OJT
and otherwise will return the full time.

Hope this helps.
Fons
 
Thanks for your reply. You got me on track, although I'm
not sure if it was as you intended. I used a variant of
your formula as a calculated field [OJTcalculated] in the
report's underlying query. The variation is that [OJT] is
a Yes/No field, so it was = IIf([OJT] = True, etc.). I
added a text box (txtOJTcalculated) to the report with its
control source set to [OJTcalculated]. I made it
invisible, and set its running sum to Over Group (the
report is grouped by employee). The group footer contains
an unbound text box set to = [txtOJTcalculated]
& "Hours". The difficulty I was having before was that I
was attempting to perform the calculation on the running
sum rather than on the individual training session.
-----Original Message-----
Without having more specififc data I would suggest to use

IIF([TraningType] = "OJT",[TrainingTime]/4,[TrainingTime])

this IIF statement would devide the time by 4 if it is OJT
and otherwise will return the full time.

Hope this helps.
Fons
-----Original Message-----
I have a database to keep track of training time. It
contains an Employees table, a Training Session table (for
subject, instructor, hours, etc.), and an Enrollment table
as a junction table. There is a many-to-many relationship
between Employee and Session (each employee can attend
many sessions, each session can include many employees),
thus the junction table, which contains employee name and
date.
A report summarizes the information for each employee. It
is based on a parameter query that includes the three
tables mentioned above, and is grouped by employee. There
is a text box for Training Time (from the Training Session
table), with its running sum set to Over Group. This
works as intended to total the training time for each
employee.
However, some training is on-the-job (OJT), which counts
as one quarter of the the time recorded. Eight hours of
OJT = 2 hours of training. What I need to do is to add
regular (not OJT) training plus one quarter of OJT, and to
have a running sum on the report. How can I do this?
.
Click to expand...
.
Click to expand...
 
Glad it worked out.
Fons
-----Original Message-----
Thanks for your reply. You got me on track, although I'm
not sure if it was as you intended. I used a variant of
your formula as a calculated field [OJTcalculated] in the
report's underlying query. The variation is that [OJT] is
a Yes/No field, so it was = IIf([OJT] = True, etc.). I
added a text box (txtOJTcalculated) to the report with its
control source set to [OJTcalculated]. I made it
invisible, and set its running sum to Over Group (the
report is grouped by employee). The group footer contains
an unbound text box set to = [txtOJTcalculated]
& "Hours". The difficulty I was having before was that I
was attempting to perform the calculation on the running
sum rather than on the individual training session.
-----Original Message-----
Without having more specififc data I would suggest to use

IIF([TraningType] = "OJT",[TrainingTime]/4, [TrainingTime])

this IIF statement would devide the time by 4 if it is OJT
and otherwise will return the full time.

Hope this helps.
Fons
-----Original Message-----
I have a database to keep track of training time. It
contains an Employees table, a Training Session table (for
subject, instructor, hours, etc.), and an Enrollment table
as a junction table. There is a many-to-many relationship
between Employee and Session (each employee can attend
many sessions, each session can include many employees),
thus the junction table, which contains employee name and
date.
A report summarizes the information for each employee. It
is based on a parameter query that includes the three
tables mentioned above, and is grouped by employee. There
is a text box for Training Time (from the Training Session
table), with its running sum set to Over Group. This
works as intended to total the training time for each
employee.
However, some training is on-the-job (OJT), which counts
as one quarter of the the time recorded. Eight hours of
OJT = 2 hours of training. What I need to do is to add
regular (not OJT) training plus one quarter of OJT, and to
have a running sum on the report. How can I do this?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top