Multiple autonumbers in same column

[sarahnice]

Hi,

One of the fields in my table is populated by a field entered in a form by a user. Is there a way to add an incrementing value to the end of this value. The autonumber should reset evry time a new value is entered into the field.

The values are like this:
45354
34545
23478
45354
45354
34545

I want to be able to add an autonumber for each different value. Like this:
45354-001
34545-001
23478-001
45354-002
45354-003
34545-002

The value 45354 is appended with the values 001, 002, 003.
The value 34545 is appended with the values 001, 002
The value 23478 is appended with the value 001

If the user entered another value of 45354, it would be appended with 004. I hope this is making sense. I want to have multiple autonumbering for each distinct value entered by the user and if the user enters a value that is already in the table, then it is appended with the next number in the sequence for that value.

I hope someone can help with this.

 

[KenReay]

Hi

this is a variation on generating your own autonumber, try searching the FAQ section, there are several FAQs on the subject

Regards

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

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

UK

 

[willir]

sarahnice

Will the sequential number increase during one data session, or is it expected to increase over any session? Specifically, do you expect the system to continue the incrementation (is that a word?) over multiple sessions?

Richard

 

[JeremyNYC]

One thing to throw in here, though I know Richard and Ken both know this, is that you should not store both of those numbers in the same field. Keep them in two fields and you can concatenate them in forms, reports, and even a function, so you only have to do it once. You'll want to be able to sort and filter on just one of those two bits of information at some point, I guarantee it. And having them both in the same field will make that a lot more work.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[willir]

You beat me to it Jeremy -- I was waiting for Sarah to answer my query, and get more into the scope and design issue before moving on this topic. Very perceptive of you to notice the sort and query issue. (The next was that if these "numbers" should perhaps be "text" fields.)

 

[KenReay]

Hi

In my view 'numbers' should always be text fields unless they are to be used in arithmetic (eg Price, Quantity, Amount ...etc).

Regards

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

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

UK

 

[JeremyNYC]

Ken, I'm with you on that. Richard, thanks...I didn't think this was something you "missed", just something that happened to not be in that post.

J



==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.