Ending = beginning

[848]

I need the ending reading in a field on one record to =
the beginning reading in a field on the next new record
Like ending speedomoter reading = beginning speedomoter on
the next blank record
help !!!
Thanks

 

[Michel Walsh]

HI,



SELECT a.car,
a.dateReading As ActualDate,
LAST(a.odometerReading) As Reading,
MAX( b.dateReading) As PreviousDate,
MAX( b.odometerReading) As PreviousReading

FROM myTable As a LEFT JOIN myTable As b
ON a.car=b.car AND a.dateReading>b.dateReading

GROUP BY a.car, a.dateReading
ORDER BY a.car, a.dateReading DESC;



That assumes the odometer always increase its value (does not go though
000000 after a 999999 ).
You can change LAST for SUM, MIN, or MAX, since there is just one record
over which the aggregate would be performed, that does not matter.


Hoping it may help,
Vanderghast, Access MVP