In This Site

In This Section

The number one source for making your website sell!

In our last tutorial, Filtering Records Using Criteria in your Acces Queries, you learned how to set criteria to limit which records will be displayed in your query. In this lesson, we'll take this one step further to create calculated controls to add new features to your queries.

Open up the Northwind Database and open the qGroupBy query that you created in your last lesson. The datasheet view should resemble the following image:

Now, return to design view. Change ALL of the total fields to equal "Group By" and you will see the familiar view below:

It's time to make some changes to this query. From the OrderDetails table, drag the UnitPrice field to the query design window. Your query will now look like the following:

When you view the datasheet view, you now have a list of how many units of each product were shipped to each country AND the unit cost of each unit. Now, what if you want to display a field for the dollar value of the shipments to each country? There is no such field in any of the tables, however the Access query window allows us to create new fields that combine existing fields in a wide variety of ways. This concept is also covered in another tutorial called Combining Fields in your Microsoft Access Queries.

In this situation, we want to create a new field called OrderValue that is created by multiplying the Quantity times the UnitPrice. Access makes this very easy. Right click in a blank column on your query design window and select "Build". This will open the Expression Builder. Now type:

OrderValue:

Your screen will look like the following. Don't forget to put a colon ( : ) after the expression that will represent the new name of your calculated field.

Now, in the lower left, there are lists of tables and queries. If the list of tables is not expanded, then double-click on Tables to expand the list of tables. Click on the Order Details table to display a list of fields in the middle column. Now double-click on the Quantity field and it will be added to the Expression Builder. Your screen will look like the following:

You will see that it added <Expr> before the field name. Delete this. Often, we place a mathematical expression BEFORE field names. In this case we do not need this. Now, place a space after the Quantity field and click the * icon on the tool bar. This will add our multiplication symbol. After another space, double-click on the UnitPrice field name. This will add the Unit Price to the Expression Builder. Your screen will now look like the following:

You have now entered all you need to enter in order to create a new control called OrderValue that will be equal to the Quantity X the Unit Price. Click OK and your Query Design window will look like the following:

While this screen shot does not display the ShipCountry and ProductName fields that are just out of view to the left, it does show how Access simply takes the expression that you just created and places it into the heading on a blank query field. Switch to datasheet view and you will see your new column listing the total value of the orders for each product shipped to each country. It should resemble the following:

You've now created a new field that calculates a value based upon other fields. Once you become comfortable with this process, the possibilities become endless. If you can conceive of a mathematical formula, then you can create a field to display the results.

Other Examples of Calculated Fields

There are far too many possibilities to list here. When you are creating complex formulas, be sure to take advantage of the Expression Builder. It will walk you through the process of building your expressions AND help you make sure you use the proper syntax.

In the next lesson, we'll look at the many ways you can combine fields in your queries. Click here to read it now