I'm working on a customer order table and I'm struggling to work out how to relate a field to a number of tables. My problem:
I have a customer order table which has fields: color_name(*) and part_code(*)
This is related to a paint_colors table which is a list of color_name(*) and their abbreviations.
The color names are then related to numerous tables like: paint_white, paint_black etc. Which lists the color name, color_code and part_code(*).
In the customer order table I want to select a color_name and then that should limit me to the part_codes in the corresponding table (Ie. If I select Color_name: White, I should only see the part_codes from the table paint_white in the drop down menu for the part_codes field).