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.
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.
From the Pledges field list, add the following fields to the design grid: Donor Last Name, Donor First Name, Pledge Amount, and Date Collected.
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 .
In the Field row, right-click in the first empty column to display a shortcut menu, and then click Zoom.
In the Zoom dialog box, type Matching Donation: [Pledge Amount]*0.5 and then compare your screen with Figure 2.42.
In the Zoom dialog box, click OK, and then Run the query. Compare your screen with Figure 2.43.
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.
In the Zoom dialog box, type Total Donation: [Pledge Amount]+[Matching Donation] and then click OK. Run the query to view the results.
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.
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.
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.
A list of possible formats for this field displays.
In the list of formats, click Currency. Then on the title bar of the Field Properties dialog box, click the Close button .
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.
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.
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.
GO! with Microsoft Office 2003 Brief (2nd Edition)
Practical Intrusion Analysis: Prevention and Detection for the Twenty-First Century: Prevention and Detection for the Twenty-First Century