Querying a Database Access Project 2

1. Querying a Database Access Project 2

2. What is a Query? • In general, a query is a form of questioning, in a line of inquiry. A query may also refer to: • A statement of information needs, typically keywords combined with Boolean operators and other modifiers, in the field of information retrieval. • Simply put … a question represented in a way that the database management system can understand (ie. Access) • When you search for a book/article at the Library or do an Internet search, you really are querying a database.

3. Why do we Need Queries? • Natural languages (English) are too vague • With complex questions, it can be hard to verify that the question was interpreted correctly, and that the answer we received is truly correct. • Consider the question: Who are our best customers? • We need a query system with more structure • We need a standardized system so users and developers can learn one method that works on any (most) system. • Query By Example (QBE) • SQL

4. Four Questions to Create a Query • What output do you want to see? Select the fields you wish to display. • What do you already know? Determine the constraints you already know and any additional “hidden” constraints. • What tables are involved? Determine the tables involved in your query, even if the query does not display data from those tables. • How are the tables joined together? Are the tables joined together automatically because of preset keys or do you need to remove this auto-join and create a different join?

5. Query Organization • Single table – we can query against one table(ie. SELECT) • Constraints – we can place constraints on the fields (ie. =,>,<,<>, BETWEEN, LIKE, IS, AND, OR, NOT, NULL) • Computations – we can perform a mathematical computation (ie. +) or aggregation (ie. AVG) • Groups/Subtotals – we can group records together(ie. GROUP BY, WHERE, HAVING) • Multiple Tables – we can join 2 or more tables together by key fields (ie. INNER JOIN, OUTER JOIN, RIGHT JOIN, LEFT JOIN)

6. Access Project 2 • Dr. Gernaey, the Director of Continuing Education for Ashton James College (AJC), is eager to query against the Client and Trainer databases we setup in Project 1. • Turn to page AC 67: • What are the name, amount paid, and current due of client CP27 • Which clients’ names begin with Fa? • Which clients are located in Lake Hammond? • Which clients have a current due of $0.00? • Which clients have an amount paid > $20,000? • Which clients of Trainer 48 have an amount paid > $20,000? • In what cities are all the clients located? • How many hours has each trainer worked so far this year? • What is the client number and name of each client and what is the number and name of the trainer to whom each client is assigned?

7. Using Text Data in Criterion (AC 75) • You would use text data in criteria for a data field whose data type is Text • Note that sometimes fields that appear to be numeric are actually text (ie. social security number) • Simply type the text in the Criteria row below the corresponding field name • Access will automatically enclose text in quotation marks. However, some DBMS require you to add the quotes.

8. Using a Wildcard in Criterion (AC 77) Access provides 2 special wildcards: • Wildcards are symbols that represent any character or combination of characters • Access automatically adds the LIKE operator and the quotation marks to criteria that use wildcards. • The Asterisk (*) represents any collection of characters • Fa* The letters Fa, followed by any collection of characters • The Question Mark (?) represents any single character • T?m The letter T, followed by any single character followed by the letter m, such as Tom or Tim

9. Criteria for a Field Not in the Result (AC 78) • Sometimes you may need to supply criteria for a field that you do not want to appear in the results of a query • Remove the check mark from the Show check box

10. Using Numeric Data in Criterion (AC 82) • You would use numeric data in criteria for a data field whose data type is Numeric • Enter the number in a criterion without dollar signs and commas (ie. no formatting). • An error message will display in Access if formatting is entered with the number.

11. Using Comparison Operators (AC 83) • Unless specified, Access assumes that the criteria you enter involves equality or an exact match (ie. =, LIKE) • The order of the greater than or equal to (>=) and less than or equal to (<=) operators is fixed; they cannot be reversed. • Comparison operators are used with Numeric and Text data. • The <> operator also can be used to indicate NOT • What 5 comparison operators can be used in queries? > < >= <= NOT

12. Using Compound Criteria (AC 84) • Sometimes you will have more than one criterion that the data for which you are searching must satisfy • There are two types of compound criteria: AND criterion each individual criterion must be true for the compound criterion to be true OR criterion  the compound criteria is true if either individual criterion is true

13. Using Compound Criteria (AC 84) • When using the AND criterion: The criteria must go on the SAME line in the Criteria area of the grid. • When using the OR criterion: The criteria must go on SEPARATE lines in the Criteria area of the grid.

14. Sorting Data in a Query (AC 85) • Sorting means ordering records in a particular way • The sort keyis the field or fields on which the records are sorted • You cannot sort on an asterisk • To sort a query that includes an asterisk, add the individual sort fields to the design grid and remove the check mark from the Show check box

