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 question
I am creating a database and I would like to create a unique primary
key by combining part of one field with another. For example, in the last name field is the name SAUNDERS and in the last four field are the numbers 1234. I would like to take the first two letters of my last name and combine them with the 1234 to create a unique field. So this unique field (my primary key) will be SA1234. Is this possible? |
#2
|
|||
|
|||
Primary key question
Anthony
!STOP! A basic principle of database design is "one fact, one field". You do not want to try to stuff two facts into a single field. If what makes your records unique fits in two fields, use two fields as your (multi-column) primary key. You are not limited to using just one field. By the way, the names of people are notorious for not being unique. And even if you are using something in your [last 4] field that you believe to be unique (e.g., social security number), you risk trying to add duplicate rows. If you need a unique (and non-meaningful) row identifier, take a look at the Autonumber data type. Regards Jeff Boyce Microsoft Office/Access MVP "Anthony" wrote in message ... I am creating a database and I would like to create a unique primary key by combining part of one field with another. For example, in the last name field is the name SAUNDERS and in the last four field are the numbers 1234. I would like to take the first two letters of my last name and combine them with the 1234 to create a unique field. So this unique field (my primary key) will be SA1234. Is this possible? |
#3
|
|||
|
|||
Primary key question
In message
, Anthony writes I am creating a database and I would like to create a unique primary key by combining part of one field with another. For example, in the last name field is the name SAUNDERS and in the last four field are the numbers 1234. I would like to take the first two letters of my last name and combine them with the 1234 to create a unique field. So this unique field (my primary key) will be SA1234. Is this possible? It's certainly possible but whether it's desirable is another question altogether. You could extract the first two characters of one field and store them in a new derived field then either append the contents of the second field or just create a composite key. This would de-normalise the table structure but although generally undesirable that's not a hanging offence. The real problem is that there are many different names that have the same first two characters, so this part of the key doesn't contribute towards the uniqueness of the key value. And if the numeric field alone is sufficient to guarantee uniqueness then you don't require those two characters as a prefix. There may be some reason why you particularly want to have a PK with that format, but it isn't obvious from your post. Whatever the aim, there are probably better ways to achieve it. -- Bernard Peek |
#4
|
|||
|
|||
Primary key question
On Sep 29, 1:25*am, "Jeff Boyce" wrote:
Anthony !STOP! A basic principle of database design is "one fact, one field". *You do not want to try to stuff two facts into a single field. If what makes your records unique fits in two fields, use two fields as your (multi-column) primary key. *You are not limited to using just one field. By the way, the names of people are notorious for not being unique. *And even if you are using something in your [last 4] field that you believe to be unique (e.g., social security number), you risk trying to add duplicate rows. If you need a unique (and non-meaningful) row identifier, take a look at the Autonumber data type. Regards Jeff Boyce Microsoft Office/Access MVP "Anthony" wrote in message ... I am creating a database and I would like to create a unique primary key by combining part of one field with another. *For example, in the last name field is the name SAUNDERS and in the last four field are the numbers 1234. *I would like to take the first two letters of my last name and combine them with the 1234 to create a unique field. *So this unique field (my primary key) will be SA1234. *Is this possible?- Hide quoted text - - Show quoted text - Thanks Jeff. So you do not suggest combining two fields. The field that I was trying to create is what we call a Soldier's battle roster number and it combines the first one or two letters of a last name (depending on your command) with the last four of the SSN. These battle roster numbers are typically unique but I can see that there is a chance there could be a duplicate every now and again. I will use the autonumber as a primary key. Thanks again. |
#5
|
|||
|
|||
Primary key question
You can combine the fields in a query or other expression whenever needed.
For instance, in a blank column in query design view: SoldierNumber: Left([Lname],2) & Right([SSN],4) I suppose you could do something like this in a query: SELECT DISTINCT Left([Lname],2) & Right([SSN],4) AS SoldierNumber FROM YourTable Then compare the number of records with the total number of records in YourTable (assuming one record per person in YourTable). If the counts are different (i.e. fewer in the query than in the table), there is duplication somewhere. However, that could be troublesome to maintain. If the PK is autonumber you can still use an expression to generate the SoldierNumber value, but there is no guarantee it will be unique. Most likely it will be, but you will have to be vigilant if you choose to go that route, and you will need an alternative if duplication happens to occur. Anthony wrote: Anthony [quoted text clipped - 27 lines] - Show quoted text - Thanks Jeff. So you do not suggest combining two fields. The field that I was trying to create is what we call a Soldier's battle roster number and it combines the first one or two letters of a last name (depending on your command) with the last four of the SSN. These battle roster numbers are typically unique but I can see that there is a chance there could be a duplicate every now and again. I will use the autonumber as a primary key. Thanks again. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200909/1 |
Thread Tools | |
Display Modes | |
|
|