Previous record as new record until it changes! 2

[proximity]

Hi,

In VBA, I want to run an SQL command that will take Table 1 and make it look like Table2.

I've seen it done before (but can't remember how!). Anyway, here goes:

Table1
Field1 Field2 Field3
12345 123222 112223
122222 122222
18852 123125 121572
152145 115542
551155 455122
12348 123221 112523
122272 122282
The gap in Field 1 needs to be filled with the previous field value and then change when the value changes. So, the end result should be:

Table2
Field1 Field2 Field3
12345 123222 112223
12345 122222 122222
18852 123125 121572
18852 152145 115542
18852 551155 455122
12348 123221 112523
12348 122272 122282

Many thanks for any and all help,

--
SM

 

[dhookom]

It doesn't look like there is any order to your table1. Do you have a primary key? Can you add a primary key so the order displayed in table1 can be described?

Duane
Hook'D on Access
MS Access MVP

 

[proximity]

Ooops! Yes, there is a primary key:

Table1
ID Field1 Field2 Field3
1 12345 123222 112223
2 122222 122222
3 18852 123125 121572
4 152145 115542
5 551155 455122
6 12348 123221 112523
7 122272 122282

ETC . . . .

 

[randy700]

One way ...

Public Function FillTable2()
    Dim rst1 As Recordset
    Dim rst2 As Recordset
    Dim db As Database
    Dim strF0 As String, strF1 As String, strF2 As String
    Set db = CurrentDb()
    Set rst1 = db.OpenRecordset("Table1")
    Set rst2 = db.OpenRecordset("Table2")
    With rst1
        .MoveFirst
        Do Until .EOF
            If Not (.Fields(0) = "") Then
                strF0 = .Fields(0)
            End If
            If Not (.Fields(1) = "") Then
                strF1 = .Fields(1)
            End If
            If Not (.Fields(2) = "") Then
                strF2 = .Fields(2)
            End If
            rst2.AddNew
            rst2.Fields(0) = strF0
            rst2.Fields(1) = strF1
            rst2.Fields(2) = strF2
            rst2.Update
            .MoveNext
        Loop
    End With
    Set rst1 = Nothing
    Set rst2 = Nothing
    Set db = Nothing
End Function

Randy

 

[dhookom]

You should also be able to use an update query:

UPDATE tblProximity SET Field1 = DLookUp("Field1","tblProximity","ID=" & DMax("ID","tblProximity","ID<" & [ID] & " AND Field1 Is Not Null"))
WHERE Field1 Is Null;

Duane
Hook'D on Access
MS Access MVP

 

[proximity]

Thanks guys,

Both processes worked. It was the update query that I had seen before but couldn't remember. Anyway, I've given you both a well deserved star!

Thank you