Calculate Difference from Initial Value

[Guest]

I have the need to create a report which will show updated differences between the first (baseline) value and each subsequent (weekly) value

The KB article recently identified by Allen Browne helped get me on the right track for referring to a field in the previous record, but I need to refer to a field that was the first record (the subject is a patient's weight so I need to write a report that will always show the variance between the baseline weight and the current weigh, not the previous weight and current weight)

If anyone can supply further references or suggestions, I'd appreciate the assist

Thanks in advance
Trisha

 

[Allen Browne]

Presumably you have already set up a report with a group header for Week.
(In the Sorting'n'Grouping dialog (View menu), you can select a date field,
and then in the lower pane choose Yes for Group Header, and Weekly for the
interval range.)

In the Group Header, include a text box that has these properties:
Control Source Weight
Name txtWeightAtStartOfWeek
Visible False

In the Detail section of your report, add another text box with these
properties:
Control Source [txtWeightAtStartOfWeek] - [Weight]
Name txtVariance
Format General Number

That should give you the variance for each record, with respect to the first
value for the week.

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

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

I have the need to create a report which will show updated differences

between the first (baseline) value and each subsequent (weekly) value.

The KB article recently identified by Allen Browne helped get me on the

right track for referring to a field in the previous record, but I need to
refer to a field that was the first record (the subject is a patient's
weight so I need to write a report that will always show the variance
between the baseline weight and the current weigh, not the previous weight
and current weight).

 

[Guest]

Allen

I understand your directions in theory, but in practice my results are zeros. I've ended up subtracting the patient's current weight from their current weight in spite of attempting to follow your instructions to a "t"

The report has fields of PatientID, AssessmentDate, and CurrentWeight

I created a group header for AssessmentDate and changed the Group On to Week and the Interval to 1. I populated the AssessmentDate group header with a text box with a control source of CurrentWeight with a name of txtBaselineWeight and set its visibility property to false

In the detail section I inserted a text box with a control source of [txtBaselineWeight]-[CurrentWeight] named it txtVariance and changed the format to general number

When I ran the report, all the values came out as zero

I'm sure there's something elemental I'm missing, as your instructions are not only clear, but they are consistent with other methods I have used in the past to perform calculations, but I appear to be overlooking something in the process

I appreciate your time and help and will keep working on this, but if you happen to see a flaw in what I've done and could let me know what it is, I would appreciate it. Deadlines loom large and this report is stopping me in my tracks

Many thanks
Trish

----- Allen Browne wrote: ----

Presumably you have already set up a report with a group header for Week
(In the Sorting'n'Grouping dialog (View menu), you can select a date field
and then in the lower pane choose Yes for Group Header, and Weekly for th
interval range.

In the Group Header, include a text box that has these properties
Control Source Weigh
Name txtWeightAtStartOfWee
Visible Fals

In the Detail section of your report, add another text box with thes
properties
Control Source [txtWeightAtStartOfWeek] - [Weight
Name txtVarianc
Format General Numbe

That should give you the variance for each record, with respect to the firs
value for the week

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

I have the need to create a report which will show updated difference

between the first (baseline) value and each subsequent (weekly) valueright track for referring to a field in the previous record, but I need t
refer to a field that was the first record (the subject is a patient'
weight so I need to write a report that will always show the varianc
between the baseline weight and the current weigh, not the previous weigh
and current weight)