Lesson 3 Summary Slides

Download Report

Transcript Lesson 3 Summary Slides

Access 2013

Access 2013

Courseware # 3255

Lesson 3: Retrieving the Data You Want

Microsoft Office

Access 2013

Lesson Objectives

• • • • • • Add, move and rearrange query fields Hide and show query fields Format query fields Display query totals Add criteria to a query Using comparison operators in expressions • • • • • • Use the Find and Replace feature Use wildcards in expressions Sort records Filter records by selection and form Create a query in Query Design view Create a query using the Query Wizard © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Lesson Objectives

• • • • • • Group and summarize data Create update queries Create make-table queries Create append queries Create delete queries Create crosstab queries • • • • • • Use special operators in expressions Use the Expression Builder Work with joined tables Add totals and calculated fields Create parameter queries Add conditional logic to queries © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Retrieving Data

• • Access provides several methods for finding exactly the data you want.

These methods include: – – – – Using the Find features Sorting records Filtering tables Using queries © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Finding Records

• • • You can use the Find feature to locate records that contain a specific value in a particular field The Find feature searches the datasheet and moves the record selector to the first record that matches the value you specify Click the Find command button to open the Find and Replace dialog box © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Finding and Replacing Data

• • • You can use the Replace feature to replace values that are found with a new value you specify Type the string or value you want to find in the Find What text box Type the string or value with which you want to replace the found value in the Replace With text box © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Using Wildcards

* ?

# [ ]

Finds any number of characters Finds a single alphabetic or numeric character Finds a single numeric character Finds any character(s) inside the brackets Specifies a logical NOT when use within the brackets Finds any one of the range of characters inside the brackets © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Sorting Records

• • • • • By default, records display in ascending order by the primary key field if one is defined To sort a datasheet: – Click in any record in the column you want to use for the sort order – Click either the Ascending or Descending button To remove a sort, click the Remove Sort button in the Ribbon When you sort by more than one column, the first column selected is the secondary sort key; the last column selected is the primary sort key Sorting merely rearranges the data in a datasheet © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Filtering Records

• • • • Filtering enables you to isolate specific records Filters use a set of conditions called criteria to test records and determine whether or not they should be displayed When a filter is applied, only a subset of the data displays Filtering affects only which records are displayed in the datasheet; it does not affect the underlying data © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Filter by Selection

• • Filtering by selection filters records that contain or do not contain identical data in a specific field To filter by selection, place the cursor in the column that you want to filter on, click Selection and select an appropriate option © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Filter by Form

• • Filter by Form allows you to specify multiple criteria To filter by form, click the Advanced button and select Filter by Form to open a blank Filter by Form window © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating Queries

• • • • • A query asks a question of a table A select query is a specialized instruction which selects and displays specific information from a table The resulting set of records is called a result set or a dynaset To create a query: – – – Specify the table(s) from which the query will select data Specify which table fields will be included Specify how the result set will be organized for display A select query is stored as a database object © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating a Select Query in Query Design View

• • • Query Design view includes a field list and a design grid Use the Show Table dialog box to add tables to the field list Drag fields from the field list into the design grid © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating a Select Query with the Query Wizard

• Although you do not work directly with the design grid when you use the Query Wizard, you still provide the same basic information: – – – Which tables to use Which fields to include in the result set How the data in the result set will display © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Modifying Queries

• • • • To add or delete fields, drag them onto or off of the design grid To rearrange columns, drag a column to a new position in the design grid Use the Sort row in the design grid to sort the records in the result set To hide a field in a query, clear the Show check box in the design grid © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Adding Query Criteria

• • Criteria that you specify for a query becomes part of the query design To add criteria, click in the Criteria row for a field and enter an expression © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Arithmetic Operators

* / Operator Interpreted As Example + -

Plus Minus [Subtotal] + [SalesTax] [Price] – [Discount] Multiplied by Divided by [UnitPrice] * [Quantity] [TotalCost] / [Quantity]

\ Mod ^

Integer division [StockQty] \ 12 Modulus [StockQty] Mod 12 Power [Quantity] ^ 2 © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Comparison Operators

Operator Interpreted As = <>

Equal to Not equal to

> < >= <=

Greater than Less than Less than or equal to

Example

=“NY” <>”NY” >32.95

<32.95

Greater than or equal to >=100 <=100 © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Concatenation Operators

• • • Used to join strings of text together to form one text string The most commonly used concatenation operator is the ampersand (&) symbol Ex: [FirstName] & “ “ & [LastName] © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Logical Operators

Operator Interpreted As And Or

Combines two criteria Evaluate each criterion separately

Example

LastName = “Anderson” AND State = “NY” LastName = “Anderson” OR State = “NY”

Not

Reverses an expression’s value LastName = “Smith” AND NOT State = “NY” © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Special Operators

Operator Interpreted As

Between Between two values

In a set or list of values

Is Null

Field contains a null value

Is Not Null Like Example

Between 12 and 36 In (“IL”,”NY”,”WA”) Is Null Field does not contain a null value Used with wildcards to describe a pattern for which Access searches Is Not Null Like “??ot” © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Adding a Logical OR to a Query

• • • When you use a logical OR, you specify two or more criteria Type an “OR” between criteria expressions if the values are located in the same field Use the Or row in the design grid to evaluate separate fields © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Adding a Logical AND to a Query

• • • • When you use a logical AND, you specify two or more criteria Both criteria must be satisfied for a record to be selected Type an “AND” between criteria expressions if the values are located in the same field List multiple criteria for multiple fields on the same row in the design grid © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Working with Joined Tables

• • • You can add fields from multiple tables to a query Access automatically joins the tables in the design grid if you created relationships between the tables in the Relationships window If you did not explicitly create relationships, Access creates join lines if: – – Each table has a field with the same name and data type One of the join fields is a primary key © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Performing Calculations in Queries

• • • You can use queries to calculate values You can perform calculations in a query using functions (called totals) You can perform calculations in a query by creating calculated fields © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Adding Calculated Fields to a Query

• • • • • Use calculated fields when you want to perform calculations that are not available through the use of functions To create a calculated field, type an expression into an empty field cell in the design grid A calculated field does not become part of the underlying table A calculated field always shows results based on the most current data in the database When you add a calculated field, the first part of the entry displays as the field name for the calculated field © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Formatting Query Fields

• To apply a format to a query field, specify the format in the field’s Property Sheet © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Using the Expression Builder

• • The Expression Builder helps you create syntactically accurate expressions Drill down through the components and double click to paste accurate and complete identifiers into your expressions © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Displaying Totals

• You can add a total row to a query result set just as you can to a datasheet © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Grouping and Summarizing Query Data

• To summarize data, use the Total row in the Query Design grid © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Aggregate Functions

Group By Defines the groups for which you want to perform calculations

Sum

Returns the total of the values in a field

Avg Min

Returns the average of the values in a field Returns the smallest value in a field

Max Count

Returns the largest value in a field Returns the number of values in a field (not including null records)

StDev Var First

Returns the standard deviation of the values in a field Returns the variance of the values in a field Returns the value from the first record in a table or query

Last

Returns the value from the last record in a table or query Expression Creates a calculated field that includes an aggregate function

Where

Microsoft Office

Access 2013

Creating Parameter Queries

• • A parameter query prompts you for input, then passes the value you enter to the query at run time The query uses the value passed to it as criteria © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Using Conditional Logic in a Query

• • • An immediate if (IIF) function tests for a value and then takes an appropriate action based on the results of the test The syntax for an immediate IF function follows: IIF(expr, truepart, falsepart) IIF always evaluates both truepart and falsepart © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating Action Queries

• • • • • Action queries make changes to a database The four types of action queries are: – – – – Update query Make-table query Append query Delete query The results of an action query cannot be reversed Make a backup of the table(s) or the database before executing an action query Create an action query as a select query first to ensure the query selects the records you expect © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating Update Queries

• • • Use an Update query to add, change or delete data in one or more existing records You cannot use an update query to add new records to a table or to delete entire records You cannot use an update query to update data in the following types of fields: – – – – Calculated fields Fields from a totals query or a crosstab query AutoNumber fields Fields that are primary keys © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating Make-Table Queries

• • • A make-table query creates a new table from all or part of the data in a table Create a select query, add the desired tables, fields and criteria, test the query, then change the query type to Make Table to open the Make Table dialog box Use the Make Table dialog box to specify a name for the table you want the query to create – You can specify to create the new table in the current database or in another database © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating Append Queries

• • • • • The table to which you want to append records must already exist The field names in both tables must match exactly The data types of the fields must be compatible The query must populate any target fields that cannot be null The query cannot violate primary key rules in the target table • • • An append query copies data from one table and pastes the data into another table Create a select query, add the desired tables, fields and criteria, test the query, then change the query type to Append to open the Append dialog box Use the Append dialog box to specify the table to which you want to append records © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating Delete Queries

• • • Use a Delete query to remove entire records from a table Begin by creating a select query and ensuring it correctly selects the records you want to delete Convert the query to a delete query and run the delete query to remove the records © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Creating Crosstab Queries

• • • A crosstab query summarizes data – The summarized values display in a tabular format – The crosstab query makes it easy to read and compare data Data listed down the side and across the top comes from tables or queries in the database Summary fields display at the intersection of rows and columns – The values are calculated using aggregate functions © CCI Learning Solutions Inc.

Microsoft Office

Access 2013

Lesson Summary

Microsoft Office

Access 2013

Lesson Summary

Microsoft Office

Access 2013

Review Questions

1. Which statement accurately compares filtering to sorting?

a. Filtering removes records from tables, whereas sorting simply hides records.

b. Filtering hides records in a table, whereas sorting changes the physical order of the records.

c. Filtering isolates a subset of data, whereas sorting simply changes the display order.

d. Filtering and sorting both change the physical order of the records.

Microsoft Office

Access 2013

Review Questions

2. Amanda wants to add another table to her query. Which of the following buttons should she click on the Ribbon?

a. Make Table b. View c. Show Table d. Update 3. Phil is having difficulty achieving the sort order he wants for a query.

He wants to sort in ascending order by state, and then in descending order by city. Which of the following is most likely the problem?

a. The query is read-only and cannot be modified.

b. The primary key for the table is a number field, making it impossible to sort on a text field.

c. The Format property for the State field is set to Non-indexed.

d. The City field is further left than the State field in the design grid.

Microsoft Office

Access 2013

Review Questions

4. What function does the Zoom box serve?

a. You can use it to build accurate expressions.

b. You can use it to display expressions and make them easier to read.

c. You can use it to optimize a query and make it run faster.

d. You can use it to quickly add the fields in a field list to the design grid.

5. The results of an action query: a. cannot be reversed.

b. are reversible.

c. summarize data.