Embedded Files
1) SQL Query qryTEST02_meters
SELECT TEST02_meters.JPNUM, TEST02_meters.JPTASK, TEST02_meters.METERSEQUENCE
FROM TEST02_meters
ORDER BY TEST02_meters.JPNUM, TEST02_meters.JPTASK;
2) VBS
Sub Process()
Dim myDB As DAO.Database
Dim myRS As DAO.Recordset
Dim myCurrentNum As Long
Dim myPreviousNum As Long
Dim myCounter As Long
Set myDB = CurrentDb
Set myRS = myDB.OpenRecordset("qryTEST02_meters")
' Loop through all records in table
myRS.MoveFirst
Do Until myRS.EOF
myCurrentNum = myRS("JPNUM")
' Check to see if the JPNUM value has changed, and either increment counter or start back over at 1
If myCurrentNum = myPreviousNum Then
myCounter = myCounter + 1
Else
myCounter = 1
End If
' Assgin counter value to METERSEQUENCE
myRS.Edit
myRS.Fields("METERSEQUENCE") = myCounter
myRS.Update
' Assign current number to previous number and go to next record
myRS.MoveNext
myPreviousNum = myCurrentNum
Loop
' Close recordset
myRS.Close
myDB.Close
Set myRS = Nothing
Set myDB = Nothing
MsgBox "Done!"
End Sub
Results
JPNUM | JPTASK | METERSEQUENCE
10 | 200 | 1
10 | 210 | 2
10 | 220 | 3
10 | 230 | 4
11 | 80 | 1
11 | 92 | 2
11 | 99 | 3
12 | 23 | 1
12 | 25 | 2
12 | 28 | 3
12 | 46 | 4
12 | 78 | 5
Page updated
Google Sites
Report abuse