calculating variable data

[Brad L.]

I'm attempting to build a relatively simple training log
to track daily exercise info... i.e activity, miles,
duration, etc.

I'm having a problem trying to figure out how to calculate
variable data.

In essence, I have an activity log data entry form which
includes activity, duration, calories burned.

I have a combo-box to select activity (cycling, running,
walking) "fed" by an activity table which includes each of
the activities and their caloric expenditure rate per
minute.

What I would like to do is include a calculated field
based on the caloric expenditure rate of the chosen
activity multiplied by the duration.

How can I express that I would like to find the caloric
expenditure rate of the chosen activity then multiply it
times the entered duration?

It seems like this should be relatively simple, but it's
driving me nuts... Tons of thanks in advance.

Brad

 

[Jeff Boyce]

Brad

You already have an Activity table that lists Activity and calories per
minute, right? And you're already using that Activity table to populate a
combobox in a form, right?

Simply add the cpm field to the query/SQL you use on the combobox (it will
be the 3rd, or 4th, or ...nth column). Now, once you have the last
component entered you need for your calculation (?minutes), multiply the
factor(s) times the cpm. You can "get at" the cpm by referring to the
combobox's value in the column holding cpm. NOTE: the Column() property is
zero-based, so your formula would include something like the following if
the cpm were in the 3rd column:
Me.cboYourComboBoxName.Column(2)

Good luck!

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

Brad

P.S. No need to store that calculated value -- since you're using a form,
just calculate it on the fly. Saves synchronization headaches and (albeit
minor) hard drive space.

Use this same calc-on-fly approach for any other needs (e.g., query, report,
....)

Jeff Boyce
<Access MVP>

 

[Brad L.]

You already have an Activity table that lists Activity
and calories per

minute, right? And you're already using that Activity table to populate a
combobox in a form, right?

Yes. The table is named 'ActivityLkp.' The fields are, in
order, 'Activity' and 'CaloricExp.' They are the only two
fields, and 'Activity' is the primary key.

Simply add the cpm field to the query/SQL you use on the combobox (it will
be the 3rd, or 4th, or ...nth column). Now, once you have the last
component entered you need for your calculation (? minutes), multiply the
factor(s) times the cpm. You can "get at" the cpm by referring to the
combobox's value in the column holding cpm. NOTE: the Column() property is
zero-based, so your formula would include something like the following if
the cpm were in the 3rd column:
Me.cboYourComboBoxName.Column(2)

I've tried plugging that into the data dource of the text
box where I intend to have the calculated field. I've
plugged it in with no calculations, just to see if it was
passing the data, and to eliminate the possibility of a
syntax error in the calculation portion of the expression.
Here is the expression exactly as I've entered it:

Me.cboActivityDrop.Column(1)


Unfortunately, It's still not working. It seems no matter
what I try, the field displays #Name.

I was even tempted to try to find some way to hammer out
some crude If/Then type expression. (i.e. to the
equivalent of:

="Variable" * Duration
If ActivityDrop = "walking" Then "Variable"=10
If ActivityDrop = "Cycling" Then "Variable"=15
If ActivityDrop = "Running" Then "Variable"=20

Of course, as you can probably tell I have almost no
coding knowledge or experience, and so that is not
indicative of actual code, just the basic logical
construct I was aiming for.

However, that really seems to be a wonky, roundabout, "re-
inventing the wheel" approach to something I'm certain can
be accomplished with much less difficulty, if I can only
wrap my brain around it.

Thanks again for your help. I suppose I'll just have to
keep fiddling with it and plodding around until I can
figure out what I'm doing wrong.

(Also, you had helped me out with a question I had a few
weeks ago, and I didn't back in time to offer my
gratitude. So, a much belated thanks for your previous
advice as well.)

 

[Jeff Boyce]

Brad

By "add it to", I mean to open the query in design mode and include the cpm
as an output/display field of the query. Then, every time you select an
Activity from the combo box that's based on that query, the value of
Me!YourComboBoxName.Column(1)
will be the cpm for the activity selected/displayed.

Good luck

Jeff Boyce
<Access MVP>