If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Primary Key dependant of another field
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 |
#2
|
|||
|
|||
Primary Key dependant of another field
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 "AlexH113" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|