Calculated Fields in MS Access

A calculated field uses existing field values, constants, operators, and functions to produce a result.

Example: Student Fees

PendingFees can be calculated by subtracting PaidFees from TotalFees:

PendingFees: [TotalFees]-[PaidFees]

The expression calculates the remaining fees for each student when used in a query.

Follow these steps to create a calculated field:

1. Open your MS Access database.
2. Click the Create tab.
3. Select Query Design.
4. Add the table containing the required fields.
5. Double-click the fields you want to use.
6. Click an empty Field cell in the query design grid.
7. Enter a field name followed by a colon and an expression.
8. Click Run (!) to display the calculated results.
9. Save the query with a meaningful name.

Example

PendingFees: [TotalFees]-[PaidFees]

Here, PendingFees is the calculated column name. The expression subtracts PaidFees from TotalFees.

Arithmetic operators allow you to perform calculations using numeric fields.

Example: Total Price

TotalPrice: [Quantity]*[UnitPrice]

If Quantity is 5 and UnitPrice is 200, TotalPrice will be 1000.

Example: Average Marks

AverageMarks: [English]+[Hindi]+[Computer]

This expression adds the three marks. To calculate the average, divide the total by three:

AverageMarks: ([English]+[Hindi]+[Computer])/3

Calculated fields can be used to calculate discounts on product prices or course fees.

Example: Calculate a 10% Discount

DiscountAmount: [Price]*10/100

If Price is 2000, DiscountAmount will be 200.

Calculate Final Price

FinalPrice: [Price]-([Price]*10/100)

If Price is 2000, the final price after a 10% discount will be 1800.

Example: Student Fee Discount

DiscountAmount: [CourseFees]*[DiscountPercent]/100

FinalFees: [CourseFees]-([CourseFees]*[DiscountPercent]/100)

This example assumes DiscountPercent stores a number such as 10 for a 10% discount.

You can calculate tax amounts using a calculated field.

Example: Calculate 18% GST

GSTAmount: [Price]*18/100

Calculate Price Including GST

PriceWithGST: [Price]+([Price]*18/100)

If Price is 1000, GSTAmount will be 180 and PriceWithGST will be 1180.

You can write calculated expressions directly in an Access SQL query.

Example 1: Calculate Pending Fees

SELECT StudentName,
       TotalFees,
       PaidFees,
       [TotalFees]-[PaidFees] AS PendingFees
FROM Students;

The AS keyword assigns the calculated result the column name PendingFees.

Example 2: Calculate Total Price

SELECT ProductName,
       Quantity,
       UnitPrice,
       [Quantity]*[UnitPrice] AS TotalPrice
FROM Products;

This query calculates the total price for each product using Quantity and UnitPrice.

Example 3: Calculate Average Marks

SELECT StudentName,
       ([English]+[Hindi]+[Computer])/3 AS AverageMarks
FROM Students;

This query calculates the average of the three subject marks for each student.

The IIf() function checks a condition and returns one value if the condition is true and another if it is false.

Syntax

IIf(condition, value_if_true, value_if_false)

Example: Pass or Fail

Result: IIf([Marks]>=40,"Pass","Fail")

If Marks is 40 or more, the result is Pass. Otherwise, the result is Fail.

Example: Fee Status

FeeStatus: IIf([PaidFees]>=[TotalFees],"Paid","Pending")

This expression returns Paid when PaidFees is greater than or equal to TotalFees. Otherwise, it returns Pending.

Calculated controls can also be created in forms and reports to display totals, balances, and other derived values.

Example: Text Box Control Source

To calculate pending fees in a form or report, set the control's Control Source property to:

=[TotalFees]-[PaidFees]

Example: Calculate Total Marks

=[English]+[Hindi]+[Computer]

Example: Calculate Percentage

=([ObtainedMarks]/[MaximumMarks])*100

These expressions calculate values for display. A calculated text box does not automatically store the result in a table field.

• Use the correct field names in expressions.
• Use square brackets around field names.
• Use parentheses to make the calculation order clear.
• Ensure fields contain compatible data types.
• Avoid dividing by zero.
• Consider how Null values should be handled.
• Use meaningful names for calculated columns.
• Test expressions with several records before using them in reports.

Example: Avoid Division by Zero

AverageMarks: IIf([MaximumMarks]=0,Null,[ObtainedMarks]/[MaximumMarks]*100)

This expression returns Null when MaximumMarks is zero and calculates the percentage otherwise.

Create a table named Students with these fields:

• StudentID — AutoNumber
• StudentName — Short Text
• TotalFees — Currency
• PaidFees — Currency
• English — Number
• Hindi — Number
• Computer — Number

Tasks

1. Enter at least five student records.
2. Calculate PendingFees using TotalFees minus PaidFees.
3. Calculate TotalMarks by adding the three subject marks.
4. Calculate AverageMarks by dividing TotalMarks by three.
5. Calculate Result using IIf() with a pass mark of 40.
6. Calculate a 10% discount on TotalFees.
7. Calculate FinalFees after discount.
8. Display the calculated values in a Select query.
9. Create a form that displays PendingFees.

Goal: Practice arithmetic expressions, calculated columns, and conditional calculations in MS Access.

1. What is a calculated field?
2. How do you create a calculated field in Query Design View?
3. What is the purpose of the AS keyword in SQL?
4. How do you calculate pending fees?
5. How can you calculate a discount of 10%?
6. What does the IIf() function do?
7. How can you display a calculated value in a form?
8. Why should division by zero be avoided?