MS Access: Table Relationships

Posted on 2011-04-21
Last Modified: 2012-06-27
I am having doubts about the table relationship in this database. Here are my observations / concerns:

The table: “tblToolNames” will have a list of instrument names such as “6” Digital Caliper” or maybe Digital Potentiometer there will be maybe 50 of each such instruments and each of these instruments will have multiple calibration checks performed on them during the year maybe 4 such checks.

Table “tblTools” will have dropdown list of the various different tool types in our inventory.

Once the checks are preformed I need to be able to view them in a form to see the effect of all four or more of the various checks that will be preformed.

Are the connections via the “tblToolNames” PK , to “tblTools” FK to the tblInspections” FK and the other 4 tables “Inspection_FK’s correct or should the connections be:

Via “tblToolNames” PK to “tblTools” FK to “tblInspections” FK and then to the other tables from “tblInspections”  PK to the other 4 tables “MoisturePK”, “DimensionPK”, “TemperaturePK” and “DeflectionPK” ???

I am not sure of either these and would like some feed back as I going to spend a lot of time in development I want to be sure on this before I start

Question by:James Coats
    LVL 1

    Expert Comment

    Can you give an example of a ToolType? I think I can help, but I'm just not sure what is the difference between a tool name and a tool type.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Unfortunately, I cannot open your database due to corporate policy issues.  However, the way I would approch this is.  I did one of these about 7 years ago.

    InspInterval - days, months, years
    InspFreq - # of InspIntervals between inspections
    As I recall, I also had another field here to indicate whether the inspection expires at the beginning or end of the period.  For example, some certifications are due at the end of the month during which it is due, others are due on the exact date, and still others are due on the 1st day of the month.

    ManID-manufacturer ID
    ManSN-manufacturer serial number
    SupplierID-where you got it from
    SupplierSKU-the suppliers ID



    Author Comment

    by:James Coats

    I like your suggestion on expired and due dates for inspections. I will use that in this database. How did you connect the different tables you made in your database??

    Do you or I in my database need to connect them at all?? Is there an advantage in connecting table via keys??


    Different tool types are: 6" Dial Caliper, 6" Digital Caliper, 24" Caliper, Dial Viscometer and instruments are usually assoicated with a Type of Device such as: Dimensional, Deflection, Temperature, Moisture, weight or viscosity... there are others but you get the idea.

    I am looking for suggestions on how to connect the different tables together I kind of like what I have but was thinking maybe the second example I gave was better. I am going to be using queries and forms of course and want the tables to work together well.
    LVL 47

    Accepted Solution

    If you haven't already done so, you should read Crystal's Access Basics

    The "connections" you are describing are the relationships.  Yes, it is important to define these relationships to prevent errant data from being written, of to prevent records from being orphaned.

    You can see that tblInventory includes a ToolTypeID(FK) to the ToolTypeID(PK) in tblToolTypes.  tblInventory also includes ManID(FK) which would relate to the ManID in tblManufacturers, which might include the manufacturer name, address, ...  It also contains a SupplierID(FK) which should relate to a table which contains information about your suppliers.

    tblInspections contains the InvID(FK) to tblInventory, which identifies the specific tool.

    When your create those relationships, you should consider adding referential integrity, which would prevent you from entering an InvID of 53 in tblInspections if that InvID value does not already exist in tblInventory.

    Author Closing Comment

    by:James Coats
    Thanks for the answer and the link.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    729 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

    24 Experts available now in Live!

    Get 1:1 Help Now