Skip to main content

Access Chapter 3

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.
  • Access: Queries II Participation Project
  • Access: Queries III Participation Project
  • MyLab Lesson D
  • Homework #3
  • Homework #4
  • Homework #5
  • MyLab Lesson D
Specify aliases for field names.
  • Access: Queries II Participation Project
  • Access: Queries III Participation Project
  • MyLab Lesson D
  • Homework #3
  • Homework #4
  • Homework #5
  • MyLab Lesson D
Format the appearance of query fields.
  • Access: Queries II Participation Project
  • MyLab Lesson D
  • Homework #3
  • Homework #4
  • Homework #5
  • MyLab Lesson D
Use the Expression Builder tool to edit formulas.
  • MyLab Lesson D
  • MyLab Lesson E
  • MyLab Lesson D
  • MyLab Lesson E
Use aggregate functions such as SUM, COUNT, and AVERAGE to gather statistics.
  • Access: Queries III Participation Project
  • MyLab Lesson D
  • Homework #3
  • Homework #4
  • Homework #5
  • MyLab Lesson D

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

  1. 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.
  2. 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/.