You can set up a related table with one field as the Max inches and the other field as the rate to charge.
Your original table can then use an auto-enter field option and the relationship to copy over the rate. Since this rate would be copied into a simple number field, the user can be allowed to modify the rate when necessary.
Take a look at the Looked up Value auto-enter option and note the options that you can select if there is "no exact match".
Note that with this related table approach, updating your pricing tiers is a data entry task not a database design change task.
Sorry PhilModJunk, I'm somewhat lost, I can't help but think I'm trying to make this too difficult
I set up 2 fields in a new table I created, one I named Max Inches, the other I named Rate, so are you saying the next step is to enter the max number of inches in each price break section into the comments section, would that be separated by a comma or a less than sign, then enter the appropriate /inch rate into the rate field comments making sure to stay in the same order also separating them by a comma or something else?
Or am I completely off base?
Once again thank you so much for your assistance
Create a series of records on a layout based on this new table. Enter the max inches and rate into each field. Using your example data, it might look something like this:
Then set up this relationship in Manage | Database | Relationships:
YourTable::Inches = RateTable::MaxInches
Then, in YourTable, set up field options for YourTable::Rate to use a looked up value field option:
Starting From: YourTable
Look up From Related Table: RateTable
And then, select over in the "if no exact match, use: next higher value"
That way if you enter 25.2 into YourTable::Inches, the YourTable::Rate field auto-enters 14.5. If you enter 7, you get 15.2. This is an auto-entered value so you can allow the user to edit this looked up value if necessary.
I have made some progress PhilModJunk, but I've made some kind of mistake since it's not figuring correctly, I think I may have missed a step or something.
I set up a Rate Table with the 2 fields MaxInches and PerInchFee, and created the records as you advised, but when the field TotalInches populates automatically (from my previous posted question, that you answered, which works so AWESOME, thank you again) it figures too low of a rate.
I'm trying to take TotalInches and multiply it by the correct PerInchFee from the Rate Table and auto enter the field "Amount". Right now I have the Amount field set up as a look up value per your above response. Can I change the Amount field to be a look up field and a calculation field, or what step have I missed?
Once again thank you for all of your help