How should I organize categories and their territory rates into Access tables?
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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ok.
Start here
http://phlonx.com/resources/nf3/
Go through it all.
Now, stop thinking about the users and the UI for a bit and think about the data.
What stuff is fully independent?
There are territories.
There are locations. Each location belongs to one and only one territory
There are Specialty Classes.
There are rates. Each rate depends upon a specialty class and upon a territory.
Four tables.
Likely, once you get them built with their relationships that you'll want to have three cascading dropdowns.
There's a Territories one. It cascades to limit the choices for a Location one.
The Territory one cascades to limit the choices for the Specialty Classes, too.
Choose the Specialty Class, and the related rate can then be displayed.
But you may have gone awry in not thinking about picking the territory.
The territory is what limits the choices of locations, and specialty classes.
Those two things then determine the rate.
Start here
http://phlonx.com/resources/nf3/
Go through it all.
Now, stop thinking about the users and the UI for a bit and think about the data.
What stuff is fully independent?
There are territories.
There are locations. Each location belongs to one and only one territory
There are Specialty Classes.
There are rates. Each rate depends upon a specialty class and upon a territory.
Four tables.
Likely, once you get them built with their relationships that you'll want to have three cascading dropdowns.
There's a Territories one. It cascades to limit the choices for a Location one.
The Territory one cascades to limit the choices for the Specialty Classes, too.
Choose the Specialty Class, and the related rate can then be displayed.
But you may have gone awry in not thinking about picking the territory.
The territory is what limits the choices of locations, and specialty classes.
Those two things then determine the rate.
Does the combination of a specific Specialty + Location determine the Rate? And you want the user to select a Specialty, and then a Location, and you would then want to lookup the Rate?
Is a specific Rate dependent on the Specialty + Location? In other words, if I have a Specialty of XX, is the rate different for LocationA versus LocationB? Or is it different for TerritoryA versus TerritoryB?
Might help if you could provide some sample data (obfuscated, of course).
Is a specific Rate dependent on the Specialty + Location? In other words, if I have a Specialty of XX, is the rate different for LocationA versus LocationB? Or is it different for TerritoryA versus TerritoryB?
Might help if you could provide some sample data (obfuscated, of course).
Scott, each Territory + Specialty has a Rate. There are only four territories and each of them contains many parts of the country. A user wouldn't automatically know the territory, but they would know the specialty category and the location which they could use to look up the territory. Then, the territory + specialty combination would pinpoint the rate.
Thank you Nick. I'm going to look through that and try it.
The sample data I have is in an Excel workbook. The workbook calculations need to become a part of the database I'm building. Should I upload the workbook?
Thank you Nick. I'm going to look through that and try it.
The sample data I have is in an Excel workbook. The workbook calculations need to become a part of the database I'm building. Should I upload the workbook?
OK Nick, I was thinking four tables at one point also. But 2 of the four tables will only have a column, two at most.
For example, the Territory table only needs a PK with territory numbers 1-4. Nothing else.
The Specialty Classes table doesn't need anything other than a PK (probably autonumber) and the SpecialtyClass description.
So, when I go with four tables, I feel like I'm adding unneeded complexity. However, when I go with two tables, tblLocations and tblRates, I end up with a many-to-many relationship based on Territory.
Trying with 3 tables, tblLocations, tblRates, tblTerritories confuses me on looking up the rate.
BTW...a user would not automatically know the territory. They would know the location and that would need a lookup for territory. Also, each specialty has a rate for each territory. So specialty 1 territory 1 = UniqueRate, Specialty 1 Territory 2 = UniqueRate2
Sometimes it seems so hard to explain things verbally (or in written form in this case) :-)
For example, the Territory table only needs a PK with territory numbers 1-4. Nothing else.
The Specialty Classes table doesn't need anything other than a PK (probably autonumber) and the SpecialtyClass description.
So, when I go with four tables, I feel like I'm adding unneeded complexity. However, when I go with two tables, tblLocations and tblRates, I end up with a many-to-many relationship based on Territory.
Trying with 3 tables, tblLocations, tblRates, tblTerritories confuses me on looking up the rate.
BTW...a user would not automatically know the territory. They would know the location and that would need a lookup for territory. Also, each specialty has a rate for each territory. So specialty 1 territory 1 = UniqueRate, Specialty 1 Territory 2 = UniqueRate2
Sometimes it seems so hard to explain things verbally (or in written form in this case) :-)
So a Location would belong to one and only one Territory?
If so, then something like this to start off:
tSpecialty
---------------
ID
Specialty
tLocation
----------------
ID
Territory_ID
Location
tTerritory
-----------------
ID
Territory
tTerritory_Rates
-------------------------
Territory_ID
Specialty_ID
Rate
Rate_Name
The user would select a Specialty and a Location, and you could then use something like this to get the Rate:
SELECT * FROM tTerritory_Rates AS tr INNER JOIN tLocation loc ON tr.Territory_ID=loc.Territ
WHERE tr.Specialty_ID=" & SomeSpecialtyIDValue & " AND loc.ID=" & SomeLocationIDValue
If so, then something like this to start off:
tSpecialty
---------------
ID
Specialty
tLocation
----------------
ID
Territory_ID
Location
tTerritory
-----------------
ID
Territory
tTerritory_Rates
-------------------------
Territory_ID
Specialty_ID
Rate
Rate_Name
The user would select a Specialty and a Location, and you could then use something like this to get the Rate:
SELECT * FROM tTerritory_Rates AS tr INNER JOIN tLocation loc ON tr.Territory_ID=loc.Territ
WHERE tr.Specialty_ID=" & SomeSpecialtyIDValue & " AND loc.ID=" & SomeLocationIDValue
Alright, that was basically my four table setup other then the rate name, which doesn't exist. So you think this is the best way to set it up? When I looked at it, it seemed I was creating extra table with little to no data in them. But then, when I tried to combine some data into fewer tables, I ran into more complexity when looking up the rate.
You don't have to create lookup tables (the Territory and Specialty tables, for example), but in general it's a good idea to do so. Without those types of tables you would find it difficult to add new Territories or Specialties - and you'd also find it difficult to create reports when you get to that phase of the project.
And you shouldn't really be concerned with the number of tables, but rather whether those tables are correctly structured. Given the information provided, it would seem the structure I suggested would be a good starting point. That doesn't mean it's the best (or even the right) structure - but it's just a common structure for the business processes you've explained.
And you shouldn't really be concerned with the number of tables, but rather whether those tables are correctly structured. Given the information provided, it would seem the structure I suggested would be a good starting point. That doesn't mean it's the best (or even the right) structure - but it's just a common structure for the business processes you've explained.
Excellent. I feel better moving forward with it like this. I so much appreciate both of you for helping me. Thank you so much.
OK Nick, I was thinking four tables at one point also. But 2 of the four tables will only have a column, two at most.
When you start to think about it, you may START with only one or two columns -- but good data has a tendency to grow.
Take Territory.
It will have an ID
Probably a friendly name
It was created by somebody
It was created on a certain date
It can have a notes field so you can write some stuff about how and why that territory came to exist
It can have an date last edited field ( maybe one state moves from one territory to another based on workload or distributions centers or something)
Locations may be similar
Take rates (I take it prices)
Those get really tricky.
Who created them, where they applied, start times and end times that they were in effect are just some of the things you may want to record.
Lots of data can add up quickly when you start to put it into production and track changes!
But if you get a good, solid normalized data setup going FIRST extending it later is much easier.
Take a shortcut early and you comes to painfully regret it later.
When you start to think about it, you may START with only one or two columns -- but good data has a tendency to grow.
Take Territory.
It will have an ID
Probably a friendly name
It was created by somebody
It was created on a certain date
It can have a notes field so you can write some stuff about how and why that territory came to exist
It can have an date last edited field ( maybe one state moves from one territory to another based on workload or distributions centers or something)
Locations may be similar
Take rates (I take it prices)
Those get really tricky.
Who created them, where they applied, start times and end times that they were in effect are just some of the things you may want to record.
Lots of data can add up quickly when you start to put it into production and track changes!
But if you get a good, solid normalized data setup going FIRST extending it later is much easier.
Take a shortcut early and you comes to painfully regret it later.
BTW...a user would not automatically know the territory. They would know the location and that would need a lookup for territory.
Good enough. You can create a flat listing of locations -- but don't sell the users short, or forget about good UI design.
1. If you give the territory a friendly name, and choosing it shortens up the list of locations, the user won't take long to know what territory they are in.
2. Humans--and comboboxes -- are visually good with about 30 things in a dropdown listing. If you have a lot more than 30 locations, having the user select a territory first so that the location dropdown has a lot fewer items in it makes their lives much easier. Hunting for something at the bottom of a long combobox dropdown when you are not a typer stinks and is not efficient design.
Good enough. You can create a flat listing of locations -- but don't sell the users short, or forget about good UI design.
1. If you give the territory a friendly name, and choosing it shortens up the list of locations, the user won't take long to know what territory they are in.
2. Humans--and comboboxes -- are visually good with about 30 things in a dropdown listing. If you have a lot more than 30 locations, having the user select a territory first so that the location dropdown has a lot fewer items in it makes their lives much easier. Hunting for something at the bottom of a long combobox dropdown when you are not a typer stinks and is not efficient design.
Thank you Nick. Thank you Scott.You're welcome.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial