Numbering Records

[steve229922]

I have a table with cutomers/customer numbers like:

123 Customer1
123 Customer1
456 Customer2
456 Customer2
456 Customer3

I need to add a field that counts each customer record so I end up w/ a table like:

123 Customer1 1
123 Customer1 2
456 Customer2 1
456 Customer2 2
456 Customer3 3

Is there a VBA fuction that might do something like this?
Any help/direection much appreciated!!!
Thanks--

 

[KenReay]

Hi

Is the object simply to give a unique key (eg CustomerCode/Sequence), if yes, then adding an autonumber column would be easier, but would not give you the sequence within Customer code

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mordja]

steve229922,

Create a module with the following.


Option Compare Database
Option Explicit
Public lasti As Integer
Public lasts As String

Public Function counts(s As String) As Integer
If (lasts = s) Then
lasti = lasti + 1
Else
lasts = s
lasti = 1
End If
counts = lasti
End Function



Now run a query, add your id and customer fields. Then add an expression field. Click on functions and insert the above function. For the parameter s put in [yourCustomerIdFieldName]. And run the query.

Mordja