SQL Query to Update Values from Column 1 to Column 2

[Christineeve]

Hi,
I'm stumped. I've searched the forums, but I'm still struggling to determine the very best way to resolve my problem. I've inherited an unnormalized database. I'm empowered to only continue to band-aide it, not normalize it or rebuild it.

This is a table that was being updated (on click) by several mile-long IIF statements in the supporting query of a form. I'd like to replace these ineffective IIF statements. Besides the obvious flaws in long IIFs, I cannot add anymore IIF critera without it crashing out.

tbl_rates
ID Rate Rate1 Rate2
15 1.2 2.0 1.7
16 1.1 1.8 .09
17 .97 2.1 1.2

I'd like to use ADO or whatever the experts here recommend to update the values in the above table (no PK or FK no relation to other tables).

Until the end of the table, I need to move the data from each column one step to the right. That is,
Rate1 moves to Rate2.
Rate moves to Rate1.
Rate is set to 0.

If it's not in this order, you replace Rate1 before updating Rate2. At least that's how I see it.

I've thought of SQL. I've thought of VBA or combination. I just cannot get a clear picture of what I should do.

Would anyone be able to suggest to me the best way to do this? I am happy to continue researching and coding something. If someone would point me in a direction to go, with a few details to start with, I'd be grateful.

Thank you for any comments you might have. If I'm in the wrong forum, I apologize. Please let me know and I'll move my post to the correct forum.

Thanks much,
Christine

 

[Remou]

If I understand you correctly, this can be done like so:

UPDATE TableNameHere SET rate = Null, rate1 = [rate], rate2 = [rate1]

 

[Christineeve]

Hi,
That looks perfect. I will try it.


Thank you so much.

 

[Christineeve]

I just wanted to post my solution. It works great so far. I will be adding error handling before it goes into production. If you see a design flaw, please let me know. I appreciate any help I can get. Thank you!

'The purpose of this code is to step through the recordset and update the value
'of one field into the next.

Dim conDatabase As ADODB.Connection
Dim rstRates As ADODB.Recordset
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim user As String
Dim mydb As String
Dim mySysMDW As String
Dim mydbpw As String

'Obtain the current user logged into the workgroup, the application path, and database name.
user = CurrentUser()
mydb = CurrentProject.Path & "\" & CurrentProject.Name
mySysMDW = CurrentProject.Path & "\" & "BlahBlah.MDW" 'Be sure to change this to match the .MDW. in your project.
mydbpw = "" 'There is no password.

Set conDatabase = New ADODB.Connection

'Connect via Jet Engine to current application.
With conDatabase
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDB:System database") = mySysMDW
.Properties("User ID") = user
.Properties("Password") = ""
.Open mydb
End With

Set rstRates = New ADODB.Recordset

With rstRates
'Open the recordset, select all the values and update relevant fields.
Set .ActiveConnection = conDatabase
.Open "SELECT * FROM tblRates", conDatabase, adOpenDynamic, adLockBatchOptimistic
Do While Not .EOF
![2Daysago] = ![1dayago] 'Copy the value from 1 day ago to 2 days ago.
![1dayago] = !rate 'Copy the value from Rate to 1 day ago.
!rate = 0 ' set Rate to 0
.UpdateBatch 'Update the record and move on.
.MoveNext
Loop
End With
MsgBox "All rates have been updated."

'Release the resources.

rstRates.Close
conDatabase.Close

Set rstRates = Nothing
Set conDatabase = Nothing

End Sub

 

[PHV]

Why not simply an UPDATE query (SQL code)?
UPDATE tblRates SET [2Daysago]=[1dayago],[1dayago]=rate,rate = 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Christineeve]

Thank you for all the help provided in this site. I've learned a lot. I hope to learn more.

The reason why I couldn't just do the recommened solution of

"UPDATE tblRates SET [2Daysago]=[1dayago],[1dayago]=rate,rate = 0"

is because I had to loop through all the records in the table and update all the fields.

I'm posting my solution one, because I wouldn't mind a code review with your suggestions for improvment (please be gentle).

And, just in case it would help someone else to do something similar.

Private Sub UpdateRates()
    'The purpose of this code is to step through the recordset and update the value
    'of one field into the next.
    Dim conDatabase As ADODB.Connection
    Dim rstRates As ADODB.Recordset
    Dim strSQL As String
    
    On Error GoTo ErrorHandler:
    
    Set conDatabase = CurrentProject.Connection
    strSQL = "SELECT * FROM lkp_VariableIndex"
    
    Set rstRates = New ADODB.Recordset
    
    rstRates.Open strSQL, conDatabase, adOpenDynamic, adLockBatchOptimistic
    
    With rstRates
        Do While Not .EOF
            ![2Daysago] = ![1dayago] 'Copy the value from 1 day ago to 2 days ago.
            ![1dayago] = !rate       'Copy the value from Rate to 1 day ago.
            !rate = 0                ' set Rate to 0
            .UpdateBatch            'Update the record (all columns) and move on.
            .MoveNext
        Loop
    End With
    MsgBox "All rates have been updated."
    
    'Release the resources by closing connections and setting them to nothing.
    rstRates.Close
    conDatabase.Close
    
    Set rstRates = Nothing
    Set conDatabase = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "There was an error updating the Rates." & vbCrLf & _
    "Please see your systems administrator for assistance.", vbCritical, "Rates Form"

End Sub

Thank you.

 

[PHV]

because I had to loop through all the records in the table
As the UPDATE instruction has no WHERE clause I don't see the problem ...

 

[Christineeve]

Hi,
I can understand why you don't see the problem, because the problem is me!

Are you saying that the only thing I had to put in my procedure is the update statement? No need to open the recordset or connect to the database? No need to loop through each record to update them one by one?

Would you show me your idea of what the best way to approach this, as in a full make-over of my sub routine?

Thank you for your help. It's much appreciated.

 

[PHV]

Private Sub UpdateRates()
DoCmd.RunSQL "UPDATE lkp_VariableIndex SET [2Daysago]=[1dayago],[1dayago]=rate,rate=0"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Christineeve]

Wow, so that just updates the entire table, and it's as easy as that? I'm going to put it to use tomorrow.

Thank you for saving me time and heartache!