We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MS Access: Table Relationships

James Coats
James Coats asked
on
Medium Priority
291 Views
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






Measuring-Instruments-3-4-21-11.mdb
Comment
Watch Question

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.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

James CoatsComputer Info. Sys. Student

Author

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.
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
James CoatsComputer Info. Sys. Student

Author

Commented:
Thanks for the answer and the link.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.