calculations in tables...

[avw410]

I was asked to convert some excel spreadsheets into an access database and I
am having trouble with the calculations. in the spreadsheet there where
several cells with formulas in them to add rows and columns. I put all the
data in a table in access but I cannot figure out how to get them to add up.
for example. we have a shop hours column and next to it the cost. the cost
equals the hours * 20. I got the cost to add up in a query using the
expression builder =["Shop Hours"]*20 but I cannot change the number in the
query to get an instant update. is there a way to add a formula in a
table???

 

[Jerry Whittle]

Formula in a table? Nope. You can do it in a query, form, or report. You need
to rerun a query or report to see the most recent data returned. It's
possible to refresh the data in a form to see any changes.

 

[boblarson]

99% of the time, you don't store calculated values like that in the tables.
Since you have all of the fields necessary to do the calculations, you can
just do the calculations in a QUERY.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[John W. Vinson]

I was asked to convert some excel spreadsheets into an access database and I
am having trouble with the calculations. in the spreadsheet there where
several cells with formulas in them to add rows and columns. I put all the
data in a table in access but I cannot figure out how to get them to add up.
for example. we have a shop hours column and next to it the cost. the cost
equals the hours * 20. I got the cost to add up in a query using the
expression builder =["Shop Hours"]*20 but I cannot change the number in the
query to get an instant update. is there a way to add a formula in a
table???

No.

Excel is a spreadsheet, a very good one.
Access is a relational database.

THEY ARE DIFFERENT and require different logic! A Table may look like a
spreadsheet but it is NOT a spreadsheet. "You can drive a nail with a crescent
wrench but that doesn't make it a hammer"!

Store data in your tables; do your calculations in a Query, or in textboxes on
a Form or Report. You will almost surely need to restructure the data in your
tables so that it is "normalized" - a good design for a spreadsheet can be a
very bad one for a relational data table, and vice versa.

You don't say where the 20 is coming from - if it's a field in a table you can
use

[ShopHours] * [BillingRate]

to calculate it, if you have both fields in appropriate tables and have joined
the tables in the query. Note that you do not need and should not have the
quotes within the fieldname, and that blanks should also be avoided.

For some tutorials on how to make the best use of Access see (among others):

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]

 

[fredg]

I was asked to convert some excel spreadsheets into an access database and I
am having trouble with the calculations. in the spreadsheet there where
several cells with formulas in them to add rows and columns. I put all the
data in a table in access but I cannot figure out how to get them to add up.
for example. we have a shop hours column and next to it the cost. the cost
equals the hours * 20. I got the cost to add up in a query using the
expression builder =["Shop Hours"]*20 but I cannot change the number in the
query to get an instant update. is there a way to add a formula in a
table???

1) Write on your blackboard, in large print ...
"Access is not the same as Excel.
Access is not a bigger version of Excel.
Access is not a spreadsheet."

Repeat the above 100 times.

2) You can delete the Cost field from your table. It is not needed.

3 In Access all you need do is store the [Shop Hours] value in your
table.

4) Then you can create a query, and include all of the fields
necessary for your calculations.
Add a new column to the query grid:
Cost:[Shop Hours] * 20

Notice that there are no quotes around Shop Hours, but that it is
enclosed within brackets [].

Use this [Cost] field on your report, if that's what you are trying to
do.

or...
You can not use a query and do the calculation directly on your form
or report, using an unbound text control:
=[Shop Hours] * 20

5) Whenever you need the result of the calculation, re-calculate it,
as above.

The above should get you started in the correct direction.