Using Special Select Queries

1. SESSION 5.1 In this session we will be exploring Pattern Match Queries List-Of-Values Queries, Non-Matching Values in Queries Both the “And” and the “OR” Operators Parameter Queries (or “Prompt Queries”) CrossTab Queries TopValue Queries Using Special Select Queries Microsoft Access 2000 Class #5

2. TheMajorSteps of a MicroSoft Access Database • Tables • Queries • Forms • Macros • Reports • Modules On our road map, we are here!

3. Remember, a query is a question you ask about data stored in a database.

4. Pattern Match Queries, List-Of-Values Queries, Non-Matching Values in Queries, Both the “And” and the “OR” Operators and Parameter Queries (or “Prompt Queries”) are all simply normal select queries that use criteria. The Criteria Row

5. Last session we looked at defining record selection by using criteria and creating calculated fields in Select queries. The Criteria Row

6. In this session we will look at specific criteria in Select queries. The Criteria Row

7. Let’s take a quick look at the “Like”, “In”, “Not”, & the “And / Or” comparison operators that we will be using in our class exercise for this session. The “Like” comparison operator (used in pattern matches) selects records by matching field values to a specific pattern that includes one or more wildcard characters - asterisk (*), question mark (?), and the number symbol (#).

8. To show only those records with an area code of 313 you would enter this in the Phone Criteria Row

9. In the previous example we used parentheses ( ) in our criteria expression.. If we had not needed the parentheses we could have simply typed 313* and Access would have inserted the word Like and the quotes for us.

10. The asterisk represents any string of characters, the question mark represents any single character, and the number symbol represents any single digit. Using a pattern match is similar to using an exact match, except that a pattern match includes wildcard characters.

11. A list-of-values match selects records whose value for the selected field matches one of two or more simple condition values. You could accomplish this by including several “Or” conditions in the design grid, but Access also provides the “In” comparison operator that works as a shorthand.

12. The “In” comparison operator allows you to define a condition with two or more values. If a record’s field value matches at least one value from the list of values, Access selects that record for inclusion in the query results.

13. To show all records that contain CustomerNum 123, 135 and 202 you would enter this in the CustomerNum Criteria row

14. A non-matching value selects records whose value for the selected field does not match the simple condition value. You create the selection criterion using the “Not” logical operator.

15. To omit all states that begin with the letter M you would enter this in the State Criteria row

16. You could also type just Not M* and Access would insert the Like and Quotes for you.

17. Let’s talk about something called compound conditions for a moment. Barbara just came in and asked to see a list of all customers in Ohio and Michigan. Notice the word “and” in Barbara’s request.

18. What would happen if we typed “OH” And “MI” in the State Field’s Criteria Row?

19. The result would look something like this, not finding anything. That’s because we have asked Access to show us all records that contain both MI and OH in the same field.

20. To answer Barbara’s question we would have to enter

21. This criteria would return something like

22. You have the option of entering this criteria in two ways

23. Using the second method as shown below Would return only records in Ohio that contain “756” in the Phone field and every record in Michigan.

24. Using the second method as shown below Would return only records in Ohio that contain “756” in the Phone field and only records in Michigan that contain “756” in the Phone field.

25. Hmm?? Lately it seems as though the questions are getting more and more intense!

26. What if ? Barbara especially is beginning to realize that many unsolved mysteries can be solved with this program by using queries.

27. This has been causing Barbara’s Staff a considerable amount of concern. Two things have been happening. First, more and more time has been spent creating queries.

28. Secondly, The number of queries is growing and it’s becoming more and more difficult to keep track of which query is which. There is an answer to this dilemma!

29. The wizards at Microsoft have come up with a very simple solution!

30. The solution is a parameter query!

31. A parameter query is a query that prompts you for criteria when the query is viewed or run. When Access runs the query, it will display a dialog box and prompt you to enter the criteria desired.

32. To create a parameter query, simply enter the text that you want as your prompt in square brackets in the criteria row for the field that you want to set the criteria for.

33. Leonard is interested . in how much business . Valle Coffee is . doing in each . state! He wants . to know the . total sales by . state and by . month. Can I get summaries ?

34. Is the Sun in the Sky? The answer is “YES!”. The solution is “CrossTab Queries”!

35. A Crosstab query performs aggregate (total) functioncalculations on the values of one database field, and displays the results in a spreadsheet like format.

36. The quickest way to create a crosstab query is to use the Crosstab Query Wizard, which guides you through the steps for creating crosstab queries.

37. A Crosstab query can also display one additional aggregate function value that summarizes each row’s set of values.

38. The crosstab query datasheet below shows the total sales for each month by state and shows a row total.

39. There are 3 other types of select queries that we need to take a quick look at. • Find Duplicates • Find Unmatched • Top Values

40. Finding duplicate records in very large databases can be a very distasteful and tedious task

41. A find duplicates query is a select query that locates duplicate records in a table or query.

42. You can create this type of query using the Find Duplicates Wizard!

43. Finding unmatched records in very large databases can also be a very tedious task

44. A find unmatched query is a select query that locates unmatched records in a related table.

45. For example, if you can not establish referential integrity between the Customer Table and Order Table, you might have a customer # in the order table that does not exist in the customer table.

46. You can create this type of query using the Find Unmatched Query Wizard!

47. The last select query that we will explore is called a Top Values Query

48. Whenever you have a query that displays a large group of records, you can limit the number to a more manageable size by showing just the first 10 records for example.

49. The Top Values property for a query lets you limit the number of records in the query results. For the Top Values property, you enter either an integer (such as 10, to show the first 10 records) or a percent (such as 50%, to show the first half.)

50. The Top Values Query Button can be found on the Query Design Window’s Tool Bar as shown below: