Access 2010 Level 2 Unit 1 Advanced Tables, Relationships, Queries, and Forms

2. Access 2010 Level 2 Unit 1 Advanced Tables, Relationships, Queries, and Forms Chapter 3 Advanced Query Techniques

3. Advanced Query Techniques Quick Links to Presentation Contents • Extract Records Using Select Queries • Modify Join Properties in a Query • CHECKPOINT 1 • Create and Use Subqueries • Select Records Using a Multiple-Value Field • Perform Operations Using Action Queries • CHECKPOINT 2

4. Extract Records Using Select Queries • A select query is the type of query most often used in Access. • Select queries extract records from a single table or from multiple tables according to criteria that you specify.

5. Extract Records Using Select Queries…continued • A filteris used in a datasheet or form to temporarily hide records that do not meet specified criteria. The subset of records can be edited, viewed, or printed. • A filter is active until it is removed or until the datasheet or form is closed.

6. Extract Records Using Select Queries…continued To save a filter as a query: • Open the table. • Filter the table as desired. • Click the Advanced Filter Options button in the Sort & Filter group in the Home tab. • Click the Filter By Form option at the drop-down list. continues on next slide… Filter By Form option

7. Extract Records Using Select Queries…continued • Click the Advanced Filter Options button in the Sort & Filter group in the Home tab. • Click the Save As Queryoption in the drop-down list. continues on next slide… Save As Query option

8. Extract Records Using Select Queries…continued • At the Save As Query dialog box, type the desired query name. • Click OK. • Close the Filter By Form datasheet. • Close the table. Save As Query dialog box

9. Extract Records Using Select Queries…continued • In a parameter query, specific criteria for a field are not stored with the query design. Instead, the field(s) used to select records have a prompt message that displays when the query is run. Enter Parameter Value dialog box

10. Extract Records Using Select Queries…continued • The message in the dialog box is created in the field to which the criterion will be applied. • When the query is run, the user types the criterion at the Enter Parameter Value dialog box. Access then selects the records based on that criterion. • If more than one field contains a parameter, Access prompts the user one field at a time.

11. Extract Records Using Select Queries…continued To create a parameter query: • Start a new query in Design view. • Add the desired table(s). • Close the Show Table dialog box. • Add the desired fields to the query design grid. • Click in the Criteria row ofthe field to be prompted. • Type the message text, encased in square brackets. • Repeat Steps 5-6 for each additional criteria field. • Save the query. • Close the query. Criteria row

12. Modify Join Properties in a Query • The term join properties refers to the way in which Access matches the values in the common fields between two tables in a relationship. • Access provides for three join types in a relationship: an inner join, a left outer join, and a right outer join.

13. Modify Join Properties in a Query…continued To create a query with an inner join: • Create a new query in Design view. • Add the tables to the query window. • Double-click the join line between the tables. • At the Join Properties dialog box, click OK. • Add the desired fields to the query design grid. • Save and run the query. Join Properties dialog box

14. Modify Join Properties in a Query…continued To create a query with a left outer join: • Create a new query in Design view. • Add the tables to the query window. • Double-click the join line between the tables. • At the Join Properties dialog box, click option 2. • Click OK. • Add the desired fields to the query design grid. • Save and run the query. option 2

15. Modify Join Properties in a Query…continued To create a right outer join: • Create a new query in Design view. • Add the tables to the query window. • Double-click the join line between the tables. • At the Join Properties dialog box, click option 3. • Click OK. • Add the desired fields to the query design grid. • Save and run the query. option 3

16. Modify Join Properties in a Query…continued To remove a table from a query: • Open the query in Design view. • Right-click the table. • Click the Remove Table option at the shortcut menu. Remove Table option

17. Modify Join Properties in a Query…continued To create a self-join query: • Create a new query in Design view. • Add two copies of the same table to the query. • Right-click the second table name. • Click the Properties option. continues on next slide… Properties option

18. Modify Join Properties in a Query…continued • Click in the Alias property box and delete the existing table name. • Type the alias table name. • Close the Property Sheet. continues on next slide… Alias property box

19. Modify Join Properties in a Query…continued • Drag the field name from the left table to the field name in the right table that contains the matching values. • Add fields to the query design grid as required. • Run the query. • Save the query. field name

