Query to update next record

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Back
Top