Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

MS Access: Table Relationships

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.

James Coats
James Coats
2 Solutions
Ken FayalCTOCommented:
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.
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.

James CoatsComputer Info. Sys. StudentAuthor Commented:
Thanks guys that really helped.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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