20. Modify Join Properties in a Query…continued • If a query is created from two tables for which no join is established, Access will not know how to relate the records in each table. • In the absence ofa relationship, Access produces a datasheet representing every combination of records between the two tables. This type of query is called a cross product or Cartesian product query. • If you add two tables to a query and no join line appears, create a join by dragging a field from one table to a compatible field in the other table.

21. CHECKPOINT 1 • This is the type of query most often used in Access. • append • select • update • make-table • The Advanced Filter Options button is located in this tab. • Home • Create • External Data • Database Tools Answer Answer Next Question Next Question • This is used to temporarily hide records that do not meet specified criteria. • screen • section • filter • sort • By default, Access uses this type of join between tables. • inner • outer • left • right Answer Answer Next Question Next Slide

22. Create and Use Subqueries • When performing multiple calculations based on numeric fields, you may decide to create a separate query for each individual calculation and then use subqueries to generate the final total. • A subqueryis a query nested inside another query. • Using subqueries to break the calculations into individual objects allows you to reuse a calculated field in multiple queries.

23. Create and Use Subqueries…continued To nest a query within a query: • Start a new query in Design view. • At the Show Table dialog box, click the Queries tab. • Double-click the query to be used as a subquery. • Add other queries or tables as required. • Close the Show Table dialog box. • Add fields as required. • Save and run the query. Queries tab

24. Select Records Using a Multiple-Value Field To show a multiple-value field in separate rows in a query: • Open the query in Design view. • Click in the Field list box of the multiple-value field in the design grid. • Move the insertion point to the end of the field name. • Type a period. • Press Enter to accept the .Value property. • Save the query. Field list box

25. Perform Operations Using Action Queries

26. Perform Operations Using Action Queries…continued To create a make-table query: • Create the query in Design view. • Add the desired table to the query. • Add the desired fields to the query design grid. • Enter the criteria to select records. • Run the query. • Switch to Design view. • Click the Make Table button in the Query Type group in the Query Tools Design tab. continues on next slide… Make Table button

27. Perform Operations Using Action Queries…continued • Type a table name. • At the Make Table dialog box, select the destination database. • Click OK. • Run the query. • Click Yes. • Save the query. Make Table dialog box

28. Perform Operations Using Action Queries…continued • A delete query is used to delete, in one step, a group of records that meet specific criteria. • You can use this action query in any instance in whichthe records you want to delete can be selected using a criteria statement.

29. Perform Operations Using Action Queries…continued To delete records using a query: • Right-click the make-table query. • Click the Design View option at the shortcut menu. • Click the Delete button in the Query Type group in the Query Tools Design tab. • Click the File tab. • Click Save Object As. • Type the name and click OK. • Run the query. • A the Microsoft Access message, click Yes. Microsoft Access message

30. Perform Operations Using Action Queries…continued To add records to a table using a query: • Open the make-table query in Design view. • Click the Append button in the Query Type group in the Query Tools Design tab. • At the Append dialog box, click OK. • Click the File tab. • Click Save Object As. • Type the name and click OK. • Run the query. • A the Microsoft Access message, click Yes. Append dialog box

31. Perform Operations Using Action Queries…continued To create an update query: • Create the query in Design view. • Add the desired table to the query. • Add the desired fields to the query design grid. • Enter the criteria to select records. • Run the query. • Switch to Design view. • Click the Update button in the Query Type group in the Query Tools Design tab. • Click in the Update To box in the field to be changed. • Type the update expression. • Run the query. • Click Yes. • Save the query. Update To box

32. CHECKPOINT 2 • This is a query nested inside another query. • make-table query • update query • append query • subquery • This type of query is used to delete, in one step, a group of records that meet specific criteria. • update • delete • append • select Answer Answer Next Question Next Question • With this type of query, selected records are added to the end of an existing table. • update • delete • append • select • With this type of query, a global change is made to aselected group of records based on an expression. • update • delete • append • select Answer Answer Next Question Next Slide

33. Advanced Query Techniques Summary of Presentation Concepts • Save a filter as a query • Create and run a parameter query to prompt for criteria • Add tables to and remove tables from a query • Create an inner join, left join, and right join to modify query results • Create a self-join to match two fields in the same table • Create a query that includes a subquery • Assign an alias to a table and a field name • Select records using a multiple-value field in a query • Create a new table using a make-table query • Remove records from a table using a delete query • Add records to the end of an existing table using an append query • Modify records using an update query