Transcript Chapter 7
Chapter 7 Query-by-Example Transparencies © Pearson Education Limited 1995, 2005 Chapter 7 - Objectives The main features of Query-By-Example (QBE). The types of queries provided by the Microsoft Access DBMS QBE facility. How to use QBE to build queries to select fields and records. How to use QBE to target single or multiple tables. 2 © Pearson Education Limited 1995, 2005 Chapter 7 - Objectives How to perform calculations using QBE. How to use advanced QBE facilities including parameter, find matched, find unmatched, crosstab, and autolookup queries. How to use QBE action queries to change content of tables. 3 © Pearson Education Limited 1995, 2005 Query-by-Example (QBE) Visual approach for accessing information in a database through use of query templates. Example values are entered into template to represent what access to database is to achieve, such as the answer to a query. Originally developed by IBM in 1970s and has proved so popular that QBE (or similar) is now provided by most DBMSs. When user constructs a QBE - in background, DBMS creates an equivalent SQL statement. 4 © Pearson Education Limited 1995, 2005 Query-by-Example (QBE) Allows user to: - Ask questions about data in one or more tables. - Specify the fields we want in the answer. - Select records according to some criteria. - Perform calculations on the data in tables. - Insert and delete records. - Modify values of fields. - Create new fields and tables. 5 © Pearson Education Limited 1995, 2005 Introduction to Microsoft Access 6 © Pearson Education Limited 1995, 2005 Summary of Microsoft Access Query Types 7 © Pearson Education Limited 1995, 2005 Introduction to Microsoft Access Queries 8 © Pearson Education Limited 1995, 2005 Building Select Queries using QBE Specifying Criteria 9 © Pearson Education Limited 1995, 2005 Building Select Queries using QBE Specifying Criteria 10 © Pearson Education Limited 1995, 2005 Building Select Queries using QBE Specifying Criteria 11 © Pearson Education Limited 1995, 2005 Creating Multi-table Queries 12 © Pearson Education Limited 1995, 2005 Calculating Totals 13 © Pearson Education Limited 1995, 2005 Calculating Totals 14 © Pearson Education Limited 1995, 2005 Using Advanced Queries - Parameter Query 15 © Pearson Education Limited 1995, 2005 Using Advanced Queries - Crosstab Query 16 © Pearson Education Limited 1995, 2005 Using Advanced Queries - Crosstab Query 17 © Pearson Education Limited 1995, 2005 Using Advanced Queries - Find Matched Query 18 © Pearson Education Limited 1995, 2005 Using Advanced Queries - Find Unmatched Query 19 © Pearson Education Limited 1995, 2005 Using Advanced Queries - Autolookup Query 20 © Pearson Education Limited 1995, 2005 Changing Content of Tables - Make-Table Action Query 21 © Pearson Education Limited 1995, 2005 Changing Content of Tables - Delete Action Query 22 © Pearson Education Limited 1995, 2005 Changing Content of Tables - Update Action Query 23 © Pearson Education Limited 1995, 2005 Changing Content of Tables - Append Action Query 24 © Pearson Education Limited 1995, 2005 Changing Content of Tables - Append Action Query 25 © Pearson Education Limited 1995, 2005