Simple Calculated Field in Access Query

A query is a request to the database to return required data or perform some action to the database. Let us create the table first.

1. Start your MS Access.

Go to Start >> All Programs >> Microsoft Office >> Microsoft Office Access 2007

2. Click Blank Database under New Blank Database category. Enter the file name (School in my case). Then click Create button.

3. Create a new table Marks as below:

SerialNumber   Number  Long Integer

English       Number     Integer

Nepali         Number     Integer

Maths         Number     Integer

Science      Number     Integer

Computer   Number     Integer

Access 2007 bring to new table immediately when you create a new database. You can click on View button and Design View to switch.

Because SerialNumber is unique field in our table, it is candidate to be primary key. Click the row SerialNumber and click on Primary Key button (Just after that View button). That’s great!

4. Click on View button and switch to Datasheet View. If Access asks you to save the table first, click ‘Yes’.

5. Enter some sample data. I entered the data as below:

Good!

Now we are ready to create a query with calculated fields Total, Percentage, Result and Division.

Creating Query in Access is really simple and intuitive. You can use Query Wizard or create by scratch through Query Design. I’ll be creating through Query Design here.

1. Click on Create tab in ribbon and then choose Query Design.

2. Click Add on ‘Show Table’ dialog box. This will select our ‘Marks’ table to base the query on. After that click on ‘Close’ button to remove the ‘Show Table’ dialog box.

A box for Marks table is displayed at the upper part of Query Design Window with the list of fields in Marks Table.

3. Select all the fields and drag from the ‘Marks’ table to drop into Field on the bottom part of Query Design Window.

4. We included all the fields from Marks table into our query. You can click on Run button (Just after that View button on ribbon) to see what the query returns. But, we have not added any calculated fields yet. Let’s add ‘Total’ field first.

5. Click in the Field cell in empty column (after Computer column) and type following string:

Total:English+Nepali+Maths+Science+computer

In this string the text before colon is name of field and after colon is expression to build value for that field. In our example ‘Total’ is the name of column and English+Nepali+Maths+Science+Computer computes the value for Total field.

After you enter the expression Access converts it as below:

Total: [ English ] + [ Nepali ] + [ Maths ] +

[ Science ] + [ computer ]

This is because in Access Field names are enclosed in square braces. Its better you type those braces yourself when entering query expressions.

Let’s run and see if query returns correct result

That’s good! Total returns correct numbers.

6. Go back to Query Design View and create other calculated fields too!

Click on View button and choose Design View to switch back.

Use following strings for the rest fields

Percentage: [ Total ] / 5

Result: IIf ( [ English ] >= 40 And [ Nepali ] >= 40 And

[ Maths ] >= 40 And [ Science ] >= 40 And [ Computer ] >= 40 ,

 "Pass" , "Fail")

Division: IIf ( [ result ] = "Fail" , "****" ,

IIf ( [ Percentage ] >= 80 , "Distinction" ,

IIf ( [ Percentage ] >= 60 , "First" ,

IIf ( [ Percentage ] >= 45 , "Second" , "Third"))))

7. Run your query to see how the finished query looks