auto increment

[Dorian]

It's not good database design to have hidden data in a column e.g. the year
'hidden' in a primary key. Stick with the autonumber primary key and put the
year into another column.
Does the sequence number really have to restart at 1 for each new year?
No, you cannot do what you want in table design, you would have to create
the key manually yourself.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Noëlla Gabriël]

Hi,

it's not because you need a unique field it has to be the primary key. The
same situation exists in invoice numbering, they have to be continuously
numbered and restart with 1 each year. The most used solustion for that is:
the pk field is, as usual, an autonumber which is hidden for the users. The
unique invoice number, or ticket number in this case, is build automatically
from the sysCounters table.
You can create this table with the following fields:
countID (autonumber) = PK field
countType (text or number if you have a related types table), in my case
this is a text field with the following 3 records: INV(invoice), CRN(credit
note), PRF(Pro Forma); in your case you need only TICKET
countYear (the year the counter is used, this makes it possible at the
beginning of a year to create a ticket for the last year, or to create
already a ticket for the next year at the end of a year)
countValue: the last used number.

all you have to do on creation of a new record is to use a dlookup function
to get the last number, build the correct ticket number with this number + 1,
and then update the counters table with this new last number.

Succes

 

[Jeff Boyce]

Noëlla

I've used a variation on your approach, but done away with the separate
table.

I have a [SequenceNumber] field (the same as your countValue) in each record
in my "main" table, as well as a [RecordYear] field.

When it comes time to add a new record, I first require the user (via a
form, of course!) to indicate the applicable year, then use a simple
function to find the largest [SequenceNumber] already used where
[RecordYear] matches the indicated applicable year. Then I add 1.

I have to futz with it a bit to handle the 'first record of the year'
situation, but that's not complex either.

Regards

Jeff Boyce
Microsoft Office/Access MVP