numbering transactions - possible query??

[Ted]

Hi all,

I have a Claims system where there are multiple transactions per claim. Each
of these transactions
has a Transaction ID. Each Transaction now needs a Transaction # per claim.
Going forward
this is not a problem but is there an easy way to go back and update the
TransactionNumber field? I
have a DateTime Stamp (DateEntered) to let me know the order of the
transactions. So for example
i could have 100 transactions which will have Transaction IDs of 1-100 but
now they need a
Transaction # per Claim. If a Claim has 10 Transactions they should be
numbered 1-10.

any help or direction would be very much appreciated
TIA
Ted

 

[StrayBullet via AccessMonster.com]

I'm currently using the following module:

Public Function LineNum(lngFldVal As Long, strSource As String, strKey As
String) As Long
Dim strCrit As String
strCrit = "[" & strKey & "] < " & CStr(lngFldVal)
LineNum = Nz(DCount(strKey, strSource, strCrit)) + 1
End Function


It is referred to in this query (which you can easily edit for use):

SELECT LineNum([dependentsId],'qrydependents','dependentsid') AS RowNumber,
DepNum.DependentsID, DepNum.intDependentAge, DepNum.intBorrowerID
FROM qrydependents AS DepNum
WHERE (((DepNum.intBorrowerID)=[forms]![frmborrowerinfo].[borrowerid]));