Performing mathematical operations in MS Access
In an Access query you can calculate a new value from the fields of a table: in the Field row write “Name: expression”, for example Total: [Price]*[Qty], with field names in square brackets. The arithmetic operations are + – * /, power ^, integer division \ and remainder Mod; among the functions are Sqr(x) – square root, Abs(x) – absolute value, Int(x) – integer part (rounding down), Sin, Cos, Tan and Log(x). Note: in Access, Log gives the natural logarithm (base e ≈ 2.718), not the base-10 logarithm; so Log(1) = 0. To choose by a condition, use IIf(condition, value1, value2), for example IIf([Mark]>=3,"Pass","Fail"); depending on the local settings the arguments are separated by a comma or a semicolon. For calculations by groups, press Σ Totals in Query Design, choose Group By for the group field and Sum, Avg, Min, Max or Count for the calculated field. Because the decimal separator depends on settings, a form such as [Price]*12/100 is convenient for percentages.
| Function | Meaning |
|---|---|
| Sqr(x) | square root |
| Abs(x) | absolute value |
| Int(x) | integer part (rounding down) |
| Log(x) | natural logarithm |
| IIf(condition, v1, v2) | choose by a condition |
| \ and Mod | integer division and remainder |
A trip organiser wrote the distance and the average speed in a table and added a third, calculated field in a query: the travel time. Covering 60 km at 30 km per hour takes 2 hours. The original columns stayed unchanged, and the new column appeared by itself for every row.
Freight planners, engineers and analysts calculate new figures in queries without damaging the original data.
In your notebook write 3 trips with columns for distance (km) and speed (km per hour), and calculate the time (distance ÷ speed) in a third column; see that the first two columns stay unchanged.
| Item | Price | Qty | Total | Total2 |
|---|---|---|---|---|
| Notebook | 5000 | 6 | 30000 | 33600 |
| Class | Marks | Sum | Avg |
|---|---|---|---|
| 10A | 5, 3, 5, 4, 5 | 22 | 4.4 |
| 10B | 4, 5, 3 | 12 | 4 |
“The shop query”: a pair builds a 5-product table, calculates Total and the amount with VAT, adds a cheap/expensive column with IIf and checks the results with a calculator.