Calculations based on other field values

Calculations based on other field values

am 30.08.2007 23:10:26 von LesYeuxBrass

I'm producing a database to calculate projected costs of our mailings
in-house and am trying to introduce a formula which will determine
what postage to apply based on envelope size and weight.

I have two tables providing data for this -

Table A contains information about the mailing itself, weight, number
of recipients, etc.
Table B contains information about the type of packaging - small,
medium or large envelopes

On the form, the User selects the size of envelope and it plugs in
data in a hidden field that provides a "base rate" (the number of
packages that can be assembled in an hour) which helps determine how
long a mailing will take.

The postage is different based on size of envelope and weight by each
size. Small has two weight classes, medium has 3, and large is
prorated by weight over a certain amount. I've created a formula for
each package size that will provide the required information but I
can't get the form to use it when I select an envelope size.

What I envision -

User selects size of envelope
Base Rate updates with the estimated value
Postage field automatically updates with the appropriate formula
related to envelope size
Formula calculates which price to use based on weight of each package.


Any suggestions are greatly appreciated.




Steven

Re: Calculations based on other field values

am 31.08.2007 05:13:28 von Ed Robichaud

How about just displaying the calculated postage (based on user input of
weight and # of pieces) for all classes - first class, flats, parcel,
priority and express, etc. This could be done with either calculated
controls or lookup tables of rates (easier to maintain for rate changes).
-Ed



wrote in message
news:1188508226.741078.290360@i38g2000prf.googlegroups.com.. .
> I'm producing a database to calculate projected costs of our mailings
> in-house and am trying to introduce a formula which will determine
> what postage to apply based on envelope size and weight.
>
> I have two tables providing data for this -
>
> Table A contains information about the mailing itself, weight, number
> of recipients, etc.
> Table B contains information about the type of packaging - small,
> medium or large envelopes
>
> On the form, the User selects the size of envelope and it plugs in
> data in a hidden field that provides a "base rate" (the number of
> packages that can be assembled in an hour) which helps determine how
> long a mailing will take.
>
> The postage is different based on size of envelope and weight by each
> size. Small has two weight classes, medium has 3, and large is
> prorated by weight over a certain amount. I've created a formula for
> each package size that will provide the required information but I
> can't get the form to use it when I select an envelope size.
>
> What I envision -
>
> User selects size of envelope
> Base Rate updates with the estimated value
> Postage field automatically updates with the appropriate formula
> related to envelope size
> Formula calculates which price to use based on weight of each package.
>
>
> Any suggestions are greatly appreciated.
>
>
>
>
> Steven
>

Re: Calculations based on other field values

am 31.08.2007 20:33:02 von dtecmeister

On Aug 30, 5:10 pm, LesYeuxBr...@gmail.com wrote:
> I'm producing a database to calculate projected costs of our mailings
> in-house and am trying to introduce a formula which will determine
> what postage to apply based on envelope size and weight.
>
> I have two tables providing data for this -
>
> Table A contains information about the mailing itself, weight, number
> of recipients, etc.
> Table B contains information about the type of packaging - small,
> medium or large envelopes
>
> On the form, the User selects the size of envelope and it plugs in
> data in a hidden field that provides a "base rate" (the number of
> packages that can be assembled in an hour) which helps determine how
> long a mailing will take.
>
> The postage is different based on size of envelope and weight by each
> size. Small has two weight classes, medium has 3, and large is
> prorated by weight over a certain amount. I've created a formula for
> each package size that will provide the required information but I
> can't get the form to use it when I select an envelope size.
>
> What I envision -
>
> User selects size of envelope
> Base Rate updates with the estimated value
> Postage field automatically updates with the appropriate formula
> related to envelope size
> Formula calculates which price to use based on weight of each package.
>
> Any suggestions are greatly appreciated.
>
> Steven

You have to use the On Change property of Size_of_envelope. Set to
"Event procedure" and add code like:
Base_Rate.value = Size_of_envelope.value * whatever
Postage.value = Base_Rate * whateverelse


You'll need to add checks for invalid entries or nulls and insure
locks on Base_rate and Postage in form.

Re: Calculations based on other field values

am 01.09.2007 08:40:23 von Larry Linson

"DTecMeister" wrote

> You have to use the On Change property of
> Size_of_envelope. Set to "Event procedure"
> and add code like: Base_Rate.value =
> Size_of_envelope.value * whatever
> Postage.value = Base_Rate * whateverelse

You really think there's a need to recalculate on every keystroke in the
Control where the user specifies envelope size -- that is when the "Change"
event fires and when the code in the Change event is executed. Most
developers would, I think, do the recalculation in the AfterUpdate event for
that Control... after the envelope size is specified.

Larry Linson
Microsoft Access MVP

Re: Calculations based on other field values

am 03.09.2007 07:09:31 von dtecmeister

On Sep 1, 2:40 am, "Larry Linson" wrote:
> "DTecMeister" wrote
>
> > You have to use the On Change property of
> > Size_of_envelope. Set to "Event procedure"
> > and add code like: Base_Rate.value =
> > Size_of_envelope.value * whatever
> > Postage.value = Base_Rate * whateverelse
>
> You really think there's a need to recalculate on every keystroke in the
> Control where the user specifies envelope size -- that is when the "Change"
> event fires and when the code in the Change event is executed. Most
> developers would, I think, do the recalculation in the AfterUpdate event for
> that Control... after the envelope size is specified.
>
> Larry Linson
> Microsoft Access MVP

You're right, but as long as you're using a drop-down, the On Change
event works nicely and means the box doesn't necessarily need to be
tied to data (though it probably would be for tracking). I would hope
that there would be a drop-down so the choices are predetermined and
extendable.