Calculating a field based on a lookup range

  • Thread starter Thread starter Normsn
  • Start date Start date
N

Normsn

(As a beginner) I am trying to do 2 things

1) On a form, calculate a field (sales total) based on a lookup range
– i.e. form has a sales price (data entry) and a total (calculated
control that refers to a table - tblRate)
The tblRate has 4 fields - LowPrice, HighPrice, SalesRate, CommRate
The first 2 records are $0, $4.99, 30%, 40% and $5.00, $6.99, 40%,
50%
The idea being that if the sales price is $3 look it up in the tblRate
(in this case - between $0 and $4.99 and multiply the sales price by
30% etc.)

Given that there may be many records in the tableRate, how do I code
the calculation on the form without using a lot of code and with the
ability to add more records easily in the tblRate.

2) Once the form is calculated – what’s the easiest way to write the
major form fields to a new table (I know you don’t usually store
calculated fields- but the rate changes frequently so I need to have a
history transaction file) – how do you write the code to store the
‘output’ of the calc fields

Many thanks for your help
 
(As a beginner) I am trying to do 2 things

1) On a form, calculate a field (sales total) based on a lookup range
– i.e. form has a sales price (data entry) and a total (calculated
control that refers to a table - tblRate)
The tblRate has 4 fields - LowPrice, HighPrice, SalesRate, CommRate
The first 2 records are $0, $4.99, 30%, 40% and $5.00, $6.99, 40%,
50%
The idea being that if the sales price is $3 look it up in the tblRate
(in this case - between $0 and $4.99 and multiply the sales price by
30% etc.)
Click to expand...

A (rather advanced and unusual) type of Query is probably the best
solution here. YOu'll need to go to the SQL window to create it, it
isn't available in the query grid. Start by joining your tblRate to
your main table joining [Price] to [LowPrice] and selecting the fields
you want to display; then open the query in SQL view and edit the part

INNER JOIN [tblRate] ON [yourtable].[Price] = [tblRate].[LowPrice]

to

INNER JOIN [tblRate] ON [yourtable].[Price] >= [tblRate].[LowPrice]
AND [yourtable].[Price] < [tblRate].[HighPrice]


You can then simply include SalesRate and CommRate as control sources
for forms or in any needed calculation expression.
Given that there may be many records in the tableRate, how do I code
the calculation on the form without using a lot of code and with the
ability to add more records easily in the tblRate.

2) Once the form is calculated – what’s the easiest way to write the
major form fields to a new table (I know you don’t usually store
calculated fields- but the rate changes frequently so I need to have a
history transaction file) – how do you write the code to store the
‘output’ of the calc fields
Click to expand...

Valid choice here! This isn't "derived data" - it's data as of a
specific point in time.

Use the Form's BeforeUpdate event to "push" the calculated controls'
values into bound (perhaps invisible) textboxes:

Me!txtRealPrice = Me!txtShowPrice
 
Back
Top