Avatar of AndyC1000
AndyC1000 asked on

Query to update fields with data from table rows above

Dear all,

I'm looking for a solution similar to filling in blank cells with the value above in Excel.  But in my case if three fields in table "TypolLFSF" Other, Couple, Family are all 0, update the cells with the data of the first populated row above (each of the three fields can't be 0 but its acceptable if one or two of the fields are).

Is this is possible in access or excel?  Please provide your advice and examples.

Below is my select statement.

SELECT TypolLFSF.*, TypolLFSF.Couple, TypolLFSF.Family, TypolLFSF.Other
FROM TypolLFSF
WHERE (((TypolLFSF.Couple)=0) AND ((TypolLFSF.Family)=0) AND ((TypolLFSF.Other)=0));

Open in new window


I also need to set TypolLFSF.HCFMDCode to "C" when the value is updated.

Thanks
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Hamed Nasr

8/22/2022 - Mon
Sharath S

Can you post some sample data with expected result?
ASKER
AndyC1000

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

Open 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

Open in new window

Jeffrey Coachman

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

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
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Hamed Nasr

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

Open in new window