Help to calculate a complex discount for a form field

[rpbsr]

I need to assign discounts based on the number of people in the household and
the income. For example:
80% if 8 people and <$78K
80% if 7 people and <$73K
....80% if 1 person and <$41K

50% if 8 people and <$49K
50% if 7 people and <$46K
....

30% if 8 people and <$29K
....

Thank you!!

 

[Tom van Stiphout]

On Sat, 21 Jun 2008 12:43:01 -0700, rpbsr

In SQL Server we have the CASE statement to do this, but in Access SQL
I would use a VBA function. For sake of discussion I assume your table
is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
all three fields required.

select CalcDiscount(NumberOfPeople, Income)
from tblHouseholds

Then in a standard module write:
Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
Income As Currency) As Single
Dim sngPercent As Single
sngPercent = 0
Select Case NumberOfPeople
Case 1
Select Case Income
Case 0 To 10000
sngPercent = 0.1 '0.1 = 10%
Case 10000 To 25000
sngPercent = 0.2
'etc.
End Select
Case 2
Select Case Income
Case 0 To 11000
sngPercent = 0.11
Case 11000 To 26000
sngPercent = 0.22
'etc.
End Select
'etc.
End Select
CalcDiscount = sngPercent
End Function
(I used fake numbers, not your sample numbers)

-Tom.

 

[rpbsr]

On Sat, 21 Jun 2008 12:43:01 -0700, rpbsr

In SQL Server we have the CASE statement to do this, but in Access SQL
I would use a VBA function. For sake of discussion I assume your table
is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
all three fields required.

select CalcDiscount(NumberOfPeople, Income)
from tblHouseholds

Then in a standard module write:
Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
Income As Currency) As Single
Dim sngPercent As Single
sngPercent = 0
Select Case NumberOfPeople
Case 1
Select Case Income
Case 0 To 10000
sngPercent = 0.1 '0.1 = 10%
Case 10000 To 25000
sngPercent = 0.2
'etc.
End Select
Case 2
Select Case Income
Case 0 To 11000
sngPercent = 0.11
Case 11000 To 26000
sngPercent = 0.22
'etc.
End Select
'etc.
End Select
CalcDiscount = sngPercent
End Function
(I used fake numbers, not your sample numbers)

-Tom.

Thanks Tom. I've never used a module. You didn't mention a discount field
for the table. Does this mean it would only appear on the form? How would I
get that form field to display the discount?

Thank you.

 

[Larry Linson]

Thanks Tom. I've never used a module. You didn't
mention a discount field for the table. Does this mean
it would only appear on the form? How would I
get that form field to display the discount?

Modules are containers for VBA code. Strictly speaking, that is the only
use for modules. The uses for VBA code are many and varied, and one such use
would be to perform complex calculations.

You will find Fields in Tables and Queries. You may see the contents of
Fields displayed in, or entered into, Controls on Forms. Form Controls may
be unbound, in which the content is set from VBA code or entered from the
keyboard; may be bound to a Field, in which the content is retrieved from
and/or saved in a Field specified in the Control Source property; or may be
Calculated Controls, in which case, the Control Source is an expression
(which may refer to a function that exists in VBA code in a module).

There are a number of ways to determine the discount used... that might
include a Table indexed by family size and income, a function with input
arguments of family size and income, or some other calculation.

Access is a software tool that makes it easy to manipulate data (arguably,
there is no easier-to-learn and easier-to-use software tool for this
purpose). That does not mean that it makes it so easy that there is _no_
learning required to implement non-trivial database applications.

Larry Linson
Microsoft Office Access MVP

 

[Tom van Stiphout]

On Sat, 21 Jun 2008 13:53:00 -0700, rpbsr

In addition to what Larry said: you should NOT have a Discount field,
because an important database design rule says: do not store values
that you can calculate.

-Tom.