Increment Index

[Alylia]

I am trying to design a table which should have a specific index format that
increases by 1 whenever a new record is added to the table.

The index should start from 05TYT-001 increasing by 1 a new record is being
added. It should be noted that the 05 in front of the TYT is dependent on the
last number of the current financial year (July04 to June05) which is denoted
by three characters followed by 05 (e.g ALV05).

Therefore waht I really need support on how to come with an index that would
be 05TYT-_ _ _ , and then be 06TYT- _ _ _ come the start of the next
financial year which should start in July 2005.


Thx

 

[Jeff Boyce]

Alylia

Your meaning for "index" and Access's meaning for "index" don't necessarily
match.

From your description, it sounds like you want a customized unique
identifier that would be used/seen by the user.

First, it is considered poor database design to stuff more than one fact
into one field. You are proposing to stuff three facts into one field.

Next, it is generally unnecessary (and there are good reasons not) to put
"calculated" values in fields in your tables. If you include a "date/time"
field on your records for when the record was created, you don't need to
store the "year" of that date also. Since you described what sounds like a
fiscal year, you could use a function to calculate the fiscal year of any
date -- use the function in queries, forms and reports, instead of storing
the calculated value.

And finally, if you want to "display" something like "05TYT-001" you can
simply concatenate together the individual fields storing the underlying
data. To get that last "sequence number", check Google.com or the
mvps.org/access/ website on the topic of "custom autonumber" (yes, I know,
this is a misnomer, but that's how you'll find it!)