Add date prefix to autonumber..

[nerdgnome]

Hello!

How do i add a date prefix to an auto number.

The Auto-Number is a PK and already exists, the existing data can remain unchanged. I want to add a year prefix and reset the counter to 0 at the start of each year. eg. 07-0001 Is this possible?

All help greatly appreciated.



Bee
temp, thrown in at the deep-end....again!

 

[rac2]

Ah yes. It is not possible. At least not with an autonumber field.

That must be done in the application, not in the database. You will need to build the yearly reference value in code. You might create a table with a column for the last value used for the annual counter and the year it applies to. Build the reference value from the year part of the date today concatenated with the next counter value; update the counter table; store the reference value with the other data for the thing you are tracking. Part of the caluculation of the next counter value will involve checking that it is stll the current year for the counter; when the year changes, then reset the counter.

An autonumber field is only intended to identify rows in a table, the value is completely arbitrary, it simply means "this row". When you wish to have an identifier which encodes some meaning such as the 3000th order in 2007, you should create a colum for that attribute of the things in your table.

 

[nerdgnome]

Ahh ok.

Thanks

Bee
temp, thrown in at the deep-end....again!

 

[Darrylles]

Hi toxic,

Add a string field to your table (e.g. date_id).

When a new record is added, use this to create your required identifier field value:

date_id = str(date) & "_" & str(PK)

atb

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[jimirvine]

If you are using this method (which I would reccommend against as it is a calculated value and should not be stored), you'll need to remember that the update of the reference field must be done, not only when a row is inserted into the table, but also when an update is performed on the table that may involve a year different to that which was originally entered as part of the prefix.
Other issues:
Using an autonumber for this purpose. It looks like you want to have a sequential number, which an autonumber field cannot guarantee i.e. it is possible to have missing numbers in the field. Also what happens if a row is deleted (let's say the 150th 'sale' of the year, then is added back in, but in between, another 'sale' has been added. The original sale will now be out of order. Use the dmax function to generate automatically incrementing sequential numbers.

HTH
Jim

 

[Darrylles]

Hi jim,

I don't agree that this method creates a 'calculated' value (not in the context that you mean).

i.e. You could never create a non-unique date_id.

The reason that 'calculated' values are not stored, is simply because of efficiency i.e. why store something that you can always calculate?

In this case - you can not recreate the same 'value' again in the future.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[jimirvine]

Hiv Darrylle

I don't agree that this method creates a 'calculated' value (not in the context that you mean).

And I absolutely respect that.
But to rebutt:

Well, the context that I mean is that it would be creating a column that is functionally dependant on another column (in this case the PK column, and potentially the 'Year' column)

The reason that 'calculated' values are not stored, is simply because of efficiency i.e. why store something that you can always calculate?

'fraid I don't agree with that. If that were the only reason not to store calculated values then that could easily be refuted by pointing out that it would ONLY be more efficient during Inserts, it would actually be (and is) less efficient when querying the data to have the component values stored and the required value calculated on the fly. However, the second method (storing the components and calculating them on the fly) would guarantee the integrity of the data (which is the single most important thing when storing data).
Take an example of sales of a particular product. The user enters The unit price and the no. of units for that sale. If the total cost of the sale were calculated and stored, then if the record was found to be in error (let's say there were actually 8 widgets bought not 80) when the no. of units was changed to be the correct value of 8, the Total sale cost would be incorrect, this would involve addidional constructs to automatically handle updates as well as inserts (and possibly even deletes depending on the structure of the calculation)

In addition, the value of 07_001 (or whatever) is not atomic. It constitutes 2 separate attributes of the entity i.e. Year and Number. These should be stored as such. You would not (I hope) store first name and last name in the same field, why start with these values?

i.e. You could never create a non-unique date_id.

I didn't say that you could (at least I don't think my post implies that ) What I did imply was that the use of Autonumber would also be a bad idea for the generation of this value as it cannot guarantee to be sequential. Maybe I'm misunderstanding what you actually mean.

(This might get interesting )

 

[Darrylles]

Hi Jim,

Yawn.

Have you ever tried to apply the usual University 'relational database design methodology' in a true working environment?

Toxic asked a question - he has reasons for wanting it this way. I assume he wants a 'user-friendly key'.

This way CAN work if he does not attempt to reset autonumber every year (why bother).

The actual autonumber record value NEVER needs to relate to this 'user-friendly key' after it has been produced. A 'user-friendly key' is just that - user displayed, i.e. it never TECHNICALLY identifies a record within a database.
A portion of this 'friendly-key' is NOT functionally dependant on another column - and need never be - why should it? It's actually disposable, it's simply a 'recordable' user-viewable label for a record.

IF the Autonumber key for that record changed, the user friendly key need not change - it has been recognised against that record content by users for a period and should not be changed.

I stated 'because of efficiency'. Do you really believe that ensuring data-integrity does not increase efficiency?
Do you only associate 'efficiency' with 'speed of execution'?

'Constitute' means 'make up or comprise'. 07_001 does not constitute 2 attributes, 2 attributes constitute 07_001.
07_001 'consists of' or 'is comprised of' 2 attributes.
In any case, where is '07' defined as the 2nd attribute - anywhere within the entity? It is certainly not stored anywhere else.

Why do you say that 'Autonumber' can not be guaranteed to be sequential? Do you know something that I'm not privvy to? (If you're referring to re-setting the Autonumber every year - then I understand, otherwise, Autonumber is ALWAYS sequential - no?).

