Unique sequential references

[neil45156]

Hi everyone,

First time here, so here go's.

I want to be able to setup a primary field which allows you to have a unique and sequential list in a table such as Q110211C and then the next one to autoenter Q110212C etc, like you get with AutoNumber.

Is this possible in Access 2003?

Thanks

Neil45156

 

[Galaxiom]

Do the Q and C ever change?

If not then just store and increment the number and add the Q and C with the Format property of the field/control or by concatenation.

 

[HiTechCoach]

You can use the DMAX() to get the last value in the sequence and then add 1

 

[neil45156]

Hi
Thanks for the reply. The Q and the C never change, but I am not sure what I should be setting up the field type as? Is it autonumber, number or a text field type?

Thanks
Neil45156

 

[Rabbie]

Hi
Thanks for the reply. The Q and the C never change, but I am not sure what I should be setting up the field type as? Is it autonumber, number or a text field type?

Thanks
Neil45156

If you store the Q or C in the field then it should be a Text field. If you just store the number then I would use a long integer field.

Autonumber is NOT the type to use here.

 

[neil45156]

Hi Rabbie,

If I set it to a text field, how do I make it increment automaticaaly?

Thanks

 

[HiTechCoach]

Hi Rabbie,

If I set it to a text field, how do I make it increment automaticaaly?

Thanks

You would have to convert the text to a numeric value add 1 then convert it back to a string. Sounds like a lot of extra overhead that can be avoid if you would use a numeric data type as previously recommended.

Another reason to use numeric data types: The have better performance with index and searching.

I would also still use an auto number for the primary key and use it in all relationships. I would set your custom auto sequencing field to be indexed with no dups. It would not the primary key or used in relationships. Especially if you are making it a text data type. To the user it can appear that this is the primary key, but in it really isn't. I use this method in many systems with great success since Access 2.0 and with SQL Servers.

 

[ChipperT]

You would have to convert the text to a numeric value add 1 then convert it back to a string. Sounds like a lot of extra overhead that can be avoid if you would use a numeric data type as previously recommended.

Another reason to use numeric data types: The have better performance with index and searching.

I would also still use an auto number for the primary key and use it in all relationships. I would set your custom auto sequencing field to be indexed with no dups. It would not the primary key or used in relationships. Especially if you are making it a text data type. To the user it can appear that this is the primary key, but in it really isn't. I use this method in many systems with great success since Access 2.0 and with SQL Servers.

You could also use an autonumber for this, starting at whatever value you want, and then simply prepend the "Q" and append the "C" when you need to display the numbers in your app. The Q and C would exist nowhere but in your display (or in a "calculated" field in a query of you need a tabular display).

 

[gemma-the-husky]

neil the underlying problem is that there is no "successor" to a text value

eg - take your name "neil" - what is the next value after "neil" ....

So - in order to be able to have an incrementing value, you need to use a number.

And as pointed out, a number wrapped in letters, is no longer a number - and you have to jump through hoops to get at the number bit.

 

[HiTechCoach]

You could also use an autonumber for this, starting at whatever value you want, and then simply prepend the "Q" and append the "C" when you need to display the numbers in your app. The Q and C would exist nowhere but in your display (or in a "calculated" field in a query of you need a tabular display).

That might be true in this situation. but it will not help eliminate gaps common with the auto number. That also assumes that the number will always be assigned/used in order.

I like to use a separate priamry key field that is system assigned. Why? what it they decide to change the way the number stuff. I have hard to switch methods for several different clients. One went for a similar type sequence the OP used to here the first part is a year number followed by a department number followed by a sequential number for that year. Since i use a separate field for this it was easy to do. I could even recreate all the old ids and the system would still work just fine. since the real primary key never changed.

 

[Galaxiom]

I like to use a separate primary key field that is system assigned. Why? what it they decide to change the way the number stuff.

One went for a similar type sequence the OP used to here the first part is a year number followed by a department number followed by a sequential number for that year.

I am usually a fan of Natural Keys when they are available. However I agree with HTC particularly on the example he mentions. System specifications that include pointless arbitrary sequences like that are highly prone to equally arbitrary changes.

Indeed that combination is technically a derived value and breaches normalization. I don't see the point of adding the information twice and when the person moves to another department they no longer fit the pattern anyway. (That is usually when some bored irrelevant executive decides the numbering system needs changing.)

Another factor that drives this kind of change is the merger. This can lead to problems not only with different numbering systems but also duplicate keys. It is obviously a lot easier to modify the client number if it isn't autonumbered.

Anything that requires incrementing a partial string is a pain whether that string manifests entirley as a number or not. Incrementing becomes a very slow process of parsing the field and comparing all values.

Although I am not a big fan of DMax and prefer the technique of storing and incrementing the next value in a table, at least a DMax on the whole value in an indexed field is very fast. Incrementing partial values is the riskiest technique in a multiuser environment because it is so slow and the chance of repeating the number due to simultaneous record creation can be a thousand times larger on a big table.

Things like the last three digits as a sequence appended to some other derived value are horrible. Usually designed when the company is small and "would never need more than 999" is a classic case of a Y2K style problem waiting to happen.

I also eschew any use of autonumber for something that means anything to the user. It just cannot be relied on to provide a sequence. They can also just take off at a random point. Indeed I avoid them almost everywhere because they can and do break mainly by repeating previously used numbers.