Return the last known value

[Twi78]

Hi all.



I hopefully have a simple query to solve for the more seasoned user. I have a table as follows:

Event¦Value
1¦5499
2¦2433
3¦
4¦
5¦2300
6¦

Where there are spaces in the table, I would like to do populate it with the last known value. The 'Event' field will always be in numerical order and so the output would look like:

Event¦Value
1¦5499
2¦2433
3¦2433
4¦2433
5¦2300
6¦2300

Can anyone suggest how I could write a query to achieve this?

Thanks
G

[June7]

I had hoped the answer would be in http://allenbrowne.com/subquery-01.html#AnotherRecord

But I am not having success. The example data lacks a group identifier so the example query won't work. If this can be done by query alone, it is far more advanced and complicated than I have ever attempted. I would use VBA to open and manipulate recordset object. Like:

Sub FixIt()
Dim rs As DAO.Recordset
Dim lngData As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY Event;")
If Not rs.EOF Then lngData = rs!Data
While Not rs.EOF
    If IsNull(rs!Data) Then
        rs.Edit
        rs!Data = lngData
        rs.Update
    Else
        lngData = rs!Data
    End If
    rs.MoveNext
Wend
End Sub

Procedure assumes record 1 has data. Note that I changed field name from Value to Data.

Value is a reserved word. Should not use reserved words as names.