Autonumber a column in a query based on another column?

[SharonT]

Does anyone know how to add an autonumber based on another column in a query and be able to specify the start number? e.g. I have a column called customer account and wish to generate an id for each new contact. (At the moment the are all zero.
All contacts are secondary contacts so I want each count to start at 2.
This would display as:

Customer Contact Contact ID
Cust1 Albert 2
Cust1 Dave 3
Cust1 Sarah 4
Cust2 George 2

I am fairly technical but by no means an Access master so any dobin friendly instructions would be gratefully received.

 

[chris153]

Hi,

If you're using a form to enter the contacts you could try using an AfterUpdate event the Customer field, such as :

Private Sub Customer_AfterUpdate()
Dim idfield As Variant
idfield = DMax("[Contact_ID]", "TableName", "[Customer] = '" & [Customer] & "'")
If IsNull(idfield) = True Then
Contact_ID = 2
Else
Contact_ID = idfield + 1
End If
End Sub

Hope this helps !

Chris

 

[Golom]

Using Access's internally defined Autonumber field it is possible to start at some number other than 1 but, from your example, it looks like you want the "Autonumber" field to start over with each new customer. That rules out the Access autonumber. It increments forever without respect to the values in any other fields. If you want to do this (Why do you want to do this by the way?) then you will need to write some code to handle it. I don't know any "automatic" way to have Access handle it for you.

 

[SharonT]

Thanks for the responses. I am trying to take some very bad old data and convert it into a suitable format for a data load into another database. The table which I need to load the data into requires a unique contact id per customer. I don't want to spend a lot of time writing code as I only need to do this a couple of times.