Transcript Slide 1
Review quiz. Answer questions. Discuss queries: ◦ What is a query? Turning data stored in a database into information for decision making. ◦ You: Completed two tutorials with step-by-step instructions for creating queries in MS Access. Now must apply knowledge and skills learned in the tutorials. ◦ Class today: Highlight key issues about queries. Explain aspects of MS Access queries in more detail. Answer any questions about queries in preparation for completion of the next part of the project (due 10/04). 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 data in tables? Result Table Query Underlying Tables A query reduces the number of rows and columns in the underlying tables to provide information for decision making. A query enhances the data in the underlying tables by adding calculations and logical conditions. 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. ◦ The goal of creating a query is to provide appropriate data for decision making. ◦ Queries “filter” the data; fewer columns, fewer rows, calculated fields, summarized information. Individual row queries. ◦ Using one table. ◦ Using multiple tables. Aggregate queries. ◦ Creating one line in the result table. ◦ Creating multiple groups in the result table. Parameter queries. Pre-written functions exist to do common summary calculations: ◦ Sum, count ◦ Max, min ◦ Avg, stDev, var ◦ First, last Can do calculations for all data in a result table, or grouped data in a result table Criteria. ◦ And vs. Or ◦ Relational operators. <, >, =, IN, LIKE ◦ Wildcards Multiple tables. Calculations. Logical conditions. ◦ IIF ◦ NOT 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. Computers require very explicit instructions. MS Access has default instructions, but that is because it is considered a very friendly, user-oriented package. Normally, must be very explicit about relational operators on the conditions of queries. ◦ =, >, <, >=, <= ◦ Like ◦ Between ◦ In ◦ Is Wildcard is an asterisk. 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. 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(trainingexpenses + travelexpenses > 500, “high expense”) ◦ IIF(ISNULL(trainingexpenses + travelexpenses), 0) ◦ IIF(ISNULL(trainingexpenses + travelexpenses), 0, trainingexpenses + travelexpenses) 5 minutes. Turn to the person or people next to you. Describe at least 3 queries that would be relevant to the training database we use for the Access project. ◦ None of the queries can be from the Access Project Part 2. ◦ Make sure that one of the queries requires calculations. 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. 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.