| Lesson 13 || Creating calculated fields |
| Objective || Add a calculated field to a query. |
There may be times when you want to add a calculated field to your database. Perhaps you want to use two fields to calculate a third, or want to use one of Accesss functions to calculate a result. Although creating calculations in Access is not quite as simple as calculating in a spreadsheet such as Excel, you should not be shy about exploring.
Your data will be more consistent and accurate if you learn how to perform calculations within your database.
In order to create a calculated field you type an expression (sometimes called a formula) into the Field row in query Design view. The new field is given a name, and as long as you save the query, that new field is available for you to use in other queries as well as in forms and reports. This SlideShow shows you how to create a calculated field.
1) This table contains information about sales.
2) In order to create a calculated Commissions field
3) In order to see all the fields in the Sales table, I double-click the asterisk(*) in the table box
4) Type the expression for the new field into the Field row of the first empty column.
5) The datasheet shows the new field, Commission
How To Create Calculated Fields
When you are creating an expression you must tell Access what kind of terms you are using. Remember that Access encloses some kinds of data between special characters.
Well, when you are creating an expression you must be sure to type those special characters in, or Access will not understand the expression.
|This type of data ||Looks like this in an expression |
|Text || text |
| Date || #1/1/00# |
| Number || 10 |
| Field Name || [field name] |
If you need a refresher on those special characters, click the View Table button to look at the chart again.
Access also has a feature called Access Zoom Box
to view your expression if it gets too long.
Creating Query-in Design View - Exercise