AutoNumber field reset to 1 at year end

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

russ7046 - 21 Feb 2005 16:03 GMT

I have inherited an access database that is designed to work as a tech
workorder system. IE unscheduled and scheduled workorders are generated and
then stored in a master WorkT table. Each time a new workorder is generated,
the WorkID field is incremented by 1. This all works well, but I would like
to reset that number to 1 at the beginning of a new year. That way I can
quickly see by max number how many workorders have been created for a
particular site/year. I have looked at the default table / query settings,
but can't find a way to modify the autonumber function.
Any help would be appreciated

Reply to this Message

Jeff Boyce - 21 Feb 2005 20:54 GMT

If you scan the tablesdbdesign newsgroup, you find a strong consensus
against using the Autonumber data type for anything other than what it was
designed to do, provide a unique row identifier to facilitate setting
relationships between rows in tables.

It sounds like your application is using an Autonumber type to stand in for
a sequence number. The dead give-away is that you want it to "reset" at
point in time.

Consider keeping the autonumber as a unique row identifier, but adding a new
"SequenceNumber" field. You can find (via Google.com and/or at the
mvps.org/access website) references to "custom autonumber" routines that
will generate sequential numbers. The gist of the routines is to find the
largest sequence number so far, add one, and assign that to the new record.
If you need to, you can (slightly) alter the routine to first check for the
year (or any other 'break point'), and re-start the numbering.

Signature

Good luck

Jeff Boyce
<Access MVP>

> I have inherited an access database that is designed to work as a tech
> workorder system. IE unscheduled and scheduled workorders are generated and
[quoted text clipped - 5 lines]
> but can't find a way to modify the autonumber function.
> Any help would be appreciated

Reply to this Message

russ7046 - 21 Feb 2005 21:57 GMT

> If you scan the tablesdbdesign newsgroup, you find a strong consensus
> against using the Autonumber data type for anything other than what it was
[quoted text clipped - 27 lines]
>
> thanks for the info, I will see what I can find

Reply to this Message

David C. Holley - 22 Feb 2005 20:01 GMT

If the site information and date/time that the workorder is created is
stored in the DB, why not just run a query on the table? It'll save a
lot of time. And BTW you can use a function as an output field of a
query to extract the year in which a WO was opened.

Reply to this Message