Microsoft Office Microsoft Access 2013 Office Access 2013

Microsoft Office Microsoft Access 2013 Office 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 © CCI Learning Solutions Inc. • 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 2

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 © CCI Learning Solutions Inc. • 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 3

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. 4

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. 5

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. 6

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. 7

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. 8

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. 9

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. 10

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. 11

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. 12

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. 13

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. 14

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. 15

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. 16

Microsoft Office Access 2013 Arithmetic Operators Operator Interpreted As Example + Plus [Subtotal] + [Sales. Tax] - Minus [Price] – [Discount] * Multiplied by [Unit. Price] * [Quantity] / Divided by [Total. Cost] / [Quantity] Integer division [Stock. Qty] 12 Modulus [Stock. Qty] Mod 12 ^ Power [Quantity] ^ 2 © CCI Learning Solutions Inc. 17

Microsoft Office Access 2013 Comparison Operators © CCI Learning Solutions Inc. Operator Interpreted As Example = Equal to =“NY” <> Not equal to <>”NY” > Greater than >32. 95 < Less than <32. 95 >= Greater than or equal to >=100 <= Less than or equal to <=100 18

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: [First. Name] & “ “ & [Last. Name] © CCI Learning Solutions Inc. 19

Microsoft Office Access 2013 Logical Operators Operator Interpreted As Example And Combines two criteria Last. Name = “Anderson” AND State = “NY” Or Evaluate each Last. Name = “Anderson” OR criterion separately State = “NY” Not Reverses an expression’s value © CCI Learning Solutions Inc. Last. Name = “Smith” AND NOT State = “NY” 20

Microsoft Office Access 2013 Special Operators Operator Interpreted As Example Between two values Between 12 and 36 In In a set or list of values In (“IL”, ”NY”, ”WA”) Is Null Field contains a null value Is Null Is Not Null Field does not contain a null value Is Not Null Like Used with wildcards to describe a Like “? ? ot” pattern for which Access searches © CCI Learning Solutions Inc. 21

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. 22

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. 23

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. 24

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. 25

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. 26

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. 27

Microsoft Office Access 2013 Using the Expression Builder • The Expression Builder helps you create syntactically accurate expressions • Drill down through the components and doubleclick to paste accurate and complete identifiers into your expressions © CCI Learning Solutions Inc. 28

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. 29

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. 30

Microsoft Office Access 2013 Aggregate Functions Group By Defines the groups for which you want to perform calculations St. Dev Returns the standard deviation of the values in a field Sum Returns the total of the values in a field Var Returns the variance of the values in a field Avg Returns the average of the values in a field First Returns the value from the first record in a table or query Min Returns the smallest value in a field Last Max Returns the largest value in a field Returns the value from the last record in a table or query Count Returns the number of values in a field (not including null records) Expression Creates a calculated field that includes an aggregate function Where Specifies criteria for a field you are not using to define groupings © CCI Learning Solutions Inc. 31

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. 32

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. 33

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. 34

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 Auto. Number fields Fields that are primary keys © CCI Learning Solutions Inc. 35

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. 36

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 © CCI Learning Solutions Inc. • 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 37

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. 38

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. 39

Microsoft Office Access 2013 Lesson Summary • 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 © CCI Learning Solutions Inc. • 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 40

Microsoft Office Access 2013 Lesson Summary • Group and summarize data • Create update queries • Create make-table queries • Create append queries • Create delete queries • Create crosstab queries © CCI Learning Solutions Inc. • 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 41

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. © CCI Learning Solutions Inc. 42

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. © CCI Learning Solutions Inc. 43

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. d. run other queries stored in the same database. © CCI Learning Solutions Inc. 44