Basic math in Access

[Frank Bond]

I am trying to get access to perform a math caculation and need help. What I
am trying to do is to take a number from one table and times it by a number
in another table then insert that number into a third table. An example is:
Field named "Hours" in table one has a value of 2. Field named "Rate" in
table two has a value of "$36.50". I would like to take the hours (2) times
the rate ($36.50) which equals $73.00 and insert it into table three. Can it
be done?

 

[Jeff Boyce]

Don't.

Access isn't a spreadsheet.

It is fairly rare that you'd need to <save> the calculated value. Instead,
use a query to calculate the value 'on the fly'.

If you'll provide more information about how your tables are designed (e.g.,
what data is where), folks here may be able to offer more specific
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Frank Bond]

Table 1 has two fields: Hours and miles. Table 2 has two Fields: $36.50 and
$.75. I just need to take the numbers from table 1 and times them by table 2
and save to table 3 for billing purposes. I know that in a form or report I
can take the field from table 1 and in a unbound text box have it times by a
defined amount. The problem with that is I need the history stored and
retreiveable.

 

[John W. Vinson]

Table 2 has two Fields: $36.50 and
$.75.

Those aren't fields. Those are values. What's the structure and significance
of Table2?

 

[Frank Bond]

So in other words it can't be done? Its simple one times two entered in to
three. One is a variable with the value entered by a form. Two is a constant
value and never seen by the user. Just used to calcuate the math for billing.

 

[John W. Vinson]

So in other words it can't be done? Its simple one times two entered in to
three. One is a variable with the value entered by a form. Two is a constant
value and never seen by the user. Just used to calcuate the math for billing.

Yes, certainly it can be done. But your explanation isn't communicating!

Please post the names and relevant fieldnames of your Tables (NOT the contents
of the tables, but the actual structure of your tables) and indicate how they
are related. I simply don't understand how your database is structured, and
that's what I'm trying (faultily clearly) to ask!

 

[Frank Bond]

Table one has two fields. One called "Hours" and one called "Miles". The user
enters a numerical value.

Table two has two fields. One called "Hours with a currency value of $36.50.
The other field is called "Miles" with a currency value of $.75. The user
does not see or change these values.

I need to take the number value from the users input to the fields of table
one and times them by the values in table two. Hours by hours and miles by
miles. The trick is that I need to save the data into another table or I
would just use an unbound text box on a form or report.

 

[rpw]

Hi Frank,
Everybody here understand the calculation you want, so you don't have to
repeat it again. I'm going to try to explain a couple of things that I hope
help you to understand.

Typically at table has a name like "tblRates" and the table structure would
be something like this:

tblRates
RateID (primary key - keeps things organized in Access' mind)
RateName (text - the name of the rate)
RateAmt (currency - the amount of the rate)

Using the above structure, here's some sample data:

RateID RateName RateAmt
1 Hours 36.50
2 Miles 00.75

With this sturcture you can add any number of different 'rates'.

Here's another table:

tblUserInput
InputID (PK-primary key)
RateID (FK - aka 'foreign key' because this is how this table 'relates' to
the tblRate)
UserQuantity (number - this is what the user inputs after selecting a rate)

With this structure you can have an unlimited number of UserQuantity entries
and they can be for any of the rates in your rate table. The data would look
something like this:

InputID RateID UserQuantity
1 1 2
2 2 37

Then, as Jeff Boyce suggested, you build a query that will provide the
results you are looking for. Without going into greater detail, this how the
data 'might' be displayed (meaning just one method).

RateName RateAmt UserQuantity CalcTotal
Hours 36.50 2 73.00
Miles 00.75 37 27.75

One reason that you typically do not want to store the calculated amount in
a table is that the data takes up valuable disk space whereas the query
calculation can recalulate the amounts in an instant whenever you want to
view the amounts.

Now then, there is a potential problem with the above: What do the user
entered amounts relate to? A person, a vehicle, what? How can you tell that
just from the above data?

I hope that this makes some sense to you and helps you to better understand
Access.

 

[aaron.kempf]

Correction!

If you want to save the calculated value-- then you should be using
'computed columns' in SQL Server.
You don't store the value-- you define the calculation.

That way; it's _NEVER_ out of sync.

Try doing _THAT_ with Access MDB or ACCDB!

-Aaron

 

[Frank Bond]

rpw, your right on with what I am trying to do. You put it more elequently
then I could. Thank!

Table one hours are a persons (drivers) time and the miles are the amount of
miles they drove in that specified time. We pay the person by both hours and
miles driven. Hence the hourly rate and the per mile rate. While the rates
remain constant the time and miles vary. I have other tables that are related
such as address from and delivery address, date of delivery, drivers name,
ect. This is kind of like a billing and payroll database for a very small
business.

Your example:
RateName RateAmt UserQuantity CalcTotal
Hours 36.50 2 73.00
Miles 00.75 37 27.75
is exactly what I am trying to accomplish.

What I don't understand is how to write the query to perform the
caculations. I have written a lot of queries but have never had to have one
do math before. As I stated earlier I would just use an unbound text box if I
didn't have to save the data.

 

[aaron.kempf]

you don't have to save the data.

Just use an unbound textbox.
and duplicate that equation whenever you want to use it in another
form or report.

or if you were using SQL Server; you could use 'computed columns' in
this fashion to 'write once, use anywhere' for your important
equations.

It's just not possible with Access; because nobody gives a crap about
Access.
The database world has evolved such that Access is not a database-- it
is merely a data project to SQL Server.

Thanks

-Aaron

 

[BruceM]

I have used calculated fields (columns, if you wish) in Access (Jet, to be
specific). What difficulty do you have when you try to do that?

you don't have to save the data.

Just use an unbound textbox.
and duplicate that equation whenever you want to use it in another
form or report.

or if you were using SQL Server; you could use 'computed columns' in
this fashion to 'write once, use anywhere' for your important
equations.

It's just not possible with Access; because nobody gives a crap about
Access.
The database world has evolved such that Access is not a database-- it
is merely a data project to SQL Server.

Thanks

-Aaron

 

[Frank Bond]

SQL Server? MDB? ACCDB? i must be too dumb to understand that!

 

[rpw]

Hi Frank,
The calculation is part of the query and I'll provide you with a sample that
you will have to translate to your exact needs. However, the idea that you
have about saving that calculation to another table is against the norm in db
design and we can discuss that later. Here's what you do to accomplish the
query (using the example I used earlier):

Get to the Query Design view

Add the two relevant tables (tblRates, tblUserInput)

Double-click the relevant fields to add them to the query (RateName,
RateAmt, UserQuantity)

For the fourth field, click into the 'Field' row and then click the
'Builder' wizard button-this will open the Expression Builder. Using this,
you should be able to select the fields from the tables and/or queries that
you want to use in the calculation. The result would look something like
this:

CalcTotal: [tblRates].[RateAmt] * [tblUserInput].[UserQuantity]

From reading your previous posts, I think that you'll be able to adapt this
to your table and field names and make it work for you.

One thing concerns me and that is how you will relate the user input to a
particular driver or vehicle, but if you've got that covered then I'm
fretting uselessly.

As far as storing the calculated field into another table - why? How is
storing it any better than letting Access recalculate it again when you need
it?

 

[Bob Quintal]

SQL Server? MDB? ACCDB? i must be too dumb to understand that!

:

Ignore Aaron's stupid response, he's just here to pick a fight with
everybody.

He's been told to go away, not go away angry, but to just go away.
But he's too dumb to understand that!

As to your problem, I'll answer one of your earlier posts.

 

[Bob Quintal]

Table one has two fields. One called "Hours" and one called
"Miles". The user enters a numerical value.

Table two has two fields. One called "Hours with a currency value
of $36.50. The other field is called "Miles" with a currency value
of $.75. The user does not see or change these values.

I need to take the number value from the users input to the fields
of table one and times them by the values in table two. Hours by
hours and miles by miles.

So far so good.

The trick is that I need to save the
data into another table or I would just use an unbound text box on
a form or report.

You really should then have the results stored into new columns in
table 1, Please explain why you need the third table.

To store the values in table 1 you need to do your calculation in
visual basic that puts the result into a bound textbox. You would
use the after update of the hours and miles textboxes to set the
bound textboxes values respectively

Example:
private sub txtHours_AfterUpdate
me.txtEarnings = me.txthours * me.hourlyrate
end sub.

Doing the same thing to a third table will cause real problems for
any newbie (and many experienced programmers).

 

[John W. Vinson]

SQL Server? MDB? ACCDB? i must be too dumb to understand that!

It's Aaron's answer to every question. It can be the right answer in some
cases... but it's Aaron's ONLY answer, no matter what the question.

 

[rpw]

Hi Frank (again),

I just re-read your last post and figured because you state that you "have
to save the data" I'd offer a different route to the solution. Another
poster wanted to know how to save the calculated amount and I provided a
method. The method involves some VB coding in the form's control's events
and it writes to the form's associated table. You can view the thread here:

http://www.microsoft.com/office/com...=&sloc=en-us&dg=microsoft.public.access&fltr=

In that poster's problem it was almost clear as to why the calculation
needed to be saved (which again, is against the norm). I guess your reasons
are your reasons, just please be aware that in MOST cases in Access it
usually is not done.

Hope all this helps....

 

[Bob Quintal]

m:

Bob;

I don't appreciate your personal attacks.

I didn't attack you. I will admit attacking your misdirections and
erroneous information. As long as you insist on posting your
misinformation here, I reserve the right to respond, in order to
provide adequate solutions to people's questions.

If that upsets you go awy. I'd prefer you not go away angry, but please
do go away.

 

[aaron.kempf]

Bob;

I don't appreciate your personal attacks.

I stand by the _FACTS_ that SQL Server supports 'computed columns' and
Access does not.

SQL Server can take an equation and _GUARANTEE_ that it is correct.
Access cannot.

Access can store the stale version.
But storing a stale version is not as much fun as storing the
calculation via a computed column.

Sorry that your cry baby database doesnt meet your needs. I'd
reccomend moving to SQL Server to utilize industry best practices for
keeping timely information and centralizing business logic.

-Aaron