Results 1 to 9 of 9
  1. #1
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171

    Table relationships. Can primary key go to same foreign key in different table?


    I have 3 databases. All have one field in common, the contract number. If the contract number is the primary in one table and the foreign in the other two, can I link the primary key in the one table to both of the common foreign key in the others?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If the contract is common to all tables ,then you can join them in queries.

  3. #3
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    I interpret you are trying to establish a relationship between tables in different databases - that cannot be done.

  4. #4
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    The tables are in the same database, just contain different information. BTW, I want to do a "one to one" relationship between the tables but can't seem to figure out how to do that. When I set up the relationship it seems to default to "one to many". What am I doing wrong?

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us about your tables in simple business terms (simple, plain English).
    Relationships in database are related to "business rules/facts".
    Good luck.

  6. #6
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Okay, I'll give it a shot. At the outset, I will confess that as a rookie I made one of the tables much larger/more complicated than it needed to be but rather than start from scratch I am trying to work around, so here it goes.

    Table 1 contains basic info. Contract number, names, addresses, etc. Contract number there is the primary key. Table 2 is for attachments of documents specific to the contract in table 1. It has only three fields; auto number, contract number and attachments. Contract number there is the foreign key.

    Table 3 is where I should have done things differently. The contract number is the foreign key. I wanted to create a table to list any activity (aka "many") associated with each contract by date. In hindsight, I should have created a table with fields for contract number, date and event and then done a "one to many". Instead, being even less adept than I am now, I created a table with a separate field for each date and for each event. Date 1, date 2, date 3, etc. Event 1, event 2, event 3, etc. Lot's of fields, long queries! Unnecessary, I know...now. The bottom line is that since I set it up this way I assume I need to let the tables know one record in the primary table relates to only one record in the foreign tables.

    Thanks.

  7. #7
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    I was able to find the answer. Go to the foreign table(s). Select the foreign key and where it says "indexed" under filed properties change to Yes (No Duplicates). That will then allow a one-to-one to be created.

    Thank you all.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Patrick,

    I recommend you take a little diversion-- 45-60 minutes and work through 1 or 2 of these tutorials from RogersAccessLibrary.
    Class info system
    Catering Business
    Widgets

    Work through the tutorial - you'll learn by experiencing a process that will work with any database.

    There is more info at this link re Database Planning and Design.


    It sounds to me you have a Contract (probably between your Company and a Client/Supplier..) and you store related information about each Contract.

    Client-->Contract-->RelatedInfo

    which represents these facts:

    there are 1 or more Clients
    Each client can have 0,1 or more Contracts
    Each contract can have 0,1 or more attachments.

  9. #9
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Thanks. Will do.

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

Similar Threads

  1. Help allocating Primary/Foreign Keys and Relationships
    By rosscortb in forum Database Design
    Replies: 2
    Last Post: 07-08-2015, 08:12 AM
  2. vba to show Relationships --Primary Key and Foreign Key
    By orange in forum Code Repository
    Replies: 1
    Last Post: 06-12-2015, 10:11 AM
  3. Replies: 5
    Last Post: 09-09-2014, 01:39 AM
  4. Primary and foreign key in relationships?
    By Fatbot in forum Access
    Replies: 1
    Last Post: 04-12-2011, 10:11 AM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

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