Option Compare Database
Global EmpNum As String
Global GBL_Icount As Long
Public Function increment(ivalue As String) As Long
If Nz(EmpNum, "zzzzzzzzz") = ivalue Then
GBL_Icount = GBL_Icount + 1
Else
EmpNum = ivalue
GBL_Icount = 1
End If
increment = GBL_Icount
End Function
Public Function fnRank(Reset As Boolean, ParamArray FieldValues() As Variant) As Integer
Static myRank As Long
Static myArray() As Variant
Dim intLoop As Integer
Dim bIdentical As Boolean
If (Reset = True) Then
ReDim myArray(UBound(FieldValues))
End If
bIdentical = True
For intLoop = LBound(FieldValues) To UBound(FieldValues)
If (bIdentical = True) And (myArray(intLoop) = FieldValues(intLoop)) Then
'do nothing
Else
bIdentical = False
myArray(intLoop) = FieldValues(intLoop)
End If
Next
If bIdentical = False Then myRank = 0
myRank = myRank + 1
fnRank = myRank
End Function
This function will run amazingly quickly (compared to the other methods mentioned above) and will accomodate as many fields as you choose to use in your ranking function.SELECT EmployeeID, DependentID, fnRank(0, [EmployeeID]) as Rank
FROM yourTable
Order by EmployeeID, DOB
The down side of this technique, is that if you scroll up through the query result set, you will see that the Rank column continues to get recomputed as records move into the set of records which are visible in the query results. So, if you scroll up, the numbers get screwed up. To avoid this, I create a Rank table (tblRanks) and actually insert the rank values into that table, along with the unique PK value that identifies each record, something like:currentdb.execute "DELETE * FROM tblRanks", dbfailonerror
fnRank -1, 0 'This resets the length of the array stored in the function
strSQL = "INSERT INTO tblRanks (PKID, Rank) " _
& "SELECT ID, fnRank(0, [EmployeeID]) as Rank " _
& "FROM yourTable "
& "ORDER BY EmployeeID, DOB"
Currentdb.Execute strsql, dbfailonerror
Then you can join tblRanks to yourTable on the PK field, something like:SELECT EmployeeID, DependentID, R.Rank
FROM yourTable as T
INNER JOIN tblRanks as R on T.ID = R.PKID
Order by EmployeeID, DOB
If you configure tblRanks so that the PKID field is the primary key of that table, then the query shown above should be updateable.
What are you using this for, is it a report or do you want to display it in a form? If displaying it in a form, do you want the form to be editable or simply use the form to view the data?