Transcript Slide 1
IS201 Agenda: 09/19 Modify contents of the database. Discuss queries: Turning data stored in a database into information for decision making. Create relationships through “Lookup tables”. At the beginning of class on 9/21: Login. Copy the Belmont database from: Kdrive:\is201\is201-hilfer\AccessBookFiles\Access1\Tutorial Save, rename and open the Belmont database in your preferred area to store files (flash drive or u:drive). Previously in IS201… Discussed information visualization and the importance of presenting information in a way that is usable and understandable. Discussed how a computer stores data and what data are stored. Learned how to store data in a database, focusing on the design of data. Learned how to create tables, relate tables and populate tables in MS Access. Touched on accessing data from a database. Have not really talked about presenting information from the data stored in a database. Belmont Landscapes Database Design Difference between table and query 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. General MS Access query vocabulary 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. 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. 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. Understanding relational 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. Making 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. Grouped output 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