Update Sequentially a field based upon the occurence of another field

[pdoi]

My table has 2 fields VIN & Sequence. The VIN field has data as follows:

VIN Sequence
123
123
123
234
234
345
456

I would like to write an update query so that the Sequence field results is as follows:

VIN Sequence
123 1
123 2
123 3
234 1
234 2
345 1
456 2

So to summarize every time the VIN changes Sequence goes back to 1. If VIN has multiple same entries the sequence is incremented by 1.

Thanks

[z{Moderator's comment: This post is the result of the member posting the same question in three different threads using two names.}]

[zmbd]

Unless you have a unique/primary key that you did not provide, or some other method of ordering, neither a simple update query nor a simple VBA solution is available from what I've seen and done and the results would be somewhat arbitrary.

[zmbd]

PDOIZ/PDOI:
1) The use of two different Avatar names is normally prohibited.

2) Do not double post your questions. Please refer to the Site FAQ and forum stickies.


[EDIT]
3) If you thought the double posting was bad... I just merged the third!

[ADezii]

Assuming your Table Name is Table1 and that the [VIN] Field cannot be NULL (Required = Yes), then there is a relatively simple solution using VBA:

Dim strSQL As String
Dim MyDB As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim intCtr As Integer
 
strSQL = "SELECT DISTINCT Table1.VIN FROM Table1 ORDER BY Table1.VIN;"
 
Set MyDB = CurrentDb
 
'Generate a Recordset of Unique VIN Numbers sorted Ascending
Set rs1 = MyDB.OpenRecordset(strSQL, dbOpenSnapshot, dbOpenForwardOnly)
 
Do While Not rs1.EOF
  'All Records belonging to each Unique VIN Number
  Set rs2 = MyDB.OpenRecordset("SELECT * FROM Table1 WHERE [VIN] = " & rs1![VIN], dbOpenDynaset)
    Do While Not rs2.EOF
     intCtr = intCtr + 1    'Increment Sequence Counter for each Unique VIN
      rs2.Edit
        rs2![Sequence] = intCtr
      rs2.Update
        rs2.MoveNext
    Loop
      intCtr = 0        'RESET Sequence Counter for each Unique VIN
      rs1.MoveNext      'Next Unique VIN
Loop
 
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

[zmbd]

ADezii:
Certainly one solution; however, are we not assuming that the order given is indeed the correct order for inserting the sequetial count? As such, it why I didn't offer any solution until we found out about a primary key or other method of ordering the records.

[zmbd]

ADezii:
Sorry there A:
Appears that Mr.P has several user names and posted the same question multiple times!