IS201 Agenda: 09/19

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).

2. 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.

3. Belmont Landscapes Database Design

4. 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.

5. 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.

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. 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.

8. 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.

9. 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.

10. 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