Update multiple rows with value from a single row

[jamesk1979]

Hi all,

Is it possible to update multiple rows in a table with a value from a single row?

I have a table like so...
ID Field Value PlanB
39 Sys 1
40 PlanA 0001
41 MaintDate 2005138
42 MaintUser Abcde123
43 Sys 1
44 PlanA 0002
45 MaintDate 2005138
46 MaintUser Abcde123

I want to copy the "0001" value in the row 40 PlanA field into all rows in the PlanB column where the ID is between 39 and 42.

And copy the "0002" value in the row 44 PlanA field into all rows in the PlanB column where the ID is between 43 and 46, etc.

Leaving me with something like this;

ID Field Value PlanB
39 Sys 1 0001
40 PlanA 0001 0001
41 MaintDate 2005138 0001
42 MaintUser Abcde123 0001
43 Sys 1 0002
44 PlanA 0002 0002
45 MaintDate 2005138 0002
46 MaintUser Abcde123 0002

Is this possible using an update query?

Apologies is this has been covered before, I tend to build all my queries using the Design View and can't usually follows the Jet SQL that seems a lot more powerful.

Thanks,
james.

 

[JerryKlmns]

Looks like you want to transform it to a new table with this new structure

NewTable Structure
----------------------
Field |Data Type
-----------+----------
Sys |Number
Plan |Text
MaintDate |Date/Time
MaintUser |Text


Create the new table and run this sub (in a module)...

Sub MakeItGood()

Dim rstRead As ADODB.Recordset
Dim strSQL As String

Set rstRead = New ADODB.Recordset
With rstRead
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseServer
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Source = "Select OldTable.* From OldTable;"
    .Open
    While Not .EOF
        strSQL = "Insert Into NewTable(Sys, Plan, MaintDate, MaintUser) Select " & .Fields(2)
        .MoveNext
        strSQL = strSQL & ", '" & .Fields(2) & "', "
        .MoveNext
        If Len(.Fields(2)) = 7 Then
            strSQL = strSQL & "#" & Left(.Fields(2), 4) & "/0" & Right(.Fields(2), 1) & "/" & Mid(.Fields(2), 5, 2) & "#"
        Else
            strSQL = strSQL & "#" & Left(.Fields(2), 4) & "/" & Right(.Fields(2), 2) & "/" & Mid(.Fields(2), 5, 2) & "#"
        End If
        .MoveNext
        strSQL = strSQL & ", '" & .Fields(2) & "';"
        CurrentProject.Connection.Execute (strSQL), adExecuteNoRecords, adCmdText
        .MoveNext
    Wend
    .Close
End With
Set rstRead = Nothing

End Sub

Since the code is in ADO, on Tools -->References check Microsoft ActiveX Data Objects 2.x Library

 

[jamesk1979]

Thanks Jerry,

That looks like a better approach that what I had been trying. I'll give it a go.

Cheers,
james.