Chapter Demonstrable Skills
After completing the Access Chapter 3 portion of the Access unit, students will be able to:
| Demonstrable Skill | Learning Activities & Informal Assessments |
Formal Assessments (excluding Exams) |
|---|---|---|
| Create calculated fields to generate new values based on existing data. |
|
|
| Specify aliases for field names. |
|
|
| Format the appearance of query fields. |
|
|
| Use the Expression Builder tool to edit formulas. |
|
|
| Use aggregate functions such as SUM, COUNT, and AVERAGE to gather statistics. |
|
|
Chapter Notes
Calculated fields and Expression Builder
Access allows you to create fields that perform calculations. You can enter them manually in the first row or use the Expression Builder [1]. An expression can be created by using field names, constants, and functions. In an expression, the field name must be spelled correctly; otherwise, Access displays an error.
The left column of the Expression Builder dialog box contains Expression Elements, which include the built-in functions, the tables and other objects from the current database, and common expressions. The middle column displays the Expression Categories based on the item selected in the Expression Elements box. The right column displays the Expression Values, if any, for the categories that you selected in the Expression Categories box.
Aggregate fields (Totals row)
Aggregate fields helps users summarize data [1].
There are two methods of adding aggregate functions to a query:
- Adding a Total row, which displays the results of the aggregate function as the last row in the Datasheet view of a table or query, and creating a totals query in Query Design view.
- Adding a Total row to the Datasheet view is quick and easy. It has the advantage of showing the total information while still showing the individual records. Total row values cannot be modified; you can change the aggregate function, but you cannot overwrite the numbers.
Tips & Tricks
Aggregates fields (Totals row)
If you don't see a formula but the query requires a calculation, it's probably a totals query [2].
For example, the prompt, "Create a new query. For each full college name, find the average of the number of in-state FTEs, the average of the number of out-of-state FTEs, and the average of its appropriations." asks you to create a query where you find the average of a number of fields. Since no calculation is provided, you can look to totals. Turn the totals row on, add the fields mentioned in the query and click on the function below each row and pick the function mentioned.
References
- A. Poatsy, K. Mulberry, C. Krebs, L. Hogan, A. Rutledge, and E. Cameron, Exploring Microsoft Office 2013, 1st ed., vol. 1. Upper Saddle River, NJ: Pearson Education, 2013.
- R. G. Phipps and B. M. Powell, “Access Chapter 3,” Computer Science 101, May 18, 2016. Available: http://cs101.wvu.edu/materials/units/access/access-chapter-3/.