Results 1 to 7 of 7
  1. #1
    Clernix is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    7

    Primary Foreign Key question

    I am new to Access. I am currently working through the new perspectives book and on chapter 8. So I know just enough to be dangerous. I am trying to set up my first data base from scratch. Currently I have 3 tables. Two of the tables are from importing excel spread sheets.

    First table Employee (primary key User ID)



    Second table Call Date (foreign key User ID)

    Third table payment data no primary key or foreign key currently

    My problem is that the second two tables are excel imports the the multiple entries for all identifiers.

    1. Can I tie two foreign keys to one primary key?
    2. Can I tie two tables by foreign key with no primary key? P-F-F

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How is UserID in Employee table assigned - autonumber? Autonumber PK may not be useful for your situation. What would be data in third table? What info would link these records to Employee or CallDate?

    No, 1 PK field cannot be associated with 2 FK fields. However, compound keys are possible but I try to avoid. This means assigning two fields as unique identifier PK and saving both values as foreign key into dependent table.

    Advise not to use spaces in naming convention.
    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
    Clernix is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    7
    The user id is short text and primary key. The user id in call data is the foreign key because it has multiple entries. What I am trying to do is link all three table so I can query call data and and the third table payment data. Call data and payment data are tables created by uploading excel data.

    Payment data is a unique identifier for each candidate but has multiple entries .

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Still don't know what data in Payment would link to Employee and CallDate.

    If userID identifier is the link in all three tables then there are defined relationships.

    This involves 2 one-to-many relationships. Including all 3 tables in same query is not practical.
    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
    Clernix is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2017
    Posts
    7
    I think I have to solution. I am going to create a 4th table.

    Question can a table only have a foreign key and not a primary key? If I link the table with only a foreign key to a table with the primary key does that work?tableexample.zip

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A PK is not always required. This can be the case with a junction table used to associate records in a many-to-many relationship. However, might want to set a compound index that includes foreign key fields of a junction table so a duplicated combination won't be allowed.

    Your AddedTable does not really make sense. How do you propose to get PaymentID as FK into Payment table? Or did you get the designations backwards?

    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
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    In general, his is how I would design the tables. But I still don't know how tblPayments relates to the other two tables.
    tbUsers (Employee) tblCallData
    tblPayments
    UserID_PK (Autonumber) CallDataID_PK (Autonumber) PaymentsID_PK (Autonumber)
    UserID_FK (Link to tblUsers) UserID_FK (Link to tblUsers)



    But you still haven't answered the question "How is tblPayments related to the other tables?".
    Is the relationship between tblEmployees and tblPayments? (as above)


    Or is the relationship between tblCallData and tblpayments.? (as below)

    tbUsers (Employee) tblCallData
    UserID_PK (Autonumber) CallDataID_PK (Autonumber)
    UserID_FK (Link to tblUsers)

    tblCallData tblPayments
    CallDataID_PK (Autonumber) PaymentsID_PK (Autonumber)
    UserID_FK (Link to tblUsers) CallDataID_FK (Link to tblCallData)



    Personally, I have a PK field in every table and the PK field is an autonumber type. And I use the _PK/_FK suffixs because it is easier for ME to know which field is which when creating queries, setting up the Row Sources for combo boxes and setting the Master/Child links between a Main form/sub form. Again, this is how I have created/developed my "style" for creating databases.

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

Similar Threads

  1. Composite Primary Key VS Foreign Key
    By johnseito in forum Database Design
    Replies: 1
    Last Post: 05-07-2019, 10:38 AM
  2. Primary Key/Foreign Key question
    By JohnnyChimpo in forum Access
    Replies: 5
    Last Post: 10-03-2015, 11:20 AM
  3. Primary and Foreign Key Question.
    By Schae235 in forum Access
    Replies: 3
    Last Post: 04-08-2015, 07:03 AM
  4. Primary key/ foreign key question
    By DaveG in forum Access
    Replies: 5
    Last Post: 05-02-2014, 12:42 PM
  5. Primary Key and Foreign Key Question
    By chrisbas in forum Access
    Replies: 1
    Last Post: 05-07-2012, 04:01 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