I will approach this with the idea that each month would give a new
claim number for each client (as opposed to assigning a constant claim
number to each client. From this perspective you first want to group
your clients into a list of distinct clients and assign claim numbers
accordingly, then you can use an update query to update the actual data
table with the respective claim numbers. Here is how to do that:
Sub AppendClaimNumbers()
Dim strSq As String, i As Integer, arrClients() As variant
Dim RS1 As Recordset
strSql = "Select Clients From tblData Group By Clients"
Set RS1 = CurrentDB.OpenRecordset(strSql)
RS1.MoveLast
RS1.MoveFirst
i = RS1.RecordCount 'get count of distinct clients
Redim arrClients(i, 1)
'populate array with client names and assign claim# i
For i = 0 To Ubound(arrClients)
arrClients(i, 0) = RS1(0)
arrClients(i, 1) = i
RS1.MoveNext
Next
'now update data tables with claim numbers for each client
For i = 0 to Ubound(arrClients)
strSql = "Update tblData Set Claim# = " & arrClient(i, 1) _
& " Where Client = '" & arrClient(i, 0) & "'"
DoCmd.Run strSql
Next
End Sub
So you first get the list of distinct clients, assign a claim number to
each client (I started with a claim number of 0 for the sake of the
array, but you could say
arrClients(i, 1) = i + 1
This would start with a claim of 1... Note on arrays, this is a 2
dimensional variant array to hold a string and a numeric value. The
first dimension holds the client's name, the 2nd dimension holds the
claim number (I could have made the array a string array but then the
claim number would be a text value instead of a numeric value even
though it was a digit). Once you populate the array you then loop
through the array in the 2nd for loop and update your data table using
the Update query. Set Claim# = " & arrClients(i, 1) sets the claim
number (note no delimeters needed for a numeric value and this is the
2nd dimension of the array - i, 1) and the condition for where is where
Client = '" & arrClients(i, 0) & "'" --this is the first dimension of
the array i, 0 (0 is first dimension, 1 is second dimension). Note also
that since Client is text you have to delimit is with single quotes = '"
& ... & "'".
This is one way to do it. There may be a more elegant/efficient way to
do this, but off the top of my head, this was the easiest.
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!