Queries can create calculated values. For example, you could multiply two fields together, such as Total Credit Hours and Tuition per Credit Hour and get a Total Tuition Due amount for each student. In this manner, you calculate the total amount of tuition due without having to include a specific field for this amount in the table, which reduces the size of the database and provides more flexibility.
There are two steps to produce a calculated field in a query. First, name the field that will store the calculated values. Second, write the expressionthe formulathat will perform the calculation. Each field name used in the calculation must be enclosed within its own pair of square brackets, [ ].
For each pledge received by the clubs, the Lake Michigan City College Foundation has agreed to donate an amount equal to 50 percent of each pledge. In this activity, you will create a calculated field to determine the additional amount each pledge is worth.
1. | On the Objects bar, be sure Queries is selected, and then double-click Create query in Design view. Add the Pledges table and expand the table area and the field list.
|
2. | From the Pledges field list, add the following fields to the design grid: Donor Last Name, Donor First Name, Pledge Amount, and Date Collected.
|
3. | Click in the Sort row under Donor Last Name, click the Sort arrow, and then click Ascending. In the Criteria row, under Date Collected, type Is Not Null and then press .
This will limit the records to only those donations that have been collected. Is Not Null excludes empty fields.
|
4. | In the Field row, right-click in the first empty column to display a shortcut menu, and then click Zoom.
The Zoom dialog box that displays gives you working space so that you can see the calculation as you type it. The calculation can also be typed directly in the empty Field box in the column.
|
| |
5. | In the Zoom dialog box, type Matching Donation: [Pledge Amount]*0.5 and then compare your screen with Figure 2.42.
Figure 2.42.
(This item is displayed on page 1014 in the print version)
The first element, Matching Donation, is the new field name where the calculated amounts will display. Following that is a colon (:). A colon in a calculated field separates the new field name from the expression. Pledge Amount is in square brackets because it is an existing field name from the Pledges table. It contains the information on which the calculation will be performed. Following the square brackets is an asterisk (*), which in math calculations signifies multiplication. Finally, the percentage (50% or 0.5) is indicated.
|
|
[Page 1014] |
6. | In the Zoom dialog box, click OK, and then Run the query. Compare your screen with Figure 2.43.
Figure 2.43.
(This item is displayed on page 1015 in the print version)
The query results display the three fields from the Pledges table plus a fourth fieldMatching Donationin which a calculated amount displays. Each calculated amount equals the amount in the Pledge Amount field multiplied by 0.5.
Alert!: Does Your Screen Differ?
If your calculations in a query do not work, carefully check the expression you typed. Spelling or syntax errors will prevent calculated fields from working properly.
|
|
[Page 1015] |
7. | Notice the formatting of the Matching Donation field. There are no dollar signs, commas, or decimal places; you will adjust this formatting later. Switch to Design view . In the Field row, in the first empty column, right-click, and then click Zoom.
|
8. | In the Zoom dialog box, type Total Donation: [Pledge Amount]+[Matching Donation] and then click OK. Run the query to view the results.
Total Donation is calculated by adding together the Pledge Amount field and the Matching Donation field. This time the Total Donation column includes dollar signs, commas, and decimal points.
|
| |
9. | Switch to Design view . In the Field row, right-click the Matching Donation field, and then click Properties. Alternatively, click the Matching Donation field, and then on the Query Design toolbar, click the Properties button.
The Field Properties dialog box displays. Here you can customize fields in a query, for example, the format of numbers in the field. As you progress in your study of Access, you will learn more about the Field Properties dialog box.
[Page 1016]Alert!: Does the Query Properties Dialog Box Display?
To display the Field Properties dialog box, you must first click the specified field, otherwise, the Query Properties dialog box might display. If this occurs, in the Field row, click the Matching Donation field to change the displayed Query Properties dialog box to the Field Properties dialog box for this field. By clicking in the specified field, the focus of the dialog box changes to the active field.
|
10. | In the Field Properties dialog box, to the right of Format, click in the white text box and then click the arrow that displays. Compare your screen with Figure 2.44.
Figure 2.44.
A list of possible formats for this field displays.
|
11. | In the list of formats, click Currency. Then on the title bar of the Field Properties dialog box, click the Close button .
|
| |
12. | Run the query to view the results. Then, select all the columns, display the Format menu, click Column Width, and then click Best Fit. Click in any record to cancel the selection. Compare your screen with Figure 2.45.
Figure 2.45.
(This item is displayed on page 1017 in the print version)
The Matching Donation column displays with currency formattinga dollar sign, thousands comma separators, and two decimal places.
|
|
[Page 1017] |
13. | From the File menu, display the Save As dialog box and using your own name, type 2A Total Donations Firstname Lastname as the query name. Click OK.
|
14. | If you have been instructed to submit your file electronically, close the query. Otherwise, display the Page Setup dialog box, and change the orientation to landscape. Print the query, and then Close it.
|