Ok, well, I didn't see an answer to this after searching for 30 minutes, so here goes:
I have the following table:
Unit ID|Department|Repair Date|Repair Odometer
101|01|2009-01-01|100,000
101|01|2009-03-01|104,000
101|01|2009-05-29|108,400
102|01|2009-01-13|92,000
102|01|2009-03-14|96,500
102|01|2009-06-01|101,000
103|02|2009-01-01|45,000
103|02|2009-06-01|59,000
I need to be able to summarize how many miles each vehicle traveled, then summarize those subtotals for each department.
So far, I have been able to create a formula that gets the mileage at the start date and the mileage at the end date and subtracts the two. This helps avoid any data entry mistakes made in between the two dates (which is quite common with our user base, so I'm trying to make this foolproof).
numbervar MinMiles;
numbervar MaxMiles;
select {Repair Date}
case Minimum ({Repair Date}, {Unit ID}) : MinMiles := {Repair Odometer}
case Maximum ({Repair Date}, {Unit ID}) : MaxMiles := {Repair Odometer}
numbervar Miles := MaxMiles - MinMiles;
This works great when I put it in the group footer for the Unit, but I can't get a subtotal in the next level up, the group for the department. For some reason I can't get a running total on a formula that references the Miles variable.
Is there anything that will total the change in a field, and then is there any way to subtotal the sum of all the changes? I've spent the last day and a half trying 20 different ways to get this to work. So far they all have the same results, and this is the simplest formula I'd been using. Everything else was having to use a lot of if Unit ID <> Next (Unit ID) logic.