15. Sorting on Multiple Keys (AC 88) • If sorting on more than one field … (such as Student Number and Name)… the more important field is called the major key (primary sort key) • The secondary field (s) is called aminor key (secondary sort key) • Major keys must be to the left of minor key in design grid (and in SQL)

16. Sorting on Multiple Keys (AC 88) • Example: City within State • What is major sort key? What is minor sort key? • Major sort key is State which follows the word “within” • Minor sort key is City • However, we wish to display the city before the state field. • To display city before state but sort by city within state … • Add the state field again to the design grid and remove the check mark from the first state field (major sort key)

17. Omitting Duplicates (AC 87) • When you sort data, duplicates normally are included. • For example, you could see “St. Louis” city appear more than once in Maryville’s student database. • We sometimes do not want duplicate values … • There are 2 ways to omit duplicate values: • Use a DISTINCT clause in SQL • Change Unique Values property (to “Yes”) on the Property Sheet • The Property Sheet is a window that contains various properties of the object to which it pertains

18. Creating a Top-Values Query (AC 89) • Sometimes you may want to only show a specified number of records or a percentage of records • To do this run a Top-values query • When you sort records … • Limit results to records having the highest (descending sort) or lowest (ascending sort) values • Once the query has been run … Close the query to reset the Top Values box to All

19. Joining Tables (AC 92) • A join is matching one table to another, based on some conditionExample:Client  Trainer YIELDS Clients with assigned trainer • In Access, a join line is automatically inserted when fields from the 2 tables have the same name • If you do not want the auto-join, delete the join line • Access does not add a join line when: (1) the fields do not have matching names or (2) the data type and data length are not identical on both fields • If fields should be joined, drag the appropriate field in one of the tables onto the matching field in the other table

20. Types of Joins & Join Properties (AC 94) • Inner Join (Access Default)Only records that match are displayed in the result • Right Outer JoinAll the records in the “one” table in the relationship will display • Left Outer JoinAll records in the “many” table in the relationship will display

21. Using Calculated Fields (AC 96) • A calculated field is a field that can be computed from other fields in a table (s) • To enter a calculated field, enter the following  - a name for the calculated field - a colon - the expression • Field names in the expression must be enclosed in brackets [ ] • Constants, such as .05, are not enclosed in brackets • You may use the following mathematical operators: addition (+), subtraction (-), multiplication (*), division (/), parentheses • When more than one mathematical operator appears in an expression, multiplication and division are performed before addition and subtraction. • To change the order of precedence, use parentheses.

22. Changing a Format and Caption (AC 98) • You can change the way items appear in query results by changing the format • Use the Property Sheet for the given field that you wish to format • You can change the desired property, such as number of decimal places or the caption

23. Calculating Statistics (AC 99) • Access supports built-in statistics. These statistics are called Aggregate functions. An aggregate function is a function that performs some mathematical function against a group of records. • Include the function in the Total row on design grid

24. Calculating Statistics (AC 99) Aggregate Functions are listed below: • COUNT Determines the number of items or values • SUM Adds the values • AVG (average) Determines the arithmetic mean of values • MAX (largest value) Determines the greatest value • MIN (smallest value) Determines the smallest value • STDEV Measures how widely values are dispersed (std. deviation) from the average value • VAR (variance) Squares the standard deviation • FIRST Determines the first item or value • LAST Determines the last item or value • AVG, SUM, STDEV, VAR can be used only with numeric fields

25. Calculating Statistics (AC 99) • An aggregate function is different from a calculated field • A calculated field is a mathematical operation performed on individual records • An aggregate function is performed on groups of records

26. Use Criteria in Calculating Statistics (AC 101) • Sometimes you only want records that satisfy a certain criteria included in your statistics • When you are using Aggregate Functions (ie. a Total Row), you can use a Where Clause to restrict your records • For instance, we may want to see the Average Amount Paid by clients where Trainer Number = 42

27. Grouping (AC 103) • Sometimes you may want to report your statistics in combination with grouping • Grouping means to create groups of records that share common characteristics • For instance, you may want to show the Average Amount Paid for clients of each trainer • Group By Trainer Number • Avg Amount Paid

28. Crosstab Queries (AC 104) A Crosstab Query calculates a statistic (ie. sum, avg,) for data that is grouped by two different types of informationExample:Total Amount Paid  group on City and Trainer Number • One field appears down the side of datasheet (City) • Other field appears across the top (Trainer Number)

29. Saving Queries • When you save a query, you are saving the design of the query, not the results. • If you wish to save the results of a query, you can save the results to a table. • The query design you saved is run against the current database. • Note that if changes have been made to the data since the last time you ran it, the results of the query may differ.

30. Querying a Database Access Project 2 Any Questions?