Primary Key dependant of another field

  • Thread starter Thread starter AlexH113
  • Start date Start date
A

AlexH113

Hi,
I need to be able to make my primary key dependent on a certain field. For
example, this is my current scenario:

We have six regions which we serve. So, we want for the Primary Key to
reflect which region they are coming from, just by looking at it.

Let's say John Doe comes and tells us that he is from the Tampa Bay region
(TMB). So, the first thing that we enter is the region, which it will then
create the ID, so that John Doe's ID would look like TMB2341.

What do I need to do in order to make it look that way.

Thanks.
J. Alexander Hewes
 
First, a quibble about terminology ...

A Primary Key serves as a unique identifier for a record/row of data in a
table. Nothing more. There is no inherent reason why it needs to be
comprehensible to humans. There is no reason it needs to convey
data/information to the 'reader'.

Then a quibble about design ...

Good database design calls for "one fact, one field". Attempting to stuff
two facts (region, plus ID) in a single field will, in the long run, cost
you more effort than you think.

It appears you've already settled on the approach you plan to take to
solving a business need/issue. If you'll post back a description of what
that business need is, folks here may be able to offer an alternate approach
that makes better use of Access' features/functions.

For example, if you're underlying business need is to:
1. uniquely identify individuals
2. categorize individuals by the region (??? in which they are registered
???)
then one way to do this is to use an Autonumber field for the ID, a foreign
key field for the region categorization, and a query to concatenate the two
together for display purposes. Note that what is stored (two separate
fields) and what is displayed (e.g. TMB123) are two different matters.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top