IS201 Class: 09/26/2013

1. IS201 Class: 09/26/2013 • Continue queries • 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. • Answer any questions about queries in preparation for completion of the next part of the project (due 10/03). • Learn conditional logic using IIF and ISNULL. • Do conditional logic exercise.

2. The goal of a query is to provide information from the data in a database. A query reduces the number of rows and columns in the underlying tables to provide information for decision making. This is done through relational criteria. A query enhances the data in the underlying tables by adding calculations and logical conditions.

3. Capabilities of queries • Individual rows vs. grouped output • Criteria. • And vs. Or • Relational operators. <, >, =, IN, LIKE, BETWEEN • Wildcards • Multiple tables. • Parameters • Calculations. • Logical conditions. • IIF • ISNULL

4. General types of queries • Individual row queries. • Using one table. • Using multiple tables. Tables must be related. • Aggregate queries. • Can use one table or multiple tables. • Creating one line in the result table as a summary of all data in the related tables. • Creating multiple groups in the result table.

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

7. Query Wizard – morning class only • Query to Find “unmatched” data. • Data that exists in one table (usually the parent), but does not exist in a related table (usually the child). • Examples: • Which donors have not made any donations? • Which categories have not been received on any donations? • Which agencies have not received any donations?

8. Grouped output • Pre-written functions exist to do common summary calculations: • Sum, count • Max, min • Avg, stDev, var

9. Parameter queries • Enter data each time a query is run. • Can provide greater flexibility for queries that don’t have structured/standardized input. • Parameter is placed within standard criteria. Parameter is surrounded by square brackets, just like a data field.

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

11. Logical Conditions • Frequently want to see if something is TRUE or FALSE, then do something based on that information. • Example: If a donation value is > $200, then we want to have a column in that output that says it is a “big” donation. • Logical condition for Access: • IIF(donationvalue>200, ‘BIG’)

12. IIF Function Syntax • The IIF function has three arguments: • Relational condition • What to do if the relational condition is True part • What to do if the relational condition is False part • Examples: • IIF(donationvalue > 200, ‘BIG’, ‘small’) • IIF(donationvalue > 200 and donationvalue < 500, ‘medium’, ‘small’) • IIF(donationdesc = ‘cash’, donationvalue , donationvalue * .75) • IIF(datediff(“d”, now(), donationdate) > 0, ‘future donation’)

13. Working with “nothing” • Nothing is not nothing for computers. • 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. • In MS Access: • ISNULL(company) • IIF(ISNULL(company), firstname + ‘ ‘ + lastname, company)