auto incrementing id in access

[chandler]

I'm trying to create an auto incrementing custom id in Access without using autonumbering. Our job tracking number resets to 0000 at the beginning of every year and the year prefix changes accordingly as well. ie. 01-9999 to 02-0000. How do I create an auto incrementing id? I should also mention that I'm working with an existing table that has job tracking numbers in it already that use the autonumber feature. Since I can't reset an autonumber I'll need to implement the new method seemlessly.

Chandler
I ran over my dogma with karma!

 

[CFDude]

I'd suggest creating 2 new columns in your table. One would be the entry date that cannot be changed (you may already have this) and an ID field that is a number but not an autonumber.

Then to insert a new record, query the database for the largest number in the new number ID field, add 1, and insert the record with the new number.

e.g

<cftransaction>
<cfquery name=&quot;foo&quot; datsource=&quot;Bar&quot;>
Select MAX(NewJobTrackNumber) + 1 as NewIdNumber From MyTable
</cfquery>

<cfset tmpID = foo.NewIdNumber>
<cfquery name=&quot;foo&quot; datsource=&quot;Bar&quot;>
Insert Into MyTable(NewJobTrackNumber,otherfield,etc...)
Values(#tmpID#,'value','value', etc...)
</cfquery>

</cftransaction>

This will also enable you to scale up in case you move to a different DB.

Now it won't be stored exactly like you want, but you can format it when you go to display it.

such as :
#DateFormat(entryDateTime,&quot;YY&quot#-#NumberFormat(NewJobTrackNumber,&quot;0000&quot#

If you are displaying jobs that use the older numbers, do a simple cfif to see if the new ID field is null, and if so, then display the old ID number.

HTH,
Tim P.

 

[chandler]

Thanks for the tip. I figured out a way to do it concactinating the year portion of now() plus the number from a &quot;counter&quot; field into the jobnumber field which is now a text field. This way I'm only adding one field instead of two and the jobnum field will be stored in the jobnumber format.

What I still haven't figured out is how to have the counter automatically reset to 0 at the new year.

The users will need &quot;access&quot; to the Access database file every now and then and I'm sure they wouldnt' be able to figure it out otherwise. These aren't the sharpest crayons in the box. I'm not sure how scalable this method will be, so any more tips in that area would be much appreciated. I'd like to move the database over to our SQL Server. Chandler
I ran over my dogma with karma!

 

[CFDude]

Are you storing the date when you insert the new job num? (That was the main reason I suggested the EntryDateTime Field -- I usually like to have that anyway for auditing purposes -- )

How are you getting the counter? What I would suggest is when you grab the max number of the counter, grab the date of the counter as well -- if the year part of that date is less than the current year, then you know it has to be reset to 0.

Does that make sense?
Tim P.

 

[chandler]

He shoots he scores! Thanks for the assist Tim. Comparing the current year to the date field did the trick. I've also moved the counter field to it's own table and set it up so that it is only 1 record. That way at the new year, I just need to manually set 1 record to 0 should the other method have a problem. The counter field uses the max() function. Hopefully it will only be a few records that get effected should anything go foobar in my absence. Chandler
I ran over my dogma with karma!