AutoNumber field reset to 1 at year end

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Jeff Boyce said:
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.

--
Good luck

Jeff Boyce
<Access MVP>



thanks for the info, I will see what I can find
Click to expand...
 
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.
 
Back
Top