none
Using two short text fields as Primary Key RRS feed

  • Question

  • I am a small business owner and am attempting to build an MSAccess DB for our customer info.  It will have multiple tables and different sources but all the info relates to a customer's LastName and FirstName.  Much to my surprise, I am unable to designate the two different fields as Primary Keys in Design View.  Each field contains duplicates; however, the two field together uniquely identify a row/record.  Can someone tell me what I am doing wrong?  Thanks!
    Friday, March 31, 2017 6:38 PM

All replies

  • Hi,

    Although what you're trying to do is possible, it would be simpler, in my humble opinion, to use a single numeric field as a primary key.

    However, if you insist in using the two fields, go to the Index window and select the second field just below the first one without specifying an index name.

    Hope it helps...

    Friday, March 31, 2017 6:53 PM
  • I don't think FirstName+LastName is really safe as a primary key, as there are lots and lots of people with the same first & last names.  You *may* have some other information about the customers that could safely serve as a primary key, but it's probably safest and simplest to use an autonumber field for the primary key, and just use the last name & first name in lookups.

    That said, you should certainly be *able* to set a compound primary key comprising those two fields.  If, in table design view, you select both fields and then click the "Primary Key" icon on the ribbon, that should do it.  However, if the fields are not adjacent -- so you can't use Shift+Click to select them -- then I think you can't use Ctrl+Click to select non-adjacent fields.  In that case, you need to build the primary key in the Indexes dialog.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by GorancoB Sunday, April 2, 2017 6:49 PM
    Friday, March 31, 2017 7:42 PM
  • While it is possible to have a multi-column key, and it's frequently done in the right contexts, personal names, as Dirk points out, are unsuitable as keys as they can legitimately be duplicated.  I was once present at a clinic where two patients arrived within minutes of each other.  Not only did both have the same first and last names, but also the same date of birth.  Indubitably the best solution is to use a numeric CustomerID 'surrogate' key, usually an autonumber for convenience.  Any referencing tables will then include a long integer number foreign key.

    As well as being unsuitable as keys, personal names alone are also unsuitable for identifying a person, in a combo box's list for instance.  In such cases other attributes can be included in the list to distinguish between people of the same name.  You'll find an example in NotInList.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In the opening form of this little demo file, which illustrates the use of the NotInList event in various contexts, the combo box to select a contact includes their address details in addition to their names, allowing two or more contacts of the same name to be differentiated.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, April 1, 2017 4:34 PM Typo corrected.
    Saturday, April 1, 2017 4:32 PM
  • As others have already started, this would be the wrong approach.  Take a look at a very basic sample database from

    https://www.devhut.net/2016/09/01/ms-access-contact-database-template-sample/ 

    It should help you understand what everyone is talking about when we tell you to setup a numeric primary key.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, April 1, 2017 8:39 PM
  • The question is not if it is legible choice to this kind of index/key. Off course recommendation stays valid but giving the actual answer on how to achieve this is what is important as answer here. 

    Open the table in designer view. Select the row on the First Name Column (click the far left cell in the table designer). The whole row will get highlighted. Hold down CTRL and select the second column row (to highlight the entire row). Once both highlighted click on the Primay Key button in your toolbar. This will effectively create a primary key using both columns.   

    That is how, but as others recommended this is not good practice and you would be better off to create what is called "surrogate" key - which is most often represented as Primary Key on Auto Number column in Access.  You will need to relate this data from this column in other tables - meaning you will have to have this surrogate data in the referenced tables and then join your tables using this key/data. Anything else will result in much slower performance even if you had only few or few hundred records.

    Saturday, April 1, 2017 10:57 PM
  • @GorancoB

    I would just point out that Dirk did answer the question:

    "That said, you should certainly be *able* to set a compound primary key comprising those two fields.  If, in table design view, you select both fields and then click the "Primary Key" icon on the ribbon, that should do it.  However, if the fields are not adjacent -- so you can't use Shift+Click to select them -- then I think you can't use Ctrl+Click to select non-adjacent fields.  In that case, you need to build the primary key in the Indexes dialog."

    At which point we then try and explain that this is not the best approach and offer a better solution.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, April 2, 2017 12:37 AM
  • I guess I missed that read from before I replied. I've proposed that one as answe as mine is obviously duplicated. 
    Sunday, April 2, 2017 6:50 PM
  • Hi LeeFranklin,

    I can see that , some of the members already provided the best suitable answer for your question.

    after you posted the issue , you did not gave any follow up on this thread.

    I suggest you to check the suggestions given by the community members and if you think that suggestions can solve your issue then mark that suggestion as an answer.

    if you have any further questions then let us know about that.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 3, 2017 6:53 AM
    Moderator
  • As well as being unsuitable as keys, personal names alone are also unsuitable for identifying a person, in a combo box's list for instance.  In such cases other attributes can be included in the list to distinguish between people of the same name. 

    I have upon occasion included a field called "NameDistinquisher", into which the user could put anything they might need to distinguish one individual from their list from another.  Then in lists that would be included in parentheses after the assembled full name, allowing the user to tell which individual they were selecting, according to their own personal logic.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, April 4, 2017 4:34 PM