Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    update queies (97 SR2)

    I am looking for an approach to update particular values in a field. The table has three different id fields (we'll just call them ID1, ID2, ID3), a date and time field and an "ID1type" field. There exists duplicate records that only vary by the date and time. My objective is to update the "ID1type" field by the most recent date and time. Or I want to change the type to "duplicate id1" for the records with the earliest date and time.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Update queries (97 SR2)

    I don't think you can do what you want to do in an update query. You can update table using VBA as shown in example. ID1, ID2, and ID3 are the fields with duplicate values; DATETIME is date field; and SEQ is number (Integer) field. Table is named TEST. This sub updates SEQ field for each unique set of ID1-ID2-ID3 values, in date sequence based on DATETIME field. If no dupes SEQ will = 1. You can then run update query to update ID1_TYPE field for all records where SEQ = 1.

    <pre>Public Sub UpdateRecordSequenceNumber()
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstDupe As DAO.Recordset
    Dim strSQL As String
    Dim intSeq As Integer
    Dim n As Integer
    Dim strID1 As String, strID2 As String, strID3 As String
    Dim intDupeCount As Integer
    Dim strMsg As String

    Set db = CurrentDb
    strSQL = "SELECT ID1, ID2, ID3, DATETIME, SEQ " & _
    "FROM TEST " & _
    "ORDER BY ID1, ID2, ID3, DATETIME;"
    Set rst = db.OpenRecordset(strSQL)

    rst.MoveFirst
    Do Until rst.EOF
    intSeq = 0
    If strID1 <> rst!ID1 Or strID2 <> rst!ID2 Or strID3 <> rst!ID3 Then
    strID1 = rst!ID1
    strID2 = rst!ID2
    strID3 = rst!ID3
    strSQL = "SELECT * FROM TEST " & _
    "WHERE ID1='" & strID1 & "' AND " & _
    "ID2='" & strID2 & "' AND " & _
    "ID3='" & strID3 & "';"
    Set rstDupe = db.OpenRecordset(strSQL)
    rstDupe.MoveLast
    intDupeCount = rstDupe.RecordCount
    rstDupe.Close
    For n = 1 To intDupeCount
    With rst
    intSeq = intSeq + 1
    .Edit
    !SEQ = intSeq
    .Update
    .MoveNext
    End With
    Next n
    Else
    rst.MoveNext
    End If
    Loop
    rst.Close

    strMsg = "Sequence numbers have been updated."
    MsgBox strMsg, vbInformation, "SEQUENCE UPDATED"

    Exit_Sub:
    Set db = Nothing
    Set rst = Nothing
    Set rstDupe = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.DESCRIPTION
    MsgBox strMsg, vbExclamation, "UPDATE SEQUENCE NUMBER ERROR"
    Resume Exit_Sub
    End Sub</pre>


    NOTE: For this to work in A2K or later you must set reference to "Microsoft DAO 3.6 Object Library".

    PS - ID1, ID2, ID3 are text fields in this example. If numerical need to modify SQL code. Also assumes none are null; if so again you'd have to modify code.

    HTH

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update queries (97 SR2)

    Thanks Mark. I had been experimenting with a max query against a distinctrow query, which gives me the value I need to hit against the table with, but doesn't allow one to do updates. I just finished reading about 17 chapters of "Access 97 Power Programming" and I got the idea that I was going to have to use the recordset object.

    How complex can the query string get or can you use multiple query strings to define the recordset?

    Since my max query against a distinctrow query gives me the data I need to look for to change, I thought I would store the info in an array and use the array to feed a loop to parse through the table and update the type field.

    Thanks for allowing me to leech off your experience.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Update queries (97 SR2)

    Not sure what you mean by "multiple query strings", if you open recordset with SQL statement as source there can only be one such SQL statement. The SQL string can be as complex as necessary, though the more complex the SQL the greater the chance of a syntax error. You can also use name of table or a saved query as source for recordset. When creating SQL statements in code I usually create an equivalent query in Design View, test results, then if OK switch to SQL view & copy 'n paste SQL string to code module. Usually SQL copied from query design will have to be modified somewhat to work in context of VBA (double quotes in string replaced with single quotes, etc).

    For example, in this case you could open recordset based on the max query that returns desired records (rather than try to duplicate complex SQL in code) then update records. Not sure what the "TYPE" field is being updated to, but sample code in previous msg should indicate how this field can be updated in code.

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Update queries (97 SR2)

    In further reply I tried to update table by creating a Select Distinct query that selected each unique combination of the 3 ID fields, along with the MIN and MAX date values for each group, then joining this query with table in new query. As you discovered, the resulting recordset is not updatable, whether opened as a Select or Update query or opened using code.

    I modified code previously posted to show how to update the ID1_TYPE field as described in your original post. In this example, the table ("TEST") has following fields: ID1, ID2, ID3, DATE_FLD, SEQ, ID1_TYPE and ID1_TYPE2. ID1
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •