[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

MS Access: Table Relationships

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






Measuring-Instruments-3-4-21-11.mdb
0
James Coats
Asked:
James Coats
  • 2
  • 2
1 Solution
 
BlackSnowmanCommented:
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.
0
 
Dale FyeCommented:
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.

tblToolTypes:
ToolTypeID
ToolTypeName
ToolTypeDesc
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.

tblInventory:
InvID
ToolTypeID
ManID-manufacturer ID
ManSN-manufacturer serial number
SupplierID-where you got it from
SupplierSKU-the suppliers ID
InServiceDate
RetiredDate

tblInspections
InspID
InvID
InspDate
InspResults

0
 
James CoatsAuthor Commented:
Fyed,

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??

BlackSnowman,

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.
0
 
Dale FyeCommented:
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.
0
 
James CoatsAuthor Commented:
Thanks for the answer and the link.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now