I don't know why, but I've been staring at this problem for a while now and I can't see it clearly. Could someone please help me figure out how to set up the Access tables for this type of data?
I need users to be able to pick a Specialty Class, then pick a Location to find the Rate. Each location is associated with a particular Territory. Each Territory has a given Rate for each Specialty Class.
I have Locations: AL in T1, TX in T1, OK in T1, CA in T2, LA in T2, NY in T2, OR in T3, WY in T3, NC in T3, etc.
I have Specialty1, Specialty2, Specialty3, etc.
I also have Specialty1 T1 Rate, Specialty1 T2 Rate, Specialty1 T3 Rate, Specialty2 T1 Rate, etc.
I've tried combinations of tables ranging from 2 to 4. But I can't seem to come with something logical that will allow the user to pick the Specialty Class, pick the Location, and then view the Rate (or drop the rate into a calculation).
Any ideas of how this is normally done?