troubleshooting Question

How should I organize categories and their territory rates into Access tables?

Avatar of fabi2004
fabi2004Flag for United States of America asked on
Microsoft Access
13 Comments2 Solutions100 ViewsLast Modified:
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.  

For example,
   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?

Thanks!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros