Results 1 to 7 of 7

Thread: Need to create foreign key in MS Access

  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Need to create foreign key in MS Access

    Plesae help! I am building a relational database with 2 main tables that should relate in a one to many relationship. I have gone to the design tab created relationship, done the join but cannot enforce referential integrity.

    The database tracks managers and their accounts. ONe table tracks managers and the subtable (the many) tracks their accounts.

    Of course there are managers without accounts as yet, but Access will not let me enforce ref integrity saying that managers may have records not found in accounts. Of course that's true.

    Can you please help me set this straight.

    And ultimately, I want to ensure that if I have to delete a manager, I can delete all associated accounts at the same time.

    I think Foreign Key may be the answer, but I have no idea how to do so.

    Thank you

  2. #2
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    An example

    Create a table called tbl_managers with the following fields
    manager_ref, manager_name etc. Make manager_ref the primary key.

    Create another table called tbl_accounts with the following fields account_ref, manager_ref etc make account_ref the primary key. Make sure the manager_ref in both tables have the same data types e.g. both are text or both are bnumber etc.

    Using the relationship screen add the two tables to the screen now create a join from the field manager_ref in tbl_managers to the field manager_ref in tbl_accounts. When you finish the join a popup will be displayed then you can finish the join criteria there should be 1 next to the tbl_manager and the infinity next to the tbl_accounts this means that one manager can have many accounts.
    Hope this useful, for a better example look at Northwind.mdb sample database

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    I am creating a database, I'd like one to many...but I can only get one to one. What am I doing wrong?

    Residents Table
    Room (Primary Key)
    Fname
    Lname
    Phone
    Email

    Family Contact Table
    Room (Primary Key)
    Fname
    Lname
    Relationship
    Address
    Phone
    Email

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You need to remove primary key on Room in contact table and create a foreign key on room which references room column in residents table.

  5. #5
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Aliciaishot

    Assuming a resident has many contacts

    Residents Table
    Resident_Ref (Primary Key)
    Room
    Fname
    Lname
    Phone
    Email

    Family Contact Table
    Contact Ref (Primary Key)
    Resident_Ref (Foreign Key)
    Fname
    Lname
    Relationship
    Address
    Phone
    Email


    Do you have a Room table that shows the location and the facilites in the room etc.?
    If you do have a room table then you will need to change Room to Room_ref so that it can be linkied to the room table
    Allan

  6. #6
    Join Date
    Jul 2011
    Posts
    2
    Well first how do I create a foreign key?

    But what info would I want to put under resident_ref and then the contact_ref - a auto number?

    I don't have a room table.

  7. #7
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Hello Alicia

    I have attached a sample database

    The table structure is the same as my previous email.

    A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables.

    In the Contacts table I added a field called residents_ref. The field residents_ref is the Primary Key (PK) in the residents table and foreign key (FK) in the contacts table When you are adding the contaccts for the residents in the Contacts table you must also included the relevant resident_ref in the contacts table.

    There is also a relationship diagram and a report that displays some data sorted by residents.
    Attached Files Attached Files
    Allan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •