year prefix on primary key

[Guest]

I would like to automatically add the last two digits of the year to each new
primary key when a new record is added to a table.

Eg.

If a new record is added to a table with an autonumber field the keys will be:
1,2,3,4,5.....

I would like to make the primary contain the prefix of the year, e.g.

06/1, o6/2, 06/3........06/1234 and so on.

Does anyone know how I can program an interface to do this?

In VBA for example?

 

[Guest]

Create a new field to carry that. Make the Primary key the new field and the
autonumber field.
However, that may not be necessary. Just have all your queries fiter on the
new field.

 

[Douglas J. Steele]

Why? As in what's wrong with having two separate fields?

If you need them concatenated for presentation purposes, do that via a
computed field in your query.

Storing multiple pieces of data in a single field is actually a violation of
relational database theory.

 

[Guest]

I honestly dont understand your advice.

How do you propose I get the autonumber field to generate a new number with
the two digits of the year ("06") concatenated at the start of the new
number????

i.e when a new record is created the next number is

06/12345...........06/12349 etc

 

[Guest]

I dont think that storing the year prefix at the start of a primary key is a
violation of data base theory.

I work with large Oracle databases for local Government organisations and
this is common practice. It helps the average user to identify the year a
record was created.

Anyway, I dont think having two separate fields will suffice.

I also, think a query will be very slow running, especially if it has to run
the concatenation on all 20,000 records in the data table. I need to find a
way to either instruct MS Access to create the autonumber with the year
prefix or somehow program VBA to create the new primary key.

Any thoughts??

 

[Rick Brandt]

I dont think that storing the year prefix at the start of a primary key is a
violation of data base theory.

Doesn't matter. It's still a bad idea. if you want the number to start over
each year then with *Separate* fields for the RecordDate and the RecordID it is
easier to efficiently determine the next number to use.

=Nz(DMax("RecordID", "TableName", "RecordDate BETWEEN DateSerial(Year(Date()),
1,1) AND DateSerial(Year(Date()),12,31)

If you have "YY/" added to the front of your ID you now have to use a much more
convoluted and much less efficient method to determine the next number.

 

[Guest]

Thank you for the reply.

So I guess everyones advice is to have the year separated from the primary
key.

How best should I do this?? in VBA??

 

[Rick Brandt]

Thank you for the reply.

So I guess everyones advice is to have the year separated from the
primary key.

How best should I do this?? in VBA??

Do what? Separate them into two fields? You would do that in the table design
view. Otherwise clarify your question.

 

[John Spencer]

The speed of the database shouldn't be affected much, if at all, by
concatenating the values. You do the concatenation only on the returned
records. You do searches against the separate fields. So if you want 2006
records between 10 and 450, you search two fields - you don't search against the
two values concatenated together.

 

[Guest]

Thanks to all responding to my question.

I will give this a go and let you all know the out come of the programming
and how the users take to it.

Many thanks.