0

Hello, I'm building a structure for a fee schedule for a construction business. let's say that the client is paying for roof. THen, the way they charge is: they charge $7.00 for the first 1000 feet and 5.00 for the rest. With this in mind, I created 4 columns: Base Fee, Base Units, Variable Fee and Variable Unit and I entered the values: 7.00 for base fee, 1000 for base amount, 5.00 for variable fee and 1 for variabe units (you know, the normal financial formula for this). However, now the user came with some moe clarification and it's more complex. For example, it could be 7.00 for the first 1000 feet, $5.00 for 1001-999 feet and $3.00 for the rest. Now I'm frozen and don't have a clue on what to do. Please help!!!
Thanks everyone

2
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by insight974
0

Are the fees per Square Foot? Here is the structure I would recommend:

Fee Table:
ID (PK)
Item (PK pointing to the work that is done (roof, floor, etc)
Fee (currency)
Lower Bound (int, the minimum area this charge applies to)
Upper Bound (int, the maximum area this charge applies to)
Running Total of Lower rates (the amount due for the work done at lower rates).

Using your example, here are the records needed:

ID, 1 (Roof), $7.00, 0, 1000, 0
ID, 1 (Roof), $5.00, 1001, 1999, $7,000
ID, 1 (Roof), $3.00, 10000, NULL, $11,990

0

I actually wouldn't use the Running Total of Lower rates I mentioned in my previous post, but it does make things easier when pulling queries together.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.