How do I make a multi-part autonumber?

D

Dedren

How do I make a multi-part autonumber or similar incrementing unique value?
Example:
F05-001
F05-002

The "F" is the type of case it is, which comes from another value in the
same record
The "05" is the year it was opened, which comes from another value in the
same record
The "-001" is the value that increments per record

We have been trying for a week now to think of something best we could come
up with was making seperate tables for each part of the number that would do
a sort of 'if' statement:
IF (casetype=M) PUT M
IF (casedateyear=2005) PUT 05
THEN increment (ending+1=ending)

I would appreciate any bit of advice anyone could give me cause I am stumped.
 
D

Douglas J Steele

You don't.

"Smart keys" (the term's slightly derogatory) aren't recommended. You're
trying to store 3 pieces of information: use 3 separate fields. You can
create a query that concatenates them together for display purposes if you
really need to, and use the query wherever you would otherwise have used the
table, but storing multiple pieces of information in a single field is
actually a violation of relational database rules.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dedren said:
How do I make a multi-part autonumber or similar incrementing unique value?
Example:
F05-001
F05-002

The "F" is the type of case it is, which comes from another value in the
same record
The "05" is the year it was opened, which comes from another value in the
same record
The "-001" is the value that increments per record

We have been trying for a week now to think of something best we could come
up with was making seperate tables for each part of the number that would do
a sort of 'if' statement:
IF (casetype=M) PUT M
IF (casedateyear=2005) PUT 05
THEN increment (ending+1=ending)

I would appreciate any bit of advice anyone could give me cause I am
Click to expand...
stumped.
 
P

peregenem

Douglas said:
"Smart keys" (the term's slightly derogatory) aren't recommended.
Click to expand...

"Intelligent keys" (the non-pejorative equivalent), such as the ISBN,
are recommended.
 
C

chriske911

How do I make a multi-part autonumber or similar incrementing unique value?
Example:
F05-001
F05-002

The "F" is the type of case it is, which comes from another value in the
same record
The "05" is the year it was opened, which comes from another value in the
same record
The "-001" is the value that increments per record

We have been trying for a week now to think of something best we could come
up with was making seperate tables for each part of the number that would do
a sort of 'if' statement:
IF (casetype=M) PUT M
IF (casedateyear=2005) PUT 05
THEN increment (ending+1=ending)

I would appreciate any bit of advice anyone could give me cause I am stumped.
Click to expand...

easiest way is to write a module and let it generate a new number in
the before update event of the record

you can lookup the current max number from the table by using dmax or
by keeping a follow number in a separate table and update it every time
you generate a new number

then use all kind of formats to concatenate them to a unique number

grtz
 
Top