B
BruceM
I posted a question on this topic in Reports, but it attracted no interest
there. I know how to generate a running sum on a report, but that data has
no life outside of the text box in which it appears. It can't be used for
calculations, sorting, or any other functions. I have learned that it is
apparently possible to do a running sum in a query, but I cannot discover
how. An article on the MS web site showed how to construct such a query (in
Access 97) in the Northwinds database, but I wasted several hours before I
discovered the techniques do not translate to my needs.
To be as brief as is possible, my situation is that I need to add up
training time for individual employees, then work with that number. I have
an effective database for managing the training information. A possible
wrinkle: On-the-job training is counted as one fourth of the actual time
logged. Four hours of OJT counts as one hour of training. I have a
calculated field in a query to produce that information. That calculated
field is the basis for running sum. On a report I can place a hidden text
box in the detail section, set to running sum Over Group. The report is
grouped by Employee, and a text box in the group footer references the hidden
text box and shows the sum of all training hours (adjusted for OJT) for that
employee.
If the training is less than six hours, I would like a text box in the group
header to say "Low". If it is 6 - 12 hours, "Medium", etc. (Those aren't
the real words, by the way, but are simpler to explain). I can do that with
a text box containing a nested IIf statement, but only if that text box is in
the group footer. When I put the same IIf statement in a text box in the
header, it always returns "Low". It seems it only looks at the first record.
The same thing happens when I put the text box in the footer as described,
and put a text box in the header referencing that text box.
I may need to get away from the report and do this stuff in a query, but I
cannot figure out how. Even if I could find a workaround for this problem in
the Report environment I will still be unable to order my reports by the
running sum total for the group.
there. I know how to generate a running sum on a report, but that data has
no life outside of the text box in which it appears. It can't be used for
calculations, sorting, or any other functions. I have learned that it is
apparently possible to do a running sum in a query, but I cannot discover
how. An article on the MS web site showed how to construct such a query (in
Access 97) in the Northwinds database, but I wasted several hours before I
discovered the techniques do not translate to my needs.
To be as brief as is possible, my situation is that I need to add up
training time for individual employees, then work with that number. I have
an effective database for managing the training information. A possible
wrinkle: On-the-job training is counted as one fourth of the actual time
logged. Four hours of OJT counts as one hour of training. I have a
calculated field in a query to produce that information. That calculated
field is the basis for running sum. On a report I can place a hidden text
box in the detail section, set to running sum Over Group. The report is
grouped by Employee, and a text box in the group footer references the hidden
text box and shows the sum of all training hours (adjusted for OJT) for that
employee.
If the training is less than six hours, I would like a text box in the group
header to say "Low". If it is 6 - 12 hours, "Medium", etc. (Those aren't
the real words, by the way, but are simpler to explain). I can do that with
a text box containing a nested IIf statement, but only if that text box is in
the group footer. When I put the same IIf statement in a text box in the
header, it always returns "Low". It seems it only looks at the first record.
The same thing happens when I put the text box in the footer as described,
and put a text box in the header referencing that text box.
I may need to get away from the report and do this stuff in a query, but I
cannot figure out how. Even if I could find a workaround for this problem in
the Report environment I will still be unable to order my reports by the
running sum total for the group.