Field in last record = one in new record

  • Thread starter Thread starter 848
  • Start date Start date
8

848

I need the ending reading in a field on one form to = the
beginning reading in a field on the next new record Can
someonr help
Thanks
 
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
 
Back
Top