numbering system

[pntrpal]

I have a database that keeps track of substation outages.
Each outage is numbered with a chronological number for
each year beginning with "1" and starts over with "1" at
the beginning of each year. (therefore it is NOT the PK).
I have a form where data is entered and there is a command
button "enter new outage". When this is selected, a new
blank form opens and the new outage number [outage#] is
automatically entered by the code ([maxOutage] + 1) based
on an invisible field named [maxOutage] and its control
source =MAX([outage#]). This works fine now because I
have 2 tables named CurrentYear and Archive which means
only the current year is stored in the CurrentYear and
moved over to the Archive table at the end of each year
(date fields are also part of the entry process on the
form so there isn't a problem with duplicate outage
numbers in a given year once it gets Archived.

However, this seems a bit redundant (and cumbersome to
move each year) to me since the tables are identical and
all reports (and there are several of them!) are
duplicated because they are based on either the
CurrentYear table and/or related queries, or the Archive
table and/or its related queries.

My question is, if I were to combine the two tables into
one, is there still a way to auto-enter the outage number,
beginning with "1" at the beginning of each year. The
chance of error or getting out of sequence would be
greater if the number were to be manually entered.

 

[John Vinson]

I have a database that keeps track of substation outages.
Each outage is numbered with a chronological number for
each year beginning with "1" and starts over with "1" at
the beginning of each year. (therefore it is NOT the PK).

What is? You *could* have two fields, the Year and the OutageNo - and
they could constitute a joint two-field Primary Key.

...However, this seems a bit redundant (and cumbersome to
move each year) to me since the tables are identical and
all reports (and there are several of them!) are
duplicated because they are based on either the
CurrentYear table and/or related queries, or the Archive
table and/or its related queries.

Right you are!

My question is, if I were to combine the two tables into
one, is there still a way to auto-enter the outage number,
beginning with "1" at the beginning of each year. The
chance of error or getting out of sequence would be
greater if the number were to be manually entered.

Yes; modify your code just a bit to use

=NZ(DMax("[OutageNo]", "tablename", "[OutageYear] = " & Year(Date()))

Don't use Year as the fieldname since it's a reserved word.