Microsoft Access 2010

1. MicrosoftAccess 2010 Lab 10 Querying a Database http://www.sophea.info/portal/Teaching/DelawareValleyCollege/tabid/64/Default.aspx

2. Objectives • Understand query and general guidelines for creating query • Create queries using Query Design • Use text and numeric data in criteria • Create and use parameter queries • Create query with calculated field Querying a Database

3. What is query? • A request for information from a database • User retrieves information from a database using query • Querying is a process of converting data into information Querying a Database

4. Querying a Database Give me the number, name, amount paid, and current due for client BC76 List the client number, name, business analyst number, and amount paid for all clients. Sort the results by business analyst number and amount paid. Querying a Database

5. Two ways of creating query • User retrieves information from a database using • Query by example (QBE): use GUI to assist users with retrieving data • Query language: Access also uses Jet SQL, a version of Simple Query Language (SQL), English-like statements that allow users to create query. Querying a Database

6. Querying a Database

7. General Guidelines • Identify the fields needed for the query • Determine the table that contains the fields and whether more than one table is required • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Querying a Database

8. Practice Query 1 Querying a Database

9. 1, 2. Identify the fields and table needed for the query • Read the query question and determine the fields and table required. Practice Query 1: Give me the number, name, amount paid, and current due of all clients. Sort the result by client name in ascending order. Querying a Database

10. 3,4,5. The answers are… • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Practice Query 1: Give me the number, name, amount paid, and current due of all clients. Sort the result by client name in ascending order. Querying a Database

11. Creating a Query in Design View • Click Create on the Ribbon to display the Create tab • Click the Query Design button (Create tab | Queries group) to create a new query Querying a Database

12. Creating a Query in Design View • Click the table to add to the query • Click the Add button to add the selected table to the query • Click the Close button to remove the dialog box from the screen Querying a Database

13. Creating a Query inDesign View • Drag the lower edge of the field list down far enough so all fields in the table appear Querying a Database

14. Creating a Query in Design View • Double-click each field to add to the query • the client number, name, amount paid, and current due) Querying a Database

15. Viewing the result • Click View button and select Datasheet view (or Run) to display the result of the query, adjust the column width to show the result completely. Querying a Database

16. Sorting the results • Click the Sort row below the field you wish to sort (client name) • Click the Sort row arrow to display a menu of possible sort orders, select ascending from the list. Querying a Database

17. Practice Query 2 Querying a Database

18. 1, 2. Identify the fields and table needed for the query • Read the query question and determine the fields and table required. Practice Query 2: List the client number, name, amount paid, current due, contract hours YTD, and business analyst number for all clients whose business analyst number is 14. Querying a Database

19. 3,4,5. The answers are… • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Practice Query 2: List the client number, name, amount paid, current due, contract hours YTD, and business analyst number for all clients whose business analyst number is 14. Querying a Database

20. Using a Number in a Criterion Querying a Database

21. Using a Comparison Operator in a Criterion Querying a Database

22. Using a Comparison Operator in a Criterion • Open the query in Design view • Enter the criterion with a comparison operator Querying a Database

23. Using a Compound Criterion Involving AND • Open the query in Design view • Add the criteria for two fields in the Criteria row Querying a Database

24. Using a Compound Criterion Involving OR • Open the query in Design view • Add criterion for one field to the Criteria row • Add criterion for another field in the or row (the row below the Criteria row) Querying a Database

25. Using Text Data in Criterion • Click the Criteria row for the field to produce an insertion point • Type the criterion • Click the View button to display the query results Querying a Database

26. Using Criteria for a Field Not Included in the Results • With the desired query open, click the Show check box to remove the check mark for a field containing criteria Querying a Database

27. Using Criteria for a Field Not Included in the Results Querying a Database

28. Using a Wildcard in Criterion • The asterisk wildcard character (*) matches any character or any block of characters in a specific position. • E.g. List all customer whose name start with Gr • Gr* will return both Grant and Grumpy • The question mark character (?) serves as a single-character placeholder. • E.g. m?ggie will return both maggie and meggie Querying a Database

29. Using a Wildcard Querying a Database

30. Practice Query 3 Querying a Database

31. 1, 2. Identify the fields and table needed for the query • Read the query question and determine the fields and table required. Practice Query 3: List the client number, name, amount paid, current due, contract hours YTD, and business analyst number for all clients whose state can be searched by the query user. Querying a Database

32. 3,4,5. The answers are… • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Practice Query 3: List the client number, name, amount paid, current due, contract hours YTD, and business analyst number for all clients whose state can be searched by the query user.. Querying a Database

33. Creating and Viewing a Parameter Query • If necessary, return to Design view and type the criterion for a parameter query ([Enter City], for example) • Run the query Querying a Database

34. Creating and Viewing a Parameter Query Querying a Database

35. Practice Query 4 Querying a Database

36. 1, 2. Identify the fields and table needed for the query • Read the query question and determine the fields and table required. Practice Query 4: List the client number, name, amount paid, current due, and the total amount (amount paid plus current due) for each client. Querying a Database

37. 3,4,5. The answers are… • Identify restrictions or criteria • Determine whether special order is required • Determine whether calculations are required Practice Query 4: List the client number, name, amount paid, current due, and the total amount (amount paid plus current due) for each client. Querying a Database

38. Using a Calculated Field in a Query • Open the query in Design view containing a field that can be calculated • Right-click the Field row in the first open column in the design grid to display a shortcut menu • Click Zoom on the shortcut menu to display the Zoom dialog box • Type the calculation in the Zoom dialog box • Click the OK button (Zoom dialog box) to enter the expression Querying a Database

39. Using a Calculated Field in a Query Querying a Database

40. Clearing the Design Grid • Open the query in Design view • Click just above the column heading in the first column in the grid to select the column • Hold the SHIFT key down and click just above the last column heading to select all the columns • Press the DELETE key to clear the design grid Querying a Database

41. Clearing the Design Grid Querying a Database