(Was that interesting enough?)

Regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[lespaul]

Autonumber is ALWAYS sequential - no?

No, the Autonumber is NOT ALWAYS sequential, which is why it doesn't work well for everyone. If you use the autonumber feature and begin a new record but then change your mind, you will have a missing number.

If you accidentally put a batch of bad records in your table and then delete them, you will have a large number of missing "numbers". So, you have a brand new table, you input 500 records, your autonumber is 1 - 500. You then put another 500 records in (so now we're at autonumber 1000), but realize that you need to delete the second batch. Your next autonumber will be 1001.

If you need a sequential number, the autonumber is NOT the way to go.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Darrylles]

Hi Les,

Thanks.

I thought that 1,2,3,500,501,765,865 was sequential; not contiguous, but sequential.

What's the difference between 'contiguous' and 'sequential' then?

ATB

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

Hi,

I kind of answered my question myself:

1,2,3,501,502,503,1001,1002,1003 etc IS sequential.

As the Autonumber function supplies integer values starting from 1, incremented by 1, then it follows that all supplied numbers must be 'contiguous'.

This in relation to the thread question doesn't matter of course - as long as the user can determine the 'age' or 'order' of the record.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[jimirvine]

Darrylles.

Yawn

I got into this conversation because I thought that you might be up for a decent debate on the subject rather than being condescending and quite frankly obnoxious about it. What, you don't like your opinion being questioned? Live with it.

Why do you say that 'Autonumber' can not be guaranteed to be sequential? Do you know something that I'm not privvy to? (If you're referring to re-setting the Autonumber every year - then I understand, otherwise, Autonumber is ALWAYS sequential - no?).

I thought that 1,2,3,500,501,765,865 was sequential; not contiguous, but sequential.

What's the difference between 'contiguous' and 'sequential' then?

It would appear that both I, lespaul and many other people do know something to which you are not privvy.

Sequential - consecutive: in regular succession without gaps
You will find that contiguous is actually a poor definition of what you are trying to imply, in that most definitions will describe contiguous in the placing of PHYSICAL entities and in addition actually tends to mean 'near or close to' Maybe you actually meant conterminous i.e connecting without a break, or possibly consecutive i.e. SEQUENTIAL.

Have you ever tried to apply the usual University 'relational database design methodology' in a true working environment?

Yes, and with success. Have you? Or couldn't you get it to work?

Toxic asked a question - he has reasons for wanting it this way. I assume he wants a 'user-friendly key'.

This way CAN work if he does not attempt to reset autonumber every year (why bother).

And I assumed that he wanted a sequential (and by that I mean - in your somewhat anal parlance - consecutive) set of values which an Autonumber would NOT guarantee and based on his requirements, your solution would require the autonumber be reset each year. since he wants the number portion to start at 1 at each new year.

The actual autonumber record value NEVER needs to relate to this 'user-friendly key' after it has been produced.

you are absolutely right, apart from the fact that it is built from the autonumber, but yes, point taken once you have used the value of autonumber, they are no longer related.

it never TECHNICALLY identifies a record within a database.

True, but then, I didn't say that it did.

I stated 'because of efficiency'. Do you really believe that ensuring data-integrity does not increase efficiency?
Do you only associate 'efficiency' with 'speed of execution'?

fair point, I obviously picked you up wrong in what you were saying.

(Was that interesting enough?)

No, I found your attitude towards a new poster trying to put forward an opinion quite obnoxious.

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

I quite agree. I'm out

 

[r937]

toxictea, if changing databases is an option, you should know that mysql can do this (restart an autonumber for each year) automatically



r937.com | rudy.ca