By Wanda Gibson, AI, MMI, SME

1. Querying a Database By Wanda Gibson, AI, MMI, SME

2. Querying a Database • A query is a question written specifically so Access will understand • Users run a query to obtain an answer • Specific rules must be followed when creating a query • If no results are given, a user asked the question incorrectly

3. Ways to create a Query

4. New Button Clicking the “New” button on the Query Object Bar takes a user to the “New Query” Box. A user can then choose the type of query he or she wants from the list.

5. In the Simple Query Wizard window, users choose the Table and the Fields needed for the Query.

6. Users should only choose the fields necessary for the Query. Include Stock Number, Description, Inventory, and Selling Price for all records.

7. Click Finish After all fields are selected, click the “Next” button and finally “Finish” to complete the Wizard.

8. Simple Query Wizard Results

9. In the Select Query Window, choose the Toys Table from the Show Table box.

10. Run Button Include the Stock Number, Description, Inventory, and Selling Price for all records. Run the Query.

11. The results of this query are the same as the results the Simple Query Wizard produced.

12. All records are requested when a user chooses the * from the table list.

13. This is another way to select all the fields in the table for the query. Either drag field names to the field line of the design grid or double click the field names to add them to the grid. Selecting individual field names will produce the same results as using the *.

14. This exact table, with all records requested, will be produced if a user chooses the * or if a user chooses the individual field names.

15. Run Button Display the Stock Number, Description, Cost, and Vendor ID for all products whose Vendor ID is TT96. Run.

16. Vendor ID TT96 The query was – Display the Stock Number, Description, Cost and Vendor ID for all products whose Vendor ID is TT96.

17. Wildcards Access has two wildcards. Question Mark [?] -- Is used for a specific number of characters. For instance, A23 would appear A??. Each character needs a ?. Verify placement of the ? in the following ways. If there is a letter or number before the text a user is including in the criteria, the user must place a ? before the text. 3 Malone would be ?Malone If there is a letter or number after, the user must place the ? after the text. Malone E would be Malone? If there is a letter or number before and after, the user will need a ? before and after the text. 3 Malone E would be ?Malone?

18. Asterisk [*] -- Is for any number of characters. Verify placement of the * in the following ways. If there are letters or numbers before the text a user is including in the criteria, the user must place an * before the text. 365 Malone would be *Malone If there are letters or numbers after, the user must place the * after the text. Malone Avenue would be Malone* If there are letters or numbers before and after, the user will need an * before and after the text. 365 Malone Avenue would be *Malone*

19. Display the Stock Number and Description for all items where the description includes the letters Block. The * is used before Block and after Block because of the placement of the letters in the description of the toys.

20. Display the Stock Number and Description for all items where the description includes the letters Block.

21. Display the Stock Number, Description, and Vendor ID for all items with a Cost less than $15. Run. Take  out of Show Box so field will not show in result Cost does not show in result.

22. Since the Show Box  was removed from the Cost field, it is not part of the result.

23. Display the Stock Number and Description for all products that have a Selling Price greater than $30. Run. Again, a criteria field is not included in the result.

24. All Selling Prices are greater than $30. $65.00 $65.00 $62.50 $40.00 $75.00 $87.50

25. AND Criteria Display all fields for those items with a Selling Price greater than $10 AND the Inventory count is at least 10.

26. Inventory count is at least 10 AND Selling Price is greater than $10 The AND means both criteria must be met.

27. Display all fields for toys with a Vendor ID of GT36 OR a Selling Price greater than $30. OR criteria

28. Selling Price is more than $30. Vendor ID is GT36. At least one of the two criteria must be met, but both criteria do not have to be met together. OR

29. Calculated Field • A Calculated Field is a Field that is added to the Query where a calculation can be made. • Any Fields from the Table used in a Calculated Field must be included in square brackets [].

30. New Field Name New Field Name Separator Math Function Field Name : [Field] * [Field] Fields from Table In this calculated field, two field names from the table are used.

31. New Field Name Separator Math Function Field Name : [Field] * .10 Field from Table Calculation In this calculated field, only one field from the table is used.

32. In the first blank grid section, Right Click and choose Zoom Include the Stock Number and Description in the design grid. Calculate the inventory value (inventory * cost) for all records.

33. Include the Stock Number and Description in the design grid. Calculate the inventory value (inventory * cost) for all records.

34. This result shows the Inventory value (inventory * cost) for all records.

35. Joining Tables • Joining Tables refers to one Field listed in 2 or more tables that shows the same information (Vendor ID). • This Field is usually the Primary Key in one of the Tables. • This Field must have exactly the same Structure (Name, Length, Data Type) and the same data. • A Join Line connects the Tables on the Field Names that are the same. By Joining Tables, a useris able to use Fields from both Tables in Queries.

36. Add Vendors Table to design grid Show Table

37. Join Line These Tables are Joined, and both are used in this query.

38. Fields from both tables are used. Display the Stock Number, Description, and Cost from the Toys Table. Display Vendor Name, Phone Number, and Sales Rep from the Vendors Table.

39. The result of using fields from both tables is shown here.

40. Inventory was used in the criteria, but it is not included in the results. Using the previous query, restrict retrieval to those whose Inventory is less than or equal to 10.

41. Inventory is used in the criteria, but it is not included in the results. Toys Table Vendors Table

42. Aggregate Functions • An aggregate function is a function that performs a mathematical calculation using a group of records in a table. • In order to use an aggregate function, a user must turn on the Total key ().

43. Examples of Aggregate Functions Group By -- Combines records that have identical values Sum –- Gives the total of a specified numeric field Avg –- Gives the average of a specified numeric field Min –- Gives the minimum number of a specified numeric field Max –- Gives the maximum number of a specified numeric field Count –- Counts the records in a specified field

44. Total is added to the Design Grid List Select Avg from the drop down list. Aggregate Functions – Users must turn on the Total Key ().

45. Average Selling Price

46. Select Avg from the drop down list. Average Selling Price by Vendor ID

47. Average Selling Price Average Selling Price by Vendor

48. Count records using Stock Number Select Count from the drop down list.

49. Average Selling Price Average Selling Price by Vendor Count of Stock Numbers

50. References Microsoft product screen shots reprinted with permission from Microsoft Corporation.