Please, need some help with a Reference Drop Down Condition

Ask for Help
1

I have a table of Suppliers

Each supplier has a few materials it sells.

In the suppliers table, its the column “material types”, which is a REFERENCE LIST type. (actually it’s a two way reference… there is a table called ProductsBySupplier, one row for each material that supplier sells… being a reference to materials table. Since it’s a two ways reference, it creates a reference list on suppliers table)

Ok… I also have a table called Purchases, and each purchase may have several items: Purchase_Items.

I select the supplier at the Purchase table. And at Purchase Items, I need to select a raw material type.

And I want ONLY the materials THAT supplier sells to appear, instead of the whole list of materials.

2

The table ProductsBySupplier has a single reference to Raw Material Codes table per row, and I can get another column showing the referenced supplier.

in the purchases items I could try referencing the ProductsBySupplier table to compare the purchase Supplier to the supplier of that material

Not sure however it would be logical and ideal to reference a reference instead of the materials table.