Referencing specific data in other tables

[NewBoard]

I am currently building a database to handle the inspection sheets used by our Quality lab. Here is the relationship layout:

For each revision on the Revisions table, I have a differing number of Dimensions that need to be measured (some parts/revs will have 5 dimensions, while others will have 15). Each dimension has a specified type of instrument used in measurement, and a max/min value. The instrument used is a drop down menu that references a list of instruments/gages that we use.

Here is how I have my inspection sheet currently laid out:

The portal on the left points towards my dimensions table. It shows the data corresponded to the specified Part Number and Revision level. The data contained in this portal is uneditable from this form. The portal on the right points towards my RecordedDimensions table. It will store data that will correspond back to the Lot# associated with that inspection sheet.

I also have a table that pairs all the different types of instruments we use, with their corresponding gage ID's:

Now I would like my user to be able to click on the dropdown box under Instrument#, and that drop down box will only show the Gage ID's for the specified gage. So, if on the dimensions table, a dimension calls out for Digital Micrometers to be used, then when my user goes to select the instrument ID (which is saved in the RecordedDimensions table) it will would allow them to choose 151, 152, 153, 306, 324, 330, and 331.
Please let me know if I have not provided enough information/pictures, I will gladly provide more.
Thank you in advance for any assistance you can offer me!

Edited May 23, 2017 by NewBoard

[comment]

I got lost in your description. It seems you're looking for a conditional value list.

[NewBoard]

3 minutes ago, comment said:

I got lost in your description. It seems you're looking for a conditional value list.

A conditional value list is exactly what I'm trying to make. It's a little easier said than done though, since I'm storing values in one table, but referencing them in another. Currently when entering values, my stored values table doesn't know which report from the Dimensions table it's referencing. If I could figure out how to the dimensions and stores values 1-to-1, I could do a conditional value list. I've managed to get the drop down to only show Gage ID's related to the gages used in the inspection. However, it shows ID's from all of the gages used on that inspection. Whereas I want it to only show the ID's for the gage used on that specific dimension.

Let me know what I can clarify for you.

[comment]

11 minutes ago, NewBoard said:

Let me know what I can clarify for you.

Let's start with this: in which table is the field you need to populate using this value list.  Also, am I correct in assuming that your last picture shows records from the Gages table, with the left column being Gages::Gage Type, and the right column being Gages::Gage ID?

[NewBoard]

9 minutes ago, comment said:

Let's start with this: in which table is the field you need to populate using this value list.  Also, am I correct in assuming that your last picture shows records from the Gages table, with the left column being Gages::Gage Type, and the right column being Gages::Gage ID?

Your assumption is correct.
So, I currently have one form for building the parts. This is where the manager will enter in what dimensions are being inspected, what tools, and the tolerances through a portal linked to the Dimensions table

Then, as the inspections are actually performed, they will pull up the following screen:

The portal on the left contains all of the information that the manager entered in earlier. I want the user to be able to enter information into the portal on the right, which points to the table RecordedDimensions. Which I have working correctly besides the instrument dropdown.

Edited May 23, 2017 by NewBoard

[comment]

1 hour ago, comment said:

in which table is the field you need to populate using this value list.

It's a simple question. The answer should be one word: the name of the table in which the field is defined.

[NewBoard]

5 minutes ago, comment said:

It's a simple question. The answer should be one word: the name of the table in which the field is defined.

 

I answered your question. RecordedDimensions

[comment]

You need to link RecordedDimensions directly to either Dimensions or Gages. If the Instrument field in RecordedDimensions is populated to be the same as the Instrument field in the corresponding  Dimension, you could define the relationship as:

RecordedDimensions::Instrument = Gages 2::Gage Type

where Gages 2 is a new occurrence of the Gages table. Then your value list would be defined to use values from Gages 2::Gage ID, include only related values starting from RecordedDimensions. 

--
Note: this is a technical answer. I don't really understand what you're doing here, or why. And I have a feeling there is a better way to do it.