Microsoft Office 2016 In Practice Chapter 3 Creating

Microsoft Office 2016: In Practice Chapter 3 Creating and Using Queries 10/3/2020 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 1

Student Learning Outcomes Microsoft Office 2016: In Practice Understanding Queries and Using the Simple Query Wizard Creating, Running, and Editing a Query in Design View Adding a Criterion to a Query Controlling the Display of Query Results Defining Multiple Criteria in a Query Creating and Using a Simple Parameter Query Adding Calculated Fields to a Query Creating and Using a Summary Query © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Case Study Microsoft Office 2016: In Practice § Placer Hills Real Estate Queries © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Understanding Queries and Using the Simple Query Wizard SLO 3. 1 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 4

Microsoft Office 2016: In Practice Queries § Find data in your database by allowing you to ask a question § Create using Design view or Query Wizard For example, show only students who are taking a course in the Biology Department © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Creating, Running, and Editing a Query in Design View SLO 3. 2 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 6

Working with Queries Microsoft Office 2016: In Practice Create a Query Based on a single table Add Fields to a Query Edit fields in query design Run a Query Save a Query Open a saved query Based on multiple tables © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Adding Criterion to a Query SLO 3. 3 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 8

Microsoft Office 2016: In Practice Criterion § Add criterion § Use comparison operators § Use wildcards in criteria Use expressions to write criterion! © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Comparison Operators Operator < <= > >= = <> Between In Like Explanation Less than or equal to Greater than or equal to Equal to Not equal to Between these two values Contained in this list of values Similar to this value Example <5 <=100 >#1/1/2014# >=”B 24” =”CA” <>0 Between 10 and 20 In(1, 5, 10) Like “C? ? ” © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Wildcards ? * # • Match any single character in a specific position • Match any number of characters • Match any single numeric digit © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Controlling the Display of Query Results SLO 3. 4 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 12

Query Options Microsoft Office 2016: In Practice Unique Values Property Show Option Sort © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Defining Multiple Criteria in a Query SLO 3. 5 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 14

Microsoft Office 2016: In Practice Compound Criteria AND OR § AND: Requires that a record meet all of the individual conditions specified in order to be included in the results § OR: Requires a record meet at least one of the conditions to be included in the results © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Creating and Using a Simple Parameter Query SLO 3. 6 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 16

Microsoft Office 2016: In Practice Parameter Query § Asks to enter the specific criteria value when you run the query § Follow rules § Enter a clear, specific phrase § Enter in criteria row cell for the desired field § Enclose parameter in square brackets § Include operators as necessary before the parameter © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Parameter Query Options § Data type § Wildcards © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Adding Calculated Fields to a Query SLO 3. 7 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 19

Microsoft Office 2016: In Practice Calculated Fields § A field that gets its value from a calculation instead of a value that is entered into the field § Can reference other fields in the database § Not stored in the database § Use order of operations © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Order of Operations ( ) ^ * / - + © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Formulas § Rules formulas § Troubleshoot calculated fields § Functions in a calculated field © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Creating and Using a Summary Query SLO 3. 8 © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. 23

Microsoft Office 2016: In Practice Summary Query § Build the same as select queries § Use the total row that is added to the Design view of the query § Include only the fields you want to group by and the fields you want to aggregate § Use multiple aggregate functions on a field and use criteria as necessary © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Microsoft Office 2016: In Practice Aggregate Functions © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Chapter Summary SLO Summary Microsoft Office 2016: In Practice 3. 1 3. 2 3. 3 3. 4 3. 5 3. 6 3. 7 3. 8 Understand queries and use the Simple Query Wizard to create and run a query Create a query in Design view, add fields, and save and run a query Add criterion into a query, use comparison operators, and create criteria with wildcards Integrate sorting and limiting fields and records that display Use the AND and OR operators to include multiple criteria in a query Create and use a parameter query Build a query that uses a calculated field Create and use a summary query using aggregate functions © 2017 by Mc. Graw-Hill Education. This proprietary material is solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.