AUTONUMBER- but with a custom alphanumeric

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We are designing a new database for filing, tracking, and updating projects.
These projects will have an alphanumeric id which will something similar to
2005DR1, 2005DR2, 2005DR3, then 2005DP1, 2005DP2, etc. I can build something
that will create this alphanumeric programmatically but if anyone knows of a
convenient way to create this using some existing Access functionality-
please let me know!!!
Thanks!
 
I forgot to mention that this id will be used to find records, listed in
reports, and possibly be the primary key as well. Thanks!
 
These projects will have an alphanumeric id which will something
similar to 2005DR1, 2005DR2, 2005DR3, then 2005DP1, 2005DP2, etc.
Click to expand...

This looks suspiciously like two or three separate piece of information.

If the 2005 refers to a year, then it should be in an integer field
called YearNumber.

If the DR is always DR, then it should not be in the database at all. If
it means something and will sometimes change, then it should be in a
small text field called something like TwoLetterDepartmentCode.

If the number has to recycle with every new YearNumber and
TwoLetterDepartmentCode, then you can use a plain integer (or long) but
you'll need some code to allocate it safely.

It's trivial to find a record, as in

SELECT Something FROM Somewhere
WHERE YearNumber = 2005
AND TwoLetterDepartmentCode = "DR"
AND SerialNumber = 003


and if you ever need to

GROUP BY TwoLetterDepartmentCode

then you'll be stuffed if you have stuffed it into the middle of an
"intelligent key". Read up on First Normal Form for more details.

For presentation to the user, you create a control on the form or report
and point its ControlSource to

= YearNumber & TwoLetterDepartmentCode & Format(SerialNumber,"000")

Hope that helps



Tim F
 
Yes, most helpful because I hadn't thought about a group by the two letter
code! I would have probably just tried to get it using string
manipulation...and maybe that is not possible in a group by?? Thanks!
 
Yes, most helpful because I hadn't thought about a group by the two
letter code! I would have probably just tried to get it using string
manipulation...and maybe that is not possible in a group by??
Click to expand...

It's more than just that. On superficial examination, there are often ways
to "work round" a non-normalised design but I fail to see why anyone would
want to. It's simply easier doing it right than doing it wrong, and I'm
lazy so I want to get the normalisation correct in the beginning.

All the best


Tim F
 
Back
Top