Best Method?: For Resetting sequential numbering . .. . .

[The ScareCrow]

Hi Leonard,

I don't know about mysql but the ms access help (search autonumber) gives a
detailed instruction on how to do this. But basically you need to delete the
autonumber column and add a new one.

I see major problems with this if there are other tables joined by this
autonumber column.

Why do you need to do this ? I can see no valid reason for this as the
autonumber column is to establish a key field to identify the record.

Ken

[Leonard B]

Hi Ken -

Thanks for your response. I will try to answer your questions.

This project is for a public service entity. As with most entities they log
their incident reports
with a sequential number, which starts off at one (1) for at the beginning of
each year.

Ex: Incident Number 05-123, 05-124, 05-125

I totally agree with you, in messing with the autonumber field could lead to a
major
problem down the road and it is not my intent to do so. Perhaps I was not
clear on that
point of things.

If all can be done, there would a separate field to contain the actual
incident number,
which is the one that would be incremented, regardless of what happens to the
autonumber field. It is this additional field that I would like to reset to
zero at the start
of a new year.

Example:

RecordID / IncNum / Title
1 / 1 / Title 1
2 / 2 / Title 2 <--- Year Ends
3 / 1 / Title 1 <--- Year Starts
4 / 2 / Title 2

And so on.

I hope this answered your question.

Thanks -

Leonard B

[PaulH]

a fairly common way of doing this in a portable manner is to use another table
to handle the sequential number. if the db is trigger capable a simple update
would update the sequence and return it to the calling app. if not then it
bcomes a bit more complex. if the db is capable, you could schedule a
procedure/script to zero that out every jan-1, if it's not capable then do it
via cf's scheduling.

[The ScareCrow]

While Paul has given a couple of options, I'm going to add another but it is
base on an assumption.

The id number appears to have the year prepended to it. If this is correct
then you could do the following
Also I assume that you have a standard autonum column in the table.

When you need to insert a new record

Select the max standard autonum column, this should then give you the maximum
incident number (although you could create a query solely on this column to
select the max number after the "-")

Then to create this incident number, just check if the year(now) is the same
as the first bit of the column (before the "-")
If it is, just increment the integer (part after the "-")
If its not (which should only happen once) then it's a new year, start from
one (1)

I might not have explained this correctly, but I can see it in my head. So if
you rerquire more info let me know.

Ken

[Leonard B]

Greetings -

I took imput from both of you guys (Ken and Paul), and found a solution to the situation.

Thanks guys for the input, got me on track.

Leonard B