Query to update next record

[Guest]

I have a table that contains a date, beginning quantity and ending quantity.
What I would like to do is to update the current records beginning quantity
with the ending quantity from the record with the date closest to its' own
date (but less than the current records date). Any help would be appreciated.

 

[Michel Walsh]

Hi,


Maybe something like

UPDATE myTable As a, myTable As b
SET a.beginning=b.ending
WHERE b.dateStamp=(SELECT MAX(c.dateStamp)
FROM myTable As c
WHERE a.dateStamp> c.ending )




to update ALL the records

you can add


AND a.beginning IS NULL

to update those having no "beginning" quantity.


To update just one record, it could be preferable to just add


AND a.dateTimeStamp = givenDateTimeStamp




Hoping it may help,
Vanderghast, Access MVP