Chapter 3 - Customize, Analyze and Summarize Query Data

Question 1

Objective - Understand the order of operations.

Answer 1

The order of operations determines the sequence by which operations are calculated in an expression. Evaluate expressions in parentheses first, then exponents, then multiplication and division, and, finally, addition and subtraction. Operations of equal value will be calculated from left to right. A solid understanding of these rules will enable you to easily create calculated fields in Access.

Question 2

Objective - Create a calculated field in a query.

Answer 2

When creating a query, you may need to create a calculation based on the fields from one or more tables. Add a calculated field in the Design view of a query to the first blank column or by inserting a blank column where needed. A formula used to calculate new fields from the values in existing fields is known as an expression. An expression can consist of a number of fields, operators (such as * , / , + , or −), functions (such as IIf), and constants (numbers). When creating a calculated field, you must follow proper syntax—the set of rules that Access follows when evaluating an expression.

Question 3

Objective - Create expressions with the Expression Builder.

Answer 3

Launch the Expression Builder while in the query design grid to assist you with creating a calculated field (or other expression). The Expression Builder helps you create expressions by supplying you with the fields, operators, and functions you need to create them. When you use the Expression Builder to help you create expressions, you can eliminate spelling errors in field names. Another advantage is with functions; functions require specific arguments in a specific order. When you insert a function using the Expression Builder, the builder gives you placeholders that tell you where each argument belongs.

Question 4

Objective - Use built-in functions in Access.

Answer 4

A function produces a result based on variable inputs known as arguments. Once you identify what you need a function to do, you can open the Built-In Functions folder in the Expression Builder to see if the function exists. If it does, add the function to the expression box and supply the required arguments. Functions work the same in Access and Excel.

Question 5

Objective - Perform date arithmetic

Answer 5

All dates and times in Access are stored as the number of days that have elapsed since December 31, 1899. Working with dates in Access can be challenging, especially when performing date arithmetic. Fortunately, Access has some built-in functions to help work with dates and date arithmetic. Sample functions include DateDiff (), DateAdd(), Date(), and Now(). These functions help perform arithmetic on date fields.

Question 6

Objective - Add aggregate functions to datasheets and queries

Answer 6

Aggregate functions perform calculations on an entire column of data and return a single value. Aggregate functions—such as Sum, Average, and Minimum—are used when you need to evaluate a group of records rather than the individual records in a table or query. Access refers to aggregate functions as Totals. In the Datasheet view of a query or table, click Totals to add a Total row to the bottom of the datasheet. When you create a query in Design view, click Totals to show the Total row.

Question 7

Aggregate Function

Answer 7

A function that performs calculations on an entire column of data and returns a single value.

Question 8

Argument

Answer 8

A variable or constant input, such as a cell reference or value, needed to complete a function. The entire group of arguments for a function is enclosed within parentheses.

Question 9

Constant

Answer 9

An unchanging value, such as a birth date.

Question 10

Date Arithmetic

Answer 10

The process of manipulating dates, or adding or subtracting a constant from a date.

Question 11

Date formatting

Answer 11

Formatting that affects a date’s display without changing the actual underlying value in the table.

Question 12

DatePart Function

Answer 12

Enables you to isolate a specific part of a date, such as the year.

Question 13

Expression

Answer 13

A formula used to calculate new fields from the values in existing fields.

Question 14

Expression Builder

Answer 14

A tool in Access to help you create expressions.

Question 15

Function

Answer 15

A predefined computation that simplifies creating a complex calculation and produces a result based on inputs known as arguments.

Question 16

IIf function

Answer 16

A predefined logical formula that evaluates a condition and returns one value if the condition is true and a different value if the condition is false.

Question 17

Order of Operations

Answer 17

Determines the sequence by which operations are calculated in an expression.

Question 18

Pmt Function

Answer 18

A predefined formula that calculates the periodic payment for a loan with a fixed interest rate and fixed term.

Question 19

Syntax

Answer 19

The rules that dictate the structure and components required to perform the necessary calculations in an equation or evaluate expressions.

Question 20

Total row

Answer 20

A table row that appears below the last row of records in an Excel table, or in Datasheet view of a table or query, and displays summary or aggregate statistics.

Question 21

Totals Query

Answer 21

A query that contains an additional row in the design grid and is used to display only aggregate data when the query is run.