Exploring Microsoft Office Access 2007 Chapter 3 Customize

Exploring Microsoft Office Access 2007 Chapter 3: Customize, Analyze, and Summarize Query Data Robert Grauer, Keith Mulbery, Maurie Wigman Lockley Committed to Shaping the Next Generation of IT Experts. 1 Copyright © 2008 Pearson Prentice Hall. All rights reserved.

Objectives n n n Understand the order of precedence Create a calculated field in a query Create expressions with the Expression Builder Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2

Objectives (continued) n n n Create and edit Access functions Perform date arithmetic Create and work with data aggregates Copyright © 2008 Pearson Prentice Hall. All rights reserved. 3

Order of Precedence n n Rules for establishing the sequence by which values are calculated in an expression Failure to follow results in faulty calculations Copyright © 2008 Pearson Prentice Hall. All rights reserved. 4

Order of Precedence Order of precedence from top to bottom Use this symbol n Parenthesis () n Exponentiation ^ n Multiplication and division *, / n Addition and subtraction +, - Copyright © 2008 Pearson Prentice Hall. All rights reserved. 5

Understand Expressions n Expressions are formulas based on existing fields q q n Result in a new field called a calculated field Used primarily in queries, reports and forms Expressions may include q q q The names of fields, controls or properties Operators like +, *, or () Functions, constants or values Copyright © 2008 Pearson Prentice Hall. All rights reserved. 6

What Are Expressions Used For? n You can use an expression to: q q q perform a calculation retrieve the value of a field or control supply criteria to a query create calculated controls and fields define a grouping level for a report Result of a calculation in a report formed by using an expression Copyright © 2008 Pearson Prentice Hall. All rights reserved. 7

Parts of an Expression Identifiers Value [Price] * [Quantity_On_Hand] * 0. 7 Operator n n Constants: a named item whose value remains constant while Access is running Values: literal values such as the number 1, 75 or the word “Hello” Copyright © 2008 Pearson Prentice Hall. All rights reserved. 8

Creating a Calculated Field Expression using existing fields in a database Total Value: [Price] * [Quantity_On_Hand] Descriptive name for new field preceded with colon (: ) n Use correct syntax q q Assign a descriptive name to the field Enclose field names in brackets Copyright © 2008 Pearson Prentice Hall. All rights reserved. 9

Calculated Field in a Query Design View Calculated Field n A calculated field is added to a blank column in the design grid Copyright © 2008 Pearson Prentice Hall. All rights reserved. 10

Saving a Query with a Calculated Field n Does not change the data in the database Only the query structure is saved q Allows new data to be added to a table that is associated with a query q n When query executed again, includes the values of the new table data Copyright © 2008 Pearson Prentice Hall. All rights reserved. 11

Expression Builder n Used to formulate the expressions in a calculated field Copyright © 2008 Pearson Prentice Hall. All rights reserved. 12

Expression Builder Expand folders by clicking plus sign n Fields available in current query Use expressions to add, subtract, multiply, and divide the values in two or more fields/ controls Copyright © 2008 Pearson Prentice Hall. All rights reserved. 13

The Expression Builder Work Area Work area with expression Logical and operand symbols n n n All expressions begin with an equal sign Logic and operand symbols may be either typed or clicked in the area underneath the work area Double-click fields to add them to the work area Copyright © 2008 Pearson Prentice Hall. All rights reserved. 14

Access Functions Payment function in work area n Function categories Predesigned formulas that calculate commonly used expressions Copyright © 2008 Pearson Prentice Hall. All rights reserved. 15

Access Functions n Some functions require arguments q q A value that provides input to the function Separate multiple arguments with commas Arguments separated by commas Copyright © 2008 Pearson Prentice Hall. All rights reserved. 16

The IIF Function The IIF function syntax in the work area of the expression builder n n n Evaluates a condition Executes one action when the expression Alternate action when the condition is false Copyright © 2008 Pearson Prentice Hall. All rights reserved. 17

Example of IIF Function =IIF(Quantity_on_Hand] >= 1, “In Stock”, “Out of Stock”) Arguments of IIF function separated by commas n n Displays the message “In Stock” if value of 1 or greater Otherwise “Out of Stock” will be displayed Copyright © 2008 Pearson Prentice Hall. All rights reserved. 18

Performing Date Arithmetic Query results from date calculation Calculated field using dates n Access stores dates as serial numbers which allows calculation of dates no matter the format entered Copyright © 2008 Pearson Prentice Hall. All rights reserved. 19

Variations of Date. Part Function n =Date. Part(“q”, “ 01/22/2007”) q n =Date. Part(“h”, Now()) q n Displays the quarter in which the given date falls Displays the hour part of the current date =Date. Part(“d”, Now()) q Displays the day part of the current date Copyright © 2008 Pearson Prentice Hall. All rights reserved. 20

Variations of the Date. Diff Function n =Date. Diff(“d”, [orderdate], [shippeddate]) q n =Date. Diff(“m”, #01/06/2006#, #07/23/2007#) q n Displays the number f days between ordering and shipping Displays the number of months between the two dates =Date. Diff(“d”, [dateborn], Now()) q Displays the number of days between the dateborn field and the current date Copyright © 2008 Pearson Prentice Hall. All rights reserved. 21

Data Aggregates Data aggregation allows summarization and consolidation of data Copyright © 2008 Pearson Prentice Hall. All rights reserved. 22

Performing an Aggregate Calculation in Datasheet View Aggregate functions displayed by clicking the arrow next to Total n n n Must be in a a numeric or currency field Click Totals in the Records group Choose the aggregate function desired Copyright © 2008 Pearson Prentice Hall. All rights reserved. 23

How do Aggregate Functions Handle Null Values? n n When using the Avg function, null fields ignored When using the Count function, null fields are not included unless an asterisk (*) is used as the argument for the function Copyright © 2008 Pearson Prentice Hall. All rights reserved. 24

How do Aggregate Functions Handle Null Values? (continued) n Examples q q n Count(*) will include null fields in the calculation Count(records) will not include null fields The Sum function ignores null values Copyright © 2008 Pearson Prentice Hall. All rights reserved. 25

Add a Total Row in a Query n The total row can be added to the design grid by clicking the Totals Icon Total row added to the query Copyright © 2008 Pearson Prentice Hall. All rights reserved. 26

Use a Totals Query to Grouping field Field to be totaled n n Organizes query results into groups Only use the field or fields that you want to total and the grouping field Copyright © 2008 Pearson Prentice Hall. All rights reserved. 27

Copyright © 2008 Pearson Prentice Hall. All rights reserved. 28