This is the scenario. I have a table “Main” with 2 fields: Location and Salesperson (actually it has several fields but I want to concentrate on these two fields. Also, I have a table called “Location” with three Records or stores (FW, WC, and W). These two tables have a 'one to many' relationship. On the other hand I have three other tables (FW-Salespersons, WC-Salespersons, and W-Salespersons). Each table has a list of salespersons of their corresponding store.
The idea is the following: if the Location field from “Main” table is chosen one of the stores, for instance, FW. I want the Salesperson field Drop Down Box from “Main” table to be change automatically to FW-Salespersons list, and the same thing for WC, and W stores. How would it be the best way to connect these tables in order to accomplish this task? If you have a suggestion how to do it, it would be greatly appreciated.
Thank you in advance.
Plaguna