How to get the first and last record of a field in a CR8.5 report?

[Digitalcandy]

I've got a report in which I want to evaluate the first and last records of a field in a report. More specifically I've got a small Access database setup that keeps track of my gym workouts. Let's say I run a 2 month date range which includes everyday I worked out and the amount I weighed on each day. If I have 30 records, (30 different days of working out), then I would also have 30 weight records.

I want to take the last body weight record and subtract it from the first body weight record without using subreports if possible.

I do not want to use the MAXIMUM/MINIMUM formulas to get a weight loss for the date range as it will not be accurate.

Thanks in advance.

 

[lbass]

Assuming you have only one weight recorded per date, you could record-select your two-month date range, group by the individual, and then also do a group select:

{date} = Maximum({date},{indivID}) or
{date} = Minimum({date},{indivID})

Now only the first and last rows will display for each individual. Sort by {date} ascending. Then create a formula {@wgtchange}:

if distinctcount({date},{indivID}) > 1 and
previous({indivID})={indivID} then
previous({weight})-{weight} else 0 //this allows for individuals who may have been weighed only once

Now you could drag {date} into the header and the footer, and display {@wgtchange} in the footer. Your report would look like:
Date Range Weight Change
Leslie 03/05/2003
05/05/2003 -22

Christina 04/02/2003
04/25/2003 +15

-LB

 

[lyanch]

Hmmm...

In the report header I would set a variable to the weight (your first record). In the report footer you would need to create a formula to figure the difference

In header:

@SetStart:

numbervar StartWeight := {table.weightfield};


Int the report footer:

@CalcWeight:

numbervar StartWeight:

{table.weightfield} - StartWeight.


Lisa