Updating records using the results of another Reco

MarXman · July 27th, 2003, 07:13 PM

Morning All,

I hope someone can help me here...
I am trying to fill in the blanks within the records of a table using information already in that table.
i.e: I have five records with the same 'premise code' but only on has 'Tier' information - I want to be able to put this information in all 5.

I thought I had it with this code but when I run it Access just shuts down on me...program not responding.

Could someone have a look and maybe VET my code and tell what what simple thing I am missing.

---------------------------------------
Private Sub Tier_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strSQL2 As String

    strSQL = "SELECT DISTINCT [Premise Code], Tier "
    strSQL = strSQL & "FROM [Combined List] "
    strSQL = strSQL & "WHERE Tier Is Not Null;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    Do Until rs.EOF
        strSQL2 = "UPDATE [Combined List] SET Tier='"
        strSQL2 = strSQL2 & rs!Tier & "' WHERE [Premise Code]='"
        strSQL2 = strSQL2 & rs![Premise Code] & "';"
        db.Execute strSQL2
        rs.MoveNext
    Loop

End Sub
--------------------------------------

Any help would be much appreciated

Comrades, What we do in life echoes in eternity

John Anthony · July 31st, 2003, 05:19 PM

Give me just a bit more information, and I should be able to help you out.

What exactly are you trying to update?

Is it one field in a record, based on something else?

John

pjm · September 26th, 2006, 02:51 PM

No wonder that you're locking up! You're running a separate query for each and every record in your table. At a glance you should be able to do this with 2 queries and NO loops.

    SELECT DISTINCT [Premise Code], Tier
    INTO temp
    FROM [Combined List]
    WHERE Tier Is Not Null;

AND

    UPDATE [Combined List] SET Tier=temp.Tier
    FROM [Combined List] JOIN temp
    ON [Combined List].[Premise Code]=temp.[Premise Code];

I roughed this out pretty quickly, so I'll leave it to you to check my syntax and add all the extra code for creating & running the queries as well as cleaning up afterwards.

-Phil-