1 / 35

Access 2010 Level 1 Unit 1 Creating Tables and Queries Chapter 3 Performing Queries

Access 2010 Level 1 Unit 1 Creating Tables and Queries Chapter 3 Performing Queries. Performing Queries. Quick Links to Presentation Contents. Perform Queries CHECKPOINT 1 Design Queries with Aggregate Functions Create a Crosstab Query Create a Find Duplicates Query

tiffany-leda
Télécharger la présentation

Access 2010 Level 1 Unit 1 Creating Tables and Queries Chapter 3 Performing Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Access 2010 Level 1 Unit 1 Creating Tables and Queries Chapter 3 Performing Queries

  2. Performing Queries Quick Links to Presentation Contents • Perform Queries • CHECKPOINT 1 • Design Queries with Aggregate Functions • Create a Crosstab Query • Create a Find Duplicates Query • Create an Unmatched Query • CHECKPOINT 2

  3. Perform Queries • Being able to extract (pull out) specific data from a table is one of the most important functions of a database. • Extracting data in Access is referred to as performing a query. • The word query means to ask a question.

  4. Perform Queries…continued To design and perform a query: • Open the desired database. • Click the Create tab. • Click the Query Design button in the Queries group. continues on next slide… Query Design button

  5. Perform Queries…continued • At the Show Table dialog box, click the desired table. • Click the Add button. • Add any additional tables. continues on next slide… Show Table dialog box

  6. Perform Queries…continued • In the query design grid, click the down-pointing arrow in the Field row field. • Click the desired field from the drop-down list. continues on next slide… Field row field

  7. Perform Queries…continued • Click inside the Criteria row field in the column containing the desired field name. • Type the desired criterion. continues on next slide… Criteria row field

  8. Perform Queries…continued • Click the Run button in the Results group. • Save the query. Run button

  9. Perform Queries…continued • You can click the down-pointing arrow at the right side of a Field row field and then click the desired field at the drop-down list. • You can also double-click a field in a table and it is inserted in the first available Field row field in the query design grid. • A third method for inserting a field in the query design grid is to drag a field from the table to the desired field in the query design grid.

  10. Perform Queries…continued continues on next slide…

  11. Perform Queries…continued

  12. Perform Queries…continued • In addition to performing several queries on specific tables, a query can also be performed on fields from more than one table. multiple criterion

  13. Perform Queries…continued • When designing a query, you can extract records containing a check mark or records that do not contain a check mark. When you press the Enter key, Access changes the 1 to True.

  14. Perform Queries…continued To sort fields in a query: • At the query window, click in the Sort row field in the query design grid. • Click the down arrow in the Sort row field. • Click the desired option. Sort row field

  15. Perform Queries…continued • You can modify a saved query. • Rather than designing a new query, open the existing query, make any needed changes, and then run the query.

  16. Perform Queries…continued To group and display multiple queries in the Navigation pane: • Click the down-pointing arrow in the Navigation pane Menu bar. • Click the Object Type option at the drop-down list. Object Type option

  17. Perform Queries…continued • The query design grid contains an or row that you can use to design a query that instructs Access to display records that match either of the two criteria. or row

  18. Perform Queries…continued • You can also select records by entering criteria statements into more than one Criteria field. Multiple criteria all entered in the same row become an And statement where each criterion must be met for Access to select the record. multiple criteria

  19. Perform Queries…continued To create a query with the Simple Query Wizard: • Click the Create tab. • Click the Query Wizard button in the Queries group. • Make sure that Simple Query Wizard is selected. • Click OK. • Specify the table(s). • Insert the fields. • Click the Next button. continues on next slide… first Simple Query Wizard dialog box

  20. Perform Queries…continued • At the second Simple Query Wizard dialog box, specify whether you want a detail or summary query. • Click the Next button. • At the third Simple Query Wizard dialog box, type a name and make any desired changes. • Click the Finish button. third Simple Query Wizard dialog box

  21. Perform Queries…continued To insert a calculated field: • Click the Field row field. • Type the desired field name followed by a colon. • Type the equation. equation

  22. CHECKPOINT 1 • Being able to do this is one of the most important functions of a database. • react • interact • detract • extract • Click this option to sort from highest to lowest. • Descending • Ascending • Alphabetical • Chronological Answer Answer Next Question Next Question • This is the term used for the results of the query. • answer • results • return • object • To insert a calculated field, type the desired field name followed by this symbol. • plus sign • minus sign • colon • asterisk Answer Answer Next Question Next Slide

  23. Design Queries with Aggregate Functions • You can include an aggregate function such as Sum, Avg, Min, Max, or Count in a query to calculate statistics from numeric field values of all the records in the table. • When an aggregate function is used, Access displays one row in the query results datasheet with the formula result for the function used.

  24. Design Queries with Aggregate Functions…continued To design a query with an aggregate function: • At the query window, click the Totals button in the Show/Hide group. continues on next slide… Totals button

  25. Design Queries with Aggregate Functions…continued • Click the down-pointing arrow in the Total row field. • Click the desired aggregate function. aggregate functions

  26. Design Queries with Aggregate Functions…continued • Using the Group By option in the Total field you can add a field to the query upon which you want Access to group records for statistical calculations. Group By option

  27. Create a Crosstab Query • A crosstab query calculates aggregate functions such as Sum and Avg in which field values are grouped by two fields. • A wizard is included that guides you through the steps to create the query.

  28. Create a Crosstab Query…continued To create a crosstab query: • Click the Create tab. • Click the Query Wizard button in the Queries group. • Double-click the Crosstab Query Wizard option. • Complete the wizard steps. Crosstab Query Wizard option

  29. Create a Find Duplicates Query • Use the find duplicates query to search a specified table or query for duplicate field values within a designated field or fields. • A few other examples of how you can use a find duplicates query include: • Find the records in an Orders table with the same customer number so that you can identify your loyal customers. • Find the records in a Customers table with the same last name and mailing address so that you send only one mailing to a household to save on printing and postage costs. • Find the records in an EmployeeExpenses table with the same employee number so that you can see the employee who is submitting the most claims.

  30. Create a Find Duplicates Query…continued To create a find duplicates query: • Click the Create tab. • Click the Query Wizard button in the Queries group. • Double-click the Find Duplicates Query Wizard option. • Complete the wizard steps. Find Duplicates Query Wizard

  31. Create an Unmatched Query • Create a find unmatched query to compare two tables and produce a list of the records in one table that have no matching record in the other related table.

  32. Create an Unmatched Query…continued To create an unmatched query: • Click the Create tab. • Click the Query Wizard button in the Queries group. • Double-click the Find Unmatched Query Wizard option. • Complete the wizard steps. Find Unmatched Query Wizard

  33. CHECKPOINT 2 • Sum, Avg, Min, Max, and Count are examples of this type of function. • informative • summative • agitate • aggregate • This type of query calculates aggregate functions in which field values are grouped by two fields. • unmatched • crosstab • find duplicates • simple Answer Answer Next Question Next Question • To display the aggregate function list, click this button in the Show/Hide group. • Functions • Show • Display • Totals • Use this type of query if you suspect a record has inadvertently been entered twice under two different product numbers. • unmatched • crosstab • find duplicates • simple Answer Answer Next Question Next Slide

  34. Performing Queries Summary of Presentation Concepts • Design queries to extract specific data from tables • Use the Simple Query Wizard to create queries • Modify queries • Design queries with Or and And criteria • Create a calculated field • Use aggregate functions in queries • Create crosstab, duplicate, and unmatched queries

More Related