Number Field Question

[Montana Rain]

I am being asked to help create a database and I hit a brick wall
right off the bat because of the the requirement for a particular
number sequence. The data base will basically be a log of incoming
requests to a 911 center from local law enforcement agencies. The
field looks like this, "2009-021". The 2009 is obviously the year and
the 021 is the request number. At the beginning of each calendar year,
the year will change and the request number starts over at 001.
I am told the number of entries is about 300-400 each year.

Can anyone help with how to create this field in my table?

Thanks in advance.

 

[Tom van Stiphout]

On Fri, 20 Feb 2009 05:02:57 -0800 (PST), Montana Rain

I would create the sequential number by reading the next number to use
from a separate table (with one row of data, and so far one field
NextSeqNo) using a recordset (read the value, increment it by 1). Then
I would store the CallDate in some field, and I would use a query to
concatenate Year(CallDate) with the SeqNo.

-Tom.
Microsoft Access MVP

 

[Fred]

The answer to only what's literally in your question would be to make a text
field and have your people type that in.

If you are talking automatic entry, you'll want to start by making these
decisions:

- Do you want the year to be automatically entered based on the date of the
entry of the record? For example if you enter a record on 1/1/10 for
something that happened 12/31/09, do you want 2009 or 2010 in there?

- Do you want to manually assign the request numbers (with a control for
uniqueness) vs. using autonumber vs. something fancier. E.G. if somebody
mistakenly double enters a request as 021 and 022, then deletes 021, do you
want the ability to type in 021 for the next one vs. the computer picking
023?

 

[Fred]

Just to clarify, I was responding to your OP, and at the time hadn't seen
Tom's response.