Generate numbers with certain criteria

[heddam]

I am trying to create a database that generates somewhat random numbers based
on a given criteria. The first three numbers are based on the dept code. The
next number is based on the funding source. The next number is based on the
type of activity and the last two numbers/letters are sequential/random.

I'm not even sure where to get started.

 

[Douglas J. Steele]

My advice would be "don't do it".

The creation of so-called "smart keys" hasn't been recommended for years.
Realistically, you're trying to cram 4 pieces of information into a single
field, something that actually violates database normalization principles.

Instead, store the four separate fields in your table. If you need the
formatted key for backwards compatibility, you can always create a query
that calculates the key (using Format and text functions such as Left and
Mid) and use the query wherever you would otherwise have used the table.

 

[heddam]

I have no choice but to create the "smart-key" you are talking about. The
criteria we use determines our project numbers. Right now we hand write all
the numbers into three 3-ring binders. I would like to jump into the 21st
century here. Is there something else that would work better?

 

[Douglas J. Steele]

I already gave you the alternative: store the four separate pieces of
information as separate fields, and use a query with a calculated field to
generate the key for display purposes.