Search code examples
sqlms-accessinsert-update

Microsoft Access SQL

I am looking for a solution to fill fields in an Access DB SQL statement. Any suggestions?

Example of the dataset attached:

Initial situation

Goal:

Goal

I am looking for a solution to fill fields in an Access DB SQl statement. Any suggestions?

Example of the dataset attached:

Initial situation

Goal

I'd really appreciate your help!

THX!!

Solution

  • As you can't sort the recordset as is, you can't use SQL.

    Use VBA and DAO to loop the records and update as desired:

    Public Function FillProduction()

    Dim Records     As DAO.Recordset
    
    Dim Sql         As String
    Dim Number4     As Long
    
    Sql = "Select * From YourTableName"
    Set Records = CurrentDb.OpenRecordset(Sql)
    
    While Not Records.EOF
        If Records.Fields("1").Value = 510 Then
            Number4 = Records.Fields("4").Value
        Else
            Records.Edit
                Records.Fields("4").Value = Number4
            Records.Update
        End If
        Records.MoveNext
    Wend
    Records.Close
    
End Function