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.
I also need to set TypolLFSF.HCFMDCode to "C" when the value is updated.
Thanks
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));I also need to set TypolLFSF.HCFMDCode to "C" when the value is updated.
Thanks
Sharath S
Can you post some sample data with expected result?
ASKER
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
Table 2 - Output
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 ATable 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
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("T
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
Dim rst As DAO.Recordset
Dim sglStoredCouple As Single
Dim sglStoredFamily As Single
Dim sglStoredHCFMDCode As Single
Set rst = CurrentDb.OpenRecordset("T
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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