Calculations in queries

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.