MS Access: Table development

Posted on 2011-04-21
Last Modified: 2012-05-11
In the table "tblToolName" as it suggests will be a list of tool names. We have many tools with the same name such as 6" calipers we currently have about 50 but each has a different serial number.

Do I really need this table? Should I put this field in the table "tblTools" or is it better to leave it like it is??

Question by:James Coats
    LVL 10

    Accepted Solution

    No you do not need the extra table.

    LVL 10

    Expert Comment

    Try to think about table relationships, one to many, many to many, one to one. One to one is what you would be creating which most of the time are not required... although there may be times when it's usefull! (see the comments on one to one relations on this page:
    LVL 10

    Expert Comment

    Hold on, I may have been getting this wrong (I had to make assumptions based on the fact there is no sample data and your comment above). If the relationship is:


    (which i first assumed) then that would be a one-to-one relationship, however:


    then it can be one-to-many.

    So you could have:

    ToolName_ID      Tool_Name
    1                         6" Caliper

    Tool_ID   Tool_Name_FID  Serial_#
    1             1                         ABC001
    2             1                         ABC002
    3             1                         ABC003

    etc. That would make the table worth while.

    Sorry about the confusion

    Author Comment

    by:James Coats
    Thank you for that update I will change it back as I am going to invest a lot of time in this effort.
    LVL 10

    Expert Comment

    In that case... my 2 cents, I personally would have had a table with a more generic name, say called tblToolType (instead of tblToolName), then you can move other related info such as the 'Type_of_device' field into the one table:

    ToolType_ID   Tool_Type   Tool_Name
    1                     Measuring  6" Caliper

    Tool_ID  Tool_Type    Serial_#
    1            1                  ABC001
    2            1                  ABC002

    Hope that helps

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now