Calculations in Access

[lasd]

Hi all,
I have built my tables and all of my relationships are set up now.
I now have a problem with calculations. i will give you an example of what i am trying to do.

Table1
lets say it has three colums.
AE = 2.000
RAE= 2.000
APPE= this column is equal to the square root of AE to be squared and RAE to be squared.

Could you help me on doing this calculation and how i get the result of that sum to appear in the APPE column on my Table1.

Also if i want to include other tables and columns in these calculations how do i do this?

Thanks in advance for your help. It is greatly appreciated.

Kindest regards

LASD

 

[lupins46]

You cannot do calculations in a table.
You must use a query, form or report to get the results of a calculation.

 

[lasd]

Thanks so much for the reply.

I went into the query section and did an update query and did some fake calculations. is this the best way to work through this. To do an update query for each field.

thanks a mill

lasd

 

[lupins46]

You can update any number of fields in a table in a single update query. So if you want to calculate 5 fields per record this can be done in a single query.

 

[lasd]

ok i think i'm beginning to see what you mean but now i am trying to add two fields together using an update query and it won't do it for me. Both fields are the same type etc etc...
if i want to mulitply two fields together and get the result out in the third field, could you tell me how to do this.

thanks a million

lasd

 

[lupins46]

Let us say that we have a field in the table called itemvalue which is calculated as quantity times price. Quantity and price are also fields on the table.

If we are doing this in the query grid.....
Add the table to the query and close the Show Tables dialog.
Select the fields that have to be updated as columns in the grid. So in my example choose itemvalue.
Choose Query>UpdateQuery from the menu.
This adds a new row 'UpdateTo' to the grid.
In the UpdateTo cell for the itemvalue column put the calculation:
quantity*price
Save the query.
Until you are confident about this process take a backup of your file at this point.

In the database window, double click the query.
You will get a message about the number of records to be updated.
Once the query is complete the table will be updated.

Bear in mind that if you now change the quantity or price on a record the itemvalue will be wrong; it will not recalculate automatically; you would need to re-run the query.

Having described all this, I should add that keeping results of calculations in tables is generally regarded as a bad idea because it can easily lead to inconsistencies if any of the values in the calculation change. It is usually possible to repeat the calculation in a form or query whenever the result is needed.

 

[willir]

LASD

First, as part of "normalization", calculated fields are not encouraged. Fields with calculated values become a "maintenance" item. That being said, there are sometimes where storing calculations improved performance or ease of use.

As stated, you can perform calculations in a query and on a form, in VBA code.

My preference for many situations is to use a function for the calculation. This way, the same function can called from a query and from a form. By using one function, you have consistancy using one source.

Create the function in the module area.

Richard