Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Margaret123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    10

    Question Can I Have Composite Keys in Multiple Tables Within a Database

    Hi. I am teaching myself Access 2016 and am working on a database that I created for practice. I would like to know if I can create composite keys in 2 of the 3 tables that make up my database. I tried to do this, but I had an impossible time creating relationships between the tables. The Relationship Type box in the Edit Relationships dialog box states "Indeterminate".

    Thanks.


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It should be possible. I've got an old app that used composite keys in related tables, and they work fine.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could tell us more about your proposed database. Or post a copy with some info about the database.

    Why do you think you need composite keys? A brief description in plain English would help readers.

    May be surrogate/autonumber PK and unique composite indexes for unique records???

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe use a compound index instead? Don't know for certain, but I would think that might eliminate the issue and make those who don't like compound keys happier (that's not me - I don't usually see an issue with compound keys).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Margaret123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    10
    "Perhaps you could tell us more about your proposed database."

    I have 3 tables.

    1)One is named Guests and has the first name, last name, address, city, state, and zip, and date of birth fields
    2)Another table is named Contacts and has the telephone number, Twitter handle, and email address fields of the guests
    3)The last table is named Activities and has yes/no fields for questions like Attending Party, Attending Ceremony, and Tickets Needed Amount

    The Guests and Contacts tables have a Guest ID and the Activities table has an Activity ID.

    In the Guests table, I would like to enter two different addresses for two of the guests. The other guests will have only one address.
    In the Contacts table, I would like to enter two phone numbers for the two guests who have two different addresses. The other guests will have only one address.

    I was reading about multiple primary keys in a book and felt that I could use this in my practice database.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have used a composite key only once. I prefer to avoid. Can use an autonumber as the key for linking tables but set a composite index to prevent duplicate combinations.
    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.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    At most, you might consider a compound key or index on tblGuest name + address, plus a compound on guest ID (as a foreign key) in tblContacts + Phone number.
    It would disallow 2 names with the same address or 2 names (the ID from names table) with the same phone number. If this is for practice, something with more than 1 possible duplication might be a better teaching exercise.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Margaret123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    10
    Micron, Thank you. I will apply what you wrote and see what happens.

  9. #9
    Margaret123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    10
    June7, Thank you. I'll look into what you suggested. I already have the records typed (11 records), but I will see how I can work your info into my database.

  10. #10
    Margaret123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    10
    orange, Thank you for the link.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I was reading about multiple primary keys in a book and felt that I could use this in my practice database.
    A table can have only 1 primary key. But that key may be composed of multiple fields.
    Most will advise you to use an autonumber primary key.
    Good luck with your project.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You should also see Microsoft Access Tables: Primary Key Tips and Techniques
    Read it many times..... I still re-read it occasionally.

  13. #13
    Margaret123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    10

    Smile

    Quote Originally Posted by ssanfu View Post
    You should also see Microsoft Access Tables: Primary Key Tips and Techniques
    Read it many times..... I still re-read it occasionally.
    ssanfu, Thank you for the link. I will read this article. I will also review relationships, as I am still a little shaky on the concept.

  14. #14
    Margaret123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    10
    Quote Originally Posted by orange View Post
    A table can have only 1 primary key. But that key may be composed of multiple fields.
    Most will advise you to use an autonumber primary key.
    Good luck with your project.
    orange, Correct. I meant to phrase it as a multiple-field primary key field, which is a mouthful and can still be confusing to me because it seems that it is actually multiple primary key fields in a table when in actuality it is only one.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    See this extended article on relationships and referential integrity http://www.mendipdatasystems.co.uk/r...ps1/4594533224
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 20
    Last Post: 01-09-2017, 12:08 AM
  2. Replies: 1
    Last Post: 05-24-2012, 09:35 AM
  3. Design dilemma; multiple tables with same foreign keys?
    By squirrly in forum Database Design
    Replies: 9
    Last Post: 08-16-2011, 10:43 AM
  4. Using composite keys but stuck badly in the design
    By hmushtaq in forum Database Design
    Replies: 2
    Last Post: 01-25-2011, 12:25 AM
  5. Using composite keys to enter data in a form
    By kymmyg in forum Database Design
    Replies: 2
    Last Post: 09-08-2010, 01:20 PM

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