Would Like Help with Formula on Grouped records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below is a sample of what I'm trying to accomplish in a report. I have a
field called [Type] and it contains Initial and Final Scores for each
RecordID. My report groups on [RecordID] and [Type]. The object is to
subtract the Final score/value from the Initial score/value to display in the
RecordID footer to show whether they improved or not. All scores(data) is
displayed in the detail section. See sample below. Any assistance would be
greatly appreciated.

Thanks, JJ

Grouping on RecordsID
then
Grouping on [Type] (Data will be either Initial or Final)

RecordID: 123456789 ([RecordID] Header)
Type ([Type] Healder/Grouping with descending sort
Score1 Score2 Score3 Score4
Initial
8 9 5
4
Final
7 5 6
2
RecrodID Footer 1 4 -1 2
 
Assuming you have a field name Type with values of "Initial" or "Final" and
fields for ScoreX.
Add a text box to some group footer with a control source like:
=Sum(Abs([Type]="Initial") * [ScoreX]) - Sum(Abs([Type]="Final") * [ScoreX])
 
Duane...Thank You SOOOO Much. It worked perfect! I would have never figured
this out in a million years. THANKS!!! JJ

Duane Hookom said:
Assuming you have a field name Type with values of "Initial" or "Final" and
fields for ScoreX.
Add a text box to some group footer with a control source like:
=Sum(Abs([Type]="Initial") * [ScoreX]) - Sum(Abs([Type]="Final") * [ScoreX])

--
Duane Hookom
MS Access MVP

JJ said:
Below is a sample of what I'm trying to accomplish in a report. I have a
field called [Type] and it contains Initial and Final Scores for each
RecordID. My report groups on [RecordID] and [Type]. The object is to
subtract the Final score/value from the Initial score/value to display in
the
RecordID footer to show whether they improved or not. All scores(data) is
displayed in the detail section. See sample below. Any assistance would
be
greatly appreciated.

Thanks, JJ

Grouping on RecordsID
then
Grouping on [Type] (Data will be either Initial or Final)

RecordID: 123456789 ([RecordID] Header)
Type ([Type] Healder/Grouping with descending sort
Score1 Score2 Score3 Score4
Initial
8 9 5
4
Final
7 5 6
2
RecrodID Footer 1 4 -1 2
Click to expand...
Click to expand...
 
Back
Top