Help with table structure Please

[Dave Elliott]

I have a very difficult problem to figure out, at least for me and so need
help with table I think so i can get my values correct.
Maybe there is another way to do this but i am at a lost as to how anyway,
here goes.
I need to calculate the value of (2) different rates (1) @ $65.00 a Hour and
(2) @ $55.00 a Hour
Lets say the company charges $65.00 an hour for one employee and even for
two employees, this is fixed.
But, if another employee iss added, then the rate becomes $65.00 for the
first two employees and 1/2 that ($32.50) for the next employee
making the Rate charged $97.50 for three employees.
for each additional employee the rate of $32.50 is added to the total rate
per hour
The Total number of employees would be set at 20

Also the same goes true for the other rate the company charges which is
$55.00 a Hour
for the first (2) then starting with the third it becomes $82.50 then
increments an additional $27.50 for each employee added.

How can I approach something like this so that when i choose a number of
employees with the matching rate i get the calculation that is correct?
I made table with CalcID as the primary key, Rate (Currency), Emp (Number)
, Formula (Currency)
Rate shows the Rate i would choose, Emp is for the number of employees,
Formula is too calculate the Rate & the Number of employees.

 

[tina]

the table you describe is really a list of calculated rates, which includes
the variables (rate and number of employees) and the calculated result. you
don't need a table, all you need is to have the formula readily available so
you can call it anywhere in the database that you need to show or use that
calculated value. i would write a public function to return the total, as

Public Function isRate(ByVal lngPersons As Long, _
ByVal curBase As Currency) As Currency

If lngPersons < 3 Then
isRate = curBase
Else
lngPersons = lngPersons - 2
isRate = curBase + ((curBase * .5) * lngPersons)
End If

End Function

write the function in a public modules, and then you can call it from
anywhere in another module (public or private), or from a query, form or
report, as

isRate(MyEmployeeCount, MyBaseRate)

substituting the correct field names, of course.

hth