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.

--
Good luck

Jeff Boyce
<Access MVP>

"russ7046" <(E-Mail Removed)> wrote in message
news:EEB8A60F-821D-4B94-B354-(E-Mail Removed)...
> 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

"Jeff Boyce" wrote:

> 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>
>
> "russ7046" <(E-Mail Removed)> wrote in message
> news:EEB8A60F-821D-4B94-B354-(E-Mail Removed)...
> > 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
>
> thanks for the info, I will see what I can find

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.

russ7046 wrote:
> 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