A calculated field is a column worked out from other fields by a formula, shown in a query or report without being stored in the table. It appears in tasks such as “show each member’s attendance as a percentage” or “show the fee per session”.
Support differs between tools, and some tasks ask for the calculation in a spreadsheet instead. Follow your task’s instructions on where to calculate. This lesson belongs to database queries and reports.
How do you build one?
- Name the result in words, for example Attendance percent.
- Identify the source fields and any fixed number the formula needs.
- Write the formula using field names, brackets where order matters.
- Set the display format: number of decimal places and whether it is a percentage.
- Test one record by hand against the formula result.
Worked example
Using the invented Harbour Hill Clubs table from the module page. The club ran 12 sessions in total.
Task: add a field called Attendance showing the percentage of the 12 sessions each member attended, to one decimal place.
Step 1, formula: Attendance = Sessions / 12 * 100
Step 2, test Dewi (11 sessions): 11 / 12 = 0.91666…, and 0.91666… * 100 = 91.666…
Step 3, format: one decimal place gives 91.7.
Step 4, a second test, Evan (7 sessions): 7 / 12 = 0.58333…, times 100 is 58.333…, displayed as 58.3.
Step 5, spot-check others: Bella, Farah and Imran with 12 sessions give 100.0. Jia with 5 gives 41.7.
The mistake to watch for
Applying both a times-100 and a percentage format:
Attendance = Sessions / 12 * 100, with the field formatted as Percent.
Dewi shows 9166.7%, because the format multiplies by 100 a second time.
The correction is to choose one method. Either keep the formula as Sessions / 12 and format the field as Percent, which shows 91.7%, or keep * 100 and format as a plain number. Testing one record by hand, as in step 2, exposes the doubling at once.
Check yourself
1. Write a formula for fee per session, then give the value for Gopal (fee RM45, 6 sessions).
Show answer
Fee / Sessions. For Gopal, 45 / 6 = 7.5, so RM7.50 per session.
2. What is Evan’s attendance percentage to one decimal place?
Show answer
7 / 12 * 100 = 58.333… so 58.3.
3. A late charge adds 10% to the fee. Write the formula and give Jia’s new fee (RM60).
Show answer
Fee * 1.1. For Jia, 60 * 1.1 = 66, so RM66.00. A second way: 60 + 60 * 0.1 = 60 + 6 = 66.
Where this leads next
Now group a report without hiding required values, which uses subtotals built from fields like these. The read-only SQL practice lab shows calculated columns beside source fields, and the practice set includes more calculations.
If formulas look correct but results feel off, online one-to-one ICT tuition lets a teacher test your formula on a record with you.