Sooner or later you will want to perform calculations with your data. It is not good practice to store these calculations in tables, as part of the data itself, but to create them when needed.
The reason for this is quite logical. Providing the data itself contains everything necessary to perform the calculation, to store the calculated result would be a waste of space. Also, should the data change, the result would have to be recalculated and edited to remain correct.
You can create calculations in queries, reports and forms (but not in tables). Instead of making a table containing fields for, for example, Quantity, Unit Price, and Total, we build a table containing the data (Quantity and Unit Price) and a query showing these fields plus and additional calculated field which multiplies the two together. No calculations are stored, saving space in the database, but the results are instantly accessible by running the query which calculates them as they are needed.
This tutorial shows you how to perform simple calculations in a query. For more examples of calculations look at Calculating Dates in Access Queries and Calculating Totals in Access Queries.
When you perform a calculation you create and name a new field that
contains the calculation you need. To begin, open a new query in design
view and specify whatever criteria you need (if any). Then…
In the Field row of a new column type the name of your new field
followed by a colon. For example: New Field:
Type the name(s) of the field(s) to be calculated using the appropriate mathematical operators (+, -, *, / etc.). For example if you wanted to multiply two fields together: [Field 1]*[Field 2]
Here are some examples…
Total Cost: [Quantity]*[Unit Price]
In this example the query creates a new field called Total Cost and displays in it the value in the Quantity field multiplied by the value in the Unit Price field.
Discount Price: [Unit Price]*0.9
In this example the query creates a new field called Discount Price and displays in it the value in the Unit Price field multiplied by 0.9.
Note: This is the same as multiplying by 90% or subtracting 10% but the
query does not recognise the % symbol and we have to devise an alternative
When you are performing calculations, you may wish to see your results formatted in a particular way - currency for example. Normally field formatting is specified in a the design of a table, but as the field is being created by the query there is another way…
In the query's design view, right-click anywhere in the new field's column and choose Properties… from the shortcut menu (or click somewhere in the new field and choose View > Properties from the menu). This opens the Field Properties dialog box...
Click the format box, and then click the down-arrow to display a choice of formats. Choose from the list, in this example Currency is selected. Finally, click [X] to close the dialog box.
When you run the query your new field will appear, containing the
calculated values and formatted as you specified.
Calculations are simple and straightforward. You just substitute field names for numbers. If you use a calculation in a Make-Table query the new table will include a new field containing the new calculated data.
©2000 Martin Green - www.fontstuff.com - firstname.lastname@example.org - All rights reserved