Results 1 to 8 of 8
  1. #1
    B. Davis is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    2

    Creating a Primary Key for an individual that incorporates data for the individual

    Hello and thanks in advance.

    I am creating a DB that contains a number of individuals and want a unique ID for each row (Primary Key) to follow a specific format. It should begin with the two letter abbreviation of the state from which the individual is from, the sequential number (specific to each state), and the sex of the individual (i.e. M or F).

    For example, the first male individual from Colorado would read CO001M. The second female would read CO002F. In this way I am totaling across sexes within each state.

    When entering a new record I envision a pop up window that requests the state and then the sex of the new individual and then Access would generate the sequential number.

    I realize this is a specific question without any data attached, but would appreciate any suggestions on first steps to take.... I.e. is this possible thorough a macro, or do I need to work in VB?

    Thanks.

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    This topic has been covered many times on here. Do a search on this forum and Google! As far as masks go, this is easily accomplished. I see no need for a Primary Key, just a unique identifier.

  3. #3
    B. Davis is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    2
    burrina. I had done a Google and forum search prior to the post and did not find any posts that I found useful. Either I am using the wrong terms or in the wrong place. Can you point me in a direction...? Thanks!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by B. Davis View Post
    In this way I am totaling across sexes within each state.
    If state and gender are in the data, there's no need for this field to get totals. A totals query would be simpler and more dynamic.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    I think I would concatenate the fields into a text box and then use a update query for the field in your table to update.

    HTH

  6. #6
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Never use Macros and not sure you could accomplish this with a Macro. That said here's some code that should get you started in the right direction...

    Company Name: Access Diva
    ID that will generated: ACC0001
    Side note: If there is more than one Company that starts with ACC the below code will go to ACC0002

    Code:
    Dim lngCountIDs As Long
    ‘Find how many Companies start with the same three letters and count plus one
            lngCountIDs = Nz(DCount("Right(cCompanyID,3),", "tblCompanies", "Left(cCompanyID,3) = '" & Left(Me.txtCompanyName, 3) & "'"), 0) + 1
     
            Me.txtCompanyID = StrConv(Trim(Left(Me.txtCompanyName, 3)), vbUpperCase) & Format(lngCountIDs, "0000")
            DoCmd.RunCommand acCmdSaveRecord

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    B Davis,

    A PK is to uniquely identify each and every record in your table. There is no requirement for PK values to be sequential.
    Often things work better when you follow the KISS (keep it simple) approach.
    In this case, 1 fact 1 field. You can always concatenate fields or parts thereof to do special things. You can always get counts by using a query with the required criteria.
    Keep your table structures simple, but well defined.

    Good luck with your project.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Constructing custom unique IDs is a common topic. Search forum and Google.

    Here is one for starters
    https://www.accessforums.net/access/...ers-21361.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-28-2013, 02:27 AM
  2. How do I import data into individual field?
    By ineedaccesshelp in forum Access
    Replies: 4
    Last Post: 11-28-2012, 02:41 PM
  3. Replies: 5
    Last Post: 07-06-2012, 03:22 PM
  4. Replies: 3
    Last Post: 01-25-2011, 09:50 AM
  5. Individual Records
    By JanisB in forum Access
    Replies: 1
    Last Post: 05-13-2010, 05:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums