Be able to use arithmetic operations or numeric functions to perform calculations including calculated fields, calculated controls

Topic 18 – Databases

Objective

Be able to use arithmetic operations or numeric functions to perform calculations, including the creation of calculated fields and calculated controls in a database.

1. Why use calculations in a database?

• Eliminate repetitive manual calculations.
• Ensure data consistency – the result is always based on the latest values.
• Provide instant feedback to users (e.g., totals, discounts, grades).
• Support decision‑making by summarising data automatically.

2. Arithmetic operations

Most database programs support the four basic arithmetic operators:

3. Numeric functions commonly available

4. Calculated fields

A calculated field stores the result of a formula that uses values from other fields in the same record. The field itself is not edited directly; its value updates automatically whenever the source fields change.

4.1 Syntax example (generic)

CalculatedField = Expression

Example – total price of an order line:

\$\text{TotalPrice} = \text{UnitPrice} \times \text{Quantity}\$

4.2 Practical example in a sales database

1. Fields in the OrderLines table: UnitPrice, Quantity, Discount%.
2. Create a calculated field called NetAmount with the expression:

   \$\text{NetAmount} = (\text{UnitPrice} \times \text{Quantity}) \times \bigl(1 - \frac{\text{Discount\%}}{100}\bigr)\$

3. If UnitPrice = 15.00, Quantity = 4, Discount% = 10 then:

   \$\text{NetAmount} = (15.00 \times 4) \times (1 - 0.10) = 60.00 \times 0.90 = 54.00\$

5. Calculated controls (forms)

Calculated controls are similar to calculated fields but are placed on a form or report. They show a result that may combine data from several records or perform aggregate calculations.

5.1 Example – running total on a sales invoice form

Assume a form lists all line items for a single invoice. Add a control named txtRunningTotal with the expression:

\$\text{RunningTotal} = \sum{i=1}^{n} (\text{UnitPrice}i \times \text{Quantity}_i)\$

The control updates each time a new line is added or an existing line is edited.

5.2 Example – age calculation from a date of birth field

Field: DateOfBirth (date type). Calculated control txtAge uses:

\$\text{Age} = \text{INT}\!\bigl(\frac{\text{Today()} - \text{DateOfBirth}}{365.25}\bigr)\$

This returns the whole number of years between today and the stored birth date.

6. Step‑by‑step guide to creating a calculated field in a typical IGCSE‑level database program

1. Open the table in Design \cdot iew.
2. Add a new field name (e.g., TotalCost).
3. Set the Data Type to a numeric type (e.g., Currency or Number).
4. Locate the Calculated or Expression property (often labelled “Default \cdot alue” or “Calculated \cdot alue”).
5. Enter the expression using field names, e.g.:

   =[UnitPrice]*[Quantity]

6. Save the table design. The field now displays the computed result for each record.

7. Common pitfalls and how to avoid them

• Division by zero – always check that the divisor is not zero before using /. Use a conditional expression such as:

  IIf([Divisor]=0, 0, [Numerator]/[Divisor])

• Rounding errors – use ROUND() to limit decimal places, especially for currency.
• Incorrect field references – field names are case‑insensitive but must be spelled exactly as they appear in the table.
• Data type mismatch – ensure all fields used in the expression are numeric; convert text to numbers with VAL() if necessary.

8. Practice questions

1. In a table Students you have fields Mark1, Mark2, Mark3. Write the expression for a calculated field Average that rounds the result to one decimal place.
2. A payroll form contains HourlyRate and HoursWorked. Create a calculated control that also adds a 12% tax to the gross pay.
3. Explain how you would prevent a “divide by zero” error when calculating a ratio Score/MaximumScore.

9. Summary

• Arithmetic operators (+, –, *, /, %) and numeric functions (ABS, ROUND, etc.) allow you to manipulate numeric data directly in a database.
• Calculated fields store results that update automatically whenever source fields change.
• Calculated controls display dynamic results on forms or reports, useful for totals, averages, ages, and other derived values.
• Always consider data types, rounding, and error handling (e.g., division by zero) when building expressions.