Access Queries

1. Access Queries • Agenda 2/24/14 • Review quiz, answer questions • Create relationships through “Lookup tables” • Discuss queries: • Turning data stored in a database into information for decision making. • Highlight key issues about queries • Answer any questions about queries to prepare you to complete part 2 of the Access Project.

2. Creating a Lookup Field • A lookup field lets the user select a value from a list of possible values • You use a Lookup Wizard field in Access to create a lookup field in a table • Example: Contract – ContractType relationship

3. What is a query? • Turning data stored in a database into information for decision making. • A query creates a “virtual table”. • Table vs query: • What is the difference between a query and a table? • What is the difference between a query datasheet and a table datasheet? • Why do we create queries when we already have the data in tables?

4. Table vs Query Cont. • Table contains structure of data, constraints and actual data. • Table is referred to as “underlying data”. • Query is a way to look at the data. • Queries seldom look at the complete contents of a table because tables are usually very big, with many columns and many rows. • A query reduces the number of rows and columns in the underlying data (tables) to provide information for decision making. • Queries “filter” the data; fewer columns, fewer rows, calculated fields, summarized information. • A query enhances the data in the underlying tables by added calculations and logical conditions.

5. MS Access query vocabulary review • Design view: Used to structure a query. Referred to as “query by example” or QBE. • Result table: The table produced by the query. Shown in the datasheet view. • SELECT query window: The window displayed in design view that is filled out to produce a result table. Also called the query design grid. • Field row: The area in the SELECT query window used to define what columns should appear in the result table. • Criteria row: The area in the SELECT query window used to identify which rows should appear in the result table.

6. Understanding data like a computer understands data Each value in a field has very specific data coded for a computer to read. Humans can discern vague similarities and differences among data fairly easily. Computers are more exacting. Computers need you to tell them when data is a date, or a character, or a number. A zero is not the same as a blank which is not the same as a null. A null is a special character assigned to a field that technically has “no value”. It is very useful because we can search for a null value with special operators.

7. Types of queries • Generalized queries • Using one or multiple tables • Specify the fields and records you want to Select. • Aggregate queries • Create one line in the result table. • OR • Create multiple groups in the result table. • Use the “Totals” Button • Parameter queries • Type of query that prompts the user for input before it runs. The query then uses the input as criteria that control your results. Example: City Parameter query in Panorama database.

8. Queries with multiple tables Referred to as “joining” tables. Can produce confusing results. Very dependent on a well-designed database. The tables must be related with appropriate foreign keys or the tables cannot be joined correctly for queries. All tables in the query must be joined for the query to work properly!

9. Relational operators • Computers require very explicit instructions. • Used in criteria to specify what records to include in the query. • Normally, must be very explicit about relational operators on the conditions of queries. • =, >, <, >=, <= • Like • Between • In – Define a condition with a list of two or more values for a field • Is • Wildcard is an asterisk.

10. And vs Or

11. Logical Operators • You use the logical operators to combine two Boolean values and return a true, false, or null result. Logical operators are also referred to as Boolean operators. • And: Returns True when Expr1 and Expr2 are true. • Example: InvoiceAmt >$500 And City = Lansing • Or: Returns True when either Expr1 or Expr2 are true. • Example: InvoiceAmt > $500 OR City = Lansing • Not: Returns True when Expr is not true. • Example: Not In ("holland","rockford","saugatuck")

12. New columns based on calculations • Can do calculations for a column based on the data in other columns for that same row. • Can use mathematical operators. • Can use pre-written functions in MS Access. Many different types of pre-written functions for date handling, data type conversion, calculations, etc. • See the pre-written functions in the expression builder. • Can be very simple to very complicated.

13. Expression Builder • To perform a calculation, you define an expression containing a combination of database fields, constants, and operators • Expression Builder is an Access tool that makes it easy for you to create an expression • In the Query Setup group on the Design tab, click the Builder button • Use the expression elements and common operators to build the expression, or type the expression directly in the expression box • Click the OK button

14. Formatting a Calculated Field • You can specify a particular format for a calculated field, just as you can for any field, by modifying its properties

15. Assigning a Conditional Value to a Calculated Field • The & (ampersand) operator is a concatenation operator that joins text expressions, the plus sign (+) has the same function. • The IsNull function tests a field value or an expression for a null value; if the field value or expression is null, the result is true; otherwise, the result is false

16. Aggregate functions • Aggregate functions are used to create grouped output. • Common summary calculations have pre-written functions: • Sum, count • Max, min • Avg, stDev, var • First, last • Calculations can be done for all data in the result tableor to create grouped output in the result table. • The Group By operator divides the selected records into groups based on the values in the specified field

17. IIF • The IIf (Immediate If) function assigns one value to a calculated field or control if a condition is true, and a second value if the condition is false.

18. IIF Example • Frequently want to see if something is TRUE or FALSE. • Example: If a training event has > $500 in expenses, then it is a high expense event. • Logical condition for Access: • IIF(ProductCost > ProductPrice, “Losing Money”) • IIF(ISNULL(trainingexpenses + travelexpenses), 0) • IIF(ISNULL(trainingexpenses + travelexpenses), 0, trainingexpenses + travelexpenses)

19. Wildcards