Simple Calculated Field in Access Query

• Tweet
• Email

• Tweet
• Email

This post is in response to the email I received at [email protected] from an ICT Trends member. He/she asked to show how result sheet can be calculated from query. I tried to make it a bit more general solving the problem.

While designing databases, the derived attributes are not included in tables. Derived attributes are the fields whose values can be obtained by performing calculations on other fields. The reason is pretty obvious – they can always be derived and thus useless to waste space by storing those data. Lets take an example.

Let us have a table that stores the a Serial Number and marks in five subjects English, Nepali, Maths, Science and Computer. We also need total, percentage, result and divisions for our application. But because we can calculate total, percentage, result and division, we do not create fields while creating table. Then how those information are presented in databases? Its through 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

That’s all! I hope you now can create any calculated query if you know the expression! Please practice the same problem in Access 2003 too as we don’t know for sure for this year, whether the exams will be taken in 2003 or 2007 version of Office.

Good Luck.

Tagged As: access, calculated query, database, practical question