Hi,
DON'T, in a table. In a table, use one record per reading, in your FORM,
where you need the view you described, base you form on the following query:
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
