AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Contents · Computing

Calculations in queries

Lessons 32 · 1 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
32

Performing mathematical operations in MS Access

Textbook: pp. 71–75
GoalCreates a calculated field in a query, uses arithmetic operations and the functions Sqr, Abs, Int, Log and IIf, and calculates totals by group.
New words
calculated field: a query column whose value is computed from other fields · hisoblanadigan maydonexpression: operands, operators and functions that give one value · ifodaIIf: the Access function that chooses one of two values by a condition · IIfGroup By: the Totals option that forms groups of equal values · Group By
Explanation

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.

FunctionMeaning
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 Modinteger division and remainder
💡 Where will I use this?
In real life

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.

Who uses it

Freight planners, engineers and analysts calculate new figures in queries without damaging the original data.

Try it today

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.

Worked examples
ItemPriceQtyTotalTotal2
Notebook500063000033600
An Orders table: Item, Price (so‘m), Qty. Notebook: Price = 5000, Qty = 6; Total: [Price]*[Qty] = 30000. With 12% VAT: Total2: [Price]*[Qty]*112/100 = 30000*112/100 = 33600. Price and Qty in the table do not change: Total is only a calculation in the query.
ClassMarksSumAvg
10A5, 3, 5, 4, 5224.4
10B4, 5, 3124
From the marks in the Students table: 10A has 5, 3, 5, 4, 5 (sum 22, Avg = 4.4); 10B has 4, 5, 3 (sum 12, Avg = 4). In a Totals query with Class – Group By and Mark – Avg, two rows appear. IIf([Mark]>=4,"good","low") gives good, low, good, good, good for 5, 3, 5, 4, 5.
Class activity

“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.

Practice
1
If the price is 12000 and the quantity is 3, what is Total: [Price]*[Qty]?
2
With 12% VAT added to this amount, what is the total?
3
Write the values of Sqr(144) and Log(1).
4
Why does the calculated field Total not change Price and Qty in the table?
Story of the Week · The Khwarizmi WayDid the population more than double?Close the week: all of this week’s subjects in one storyRead →