MS Access: Table Relationships

Posted on 2011-04-19
Last Modified: 2012-05-11
I would like to adjust my table relationships so that the following happens:

Tool List is the Master Table

All other tables are slaves.

I have inherited this from someone else so I am working through this.

The ID’s in the other tables are from the “Tool List” ID but were just named “ID” in that table. Should I change that? To “Instrument ID”??

Also the “Tool List” table has a “Type of Device” field which identifies what each instrument it via a description.

I want to put that field as it related to the slave tables such as Temperature, Deflection, Dimension so that respective table will only see or accept data from the Master table that is related to itself.

Can someone give me an example of how to do this. I then will make Forms that relate to each slave table that will accept data related to itself.

Question by:James Coats
    LVL 9

    Accepted Solution

    You'll want to add what is called "Foreign Keys" to the tool list table which refer to the tables which are called "reference" tables.  You just add numeric fields to the Tool List table like "Dimension Verification ID", then you draw a relationship line from the Autonumber field in the DImension Verification table to the Dimension Verification ID field in the Tool List table.  Just repeat that for all reference tables.
    LVL 6

    Assisted Solution

    I would rename the ID's one the other tables, so you always know where they belong. I always keep the name, but change the ID to id in the foreign key, in all the other tables, that way, its easy to see if your what type of ID yours working with.

    if "Type of Device" cannot have duplicates, then you can just use the Instrument ID. If that field have duplicates, I would suggest that you create a field called "Device type #" and use that. It's generally a bad idea to use text as keys.


    Author Closing Comment

    by:James Coats
    Thanks guys that really helped.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now