1 / 13

IS201 Class: 09/26/2013

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.

tan
Download Presentation

IS201 Class: 09/26/2013

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

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

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

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

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

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

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

More Related