Query to update fields with data from table rows above

Can you post some sample data with expected result?

Thanks for your response.

I've added two tables below, table 1 the orginal table and table 2, the output.  Sorry about the formatting issues.

See records in table 1 with id 3, 4 and 6, they all have 0 inserted in the Couple, Family and Other fields.  

By detault HCFMDCode is "A" which means the data is unchanged.
 
In the output table you'll see records for id's 3, 4 and 6 have been updated with the values from id's 2 and 5.

The HCFMD code for these records have now been updated to "C".  

Notice the record from id 4 has also been updated with the values from record id 2 because record id 3 also contains fields with all 0's.

I hope this makes sense, to summarise I want to update the record's with valid data, and invalid data refers to a set of fields where the values are 0.

Table 1 - Original Table

ID	Couple	Family	Other	HCFMDCode
1     	0.433   0.567           0                    A
2      	0.223   0.388           0.388             A
3     	0          0                  0                    A
4     	0          0                  0                    A
5      	0.355   0.332           0.332             A
6      	0          0                  0                    A

Select allOpen in new window

Table 2 - Output

ID	Couple	Family	Other	HCFMDCode
1     	0.433   0.567           0                    A
2      	0.223   0.388           0.388             A
3     	0.223   0.388           0.388             C
4     	0.223   0.388           0.388             C
5      	0.355   0.332           0.332             A
6      	0.355   0.332           0.332             C

Select allOpen in new window

Try this code like this on a button on a form:

Dim rst As DAO.Recordset
Dim sglStoredCouple As Single
Dim sglStoredFamily As Single
Dim sglStoredHCFMDCode As Single
Set rst = CurrentDb.OpenRecordset("TypolLFSF")

rst.MoveFirst

    Do Until rst.EOF
        If rst!Couple = 0 And rst!Family = 0 And rst!HCFMDCode = 0 Then
            rst.Edit
            rst!Couple = sglStoredCouple
            rst!Family = sglStoredFamily
            rst!HCFMDCode = sglStoredHCFMDCode
            rst.Update
        End If
    'Store the new current values
    sglStoredCouple = rst!Couple
    sglStoredFamily = rst!Family
    sglStoredHCFMDCode = rst!HCFMDCode
    rst.MoveNext
       
    Loop

rst.Close
Set rst = Nothing

JeffCoachman

from the  sample data you posted, it is not clear what the purpose of this table is ...
In the bigger picture, this seems like redundant (un-normalized) data.
Perhaps it would be more efficient if you stored this repeating data only once in a separate table.

But this is probably best if dealt with in a separate new thread...


JeffCoachman

Try this code, it checks the current record field value, if it is 0, then set the value to that of the previous record.

Private Sub Command1_Click()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("TypolLFSF")
    Dim rs1 As Recordset
    Dim i As Integer
    Set rs1 = CurrentDb.OpenRecordset("TypolLFSF")
    rs.MoveFirst
    rs1.MoveFirst
    rs.MoveNext 'one record ahead of rs1
    Do While Not rs.EOF
        rs.Edit
        For i = 1 To rs.Fields.Count - 1
            If Nz(rs(i), 0) = 0 Then
                rs(i) = rs1(i)
            End If
        Next i
        rs.Update
        rs.MoveNext
        rs1.MoveNext
    Loop
    rs.Close
    rs1.Close
End Sub

Select allOpen in new window