Results 1 to 9 of 9
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    Datatype primary key


    All, using access 2016, I am designing a new database based on existing data from a spreadsheet. From the data, I have determined 4 basic tables. They each have a unique field I can use to identify the primary key. My problem is in two of the tables, the unique key begins with a letter and the other two is all numeric. All the tables have to relate to one another and the user wants these fields as the primary keys. I told the user This is not possible for two different datatypes. What can I do to get all these tables in the relationship?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You already know what you have to do. The fields must be same type. Why does one of the keys begin with a letter? Is rest of key numerals and same as the other keys? Show sample data.
    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.

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Quote Originally Posted by June7 View Post
    You already know what you have to do. The fields must be same type. Why does one of the keys begin with a letter? Is rest of key numerals and same as the other keys? Show sample data.
    Thanks for replying. The user is adimate about using their unique keys and the only way this could happen is if the datatype is text. As a rule; I dislike using a text field on all numeric fields. Further; I am torn to using autonumber as a primary key. I always have a hard time when I query and create forms and reports. The initial load of data will be done done via spreadsheet with multiple tabs. I don't know why two of the unique keys begin with a letter.

    Code:
      Table 1 Unique key U-1070
      Table 2 Unique key 10889
      Table 3 Unique key P-7955  
      Table 4 Unique key 15675
    I'm open to suggestions.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Actually, why would tables link on unique primary key? That would be a 1-to-1 relationship and might as well be one table.

    Need to explain what these data entities are and how they are supposed to relate.

    Perhaps what is missing is a junction table.
    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.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    I ALWAYS use an autonumber type for the PK field. It is so much simpler when creating queries (complex or not).

    You could always set an unique index on the text fields.


    Maybe this will help: Microsoft Access Tables: Primary Key Tips and Techniques

  6. #6
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Sorry all...just realized I didn't finish the sample table...disregard the words unique key. I was trying to show what the user wanted for the ID field.n the sample data...example:
    TableName1
    PK tbl1ID (U-1070)
    1:Many
    Tablename2
    PK tbl2ID (10889)
    FK tbl1ID (U-1070)

    Yes ssanfu. I may use autonumber for the PK in each table and set the unique index on the text fields they want for the relationship data.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, tbl1ID would be a text field in both tables, tbl2ID could be a number field, as long as none of the id's are like 01735.

    Now how do tables 3 and 4 fit in?
    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.

  8. #8
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Omg June7...I must have been in a fog all day yesterday...I don't know why I was thinking all the tables had to be one datatype for the primary key. It clicked when you said, "as long as none of the IDs ate like 01735". I think I was hung up on two of the tables have a many to many relationship and I was not familiar with having to do a junction table:
    TableName3
    PK tbl3ID (P-1790)
    FK tbl1ID (U-1070)
    1:Many
    Tablename4
    PK tbl4ID (18890)
    FK tbl2ID (10889)

    I left out the FK tbl4ID (18890 ) in Tablename2 ...they have a Many:Many relationship..not sure how to do a junction table. Any suggestions would be welcomed

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Example of many to many relationship.

    tblMovies
    MovieID_PK

    tblActors
    ActorID_PK

    tblMovieActors
    MovieID_FK
    ActorID_FK
    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. Best Practice To Store Which Datatype?
    By kd2017 in forum Database Design
    Replies: 3
    Last Post: 08-26-2017, 01:24 PM
  2. Replies: 2
    Last Post: 07-13-2016, 08:28 AM
  3. Replies: 2
    Last Post: 05-07-2015, 09:50 PM
  4. Unexpected datatype
    By George in forum Access
    Replies: 6
    Last Post: 03-23-2015, 08:00 PM
  5. Binary datatype
    By huBelial in forum Access
    Replies: 1
    Last Post: 04-04-2011, 02:02 PM

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