CS224 Chapter 5 Notes

• ❑ Query Basics: Role and Type
• ❑ A query is a question. The answers to these questions can help us manage the organization's operating environment
• ❑ Asking the right questions is a key to management sucess
• ❑ Our job is to make sure these queries are translated into a format that the database can handle efficiently
• ❑ There are two basic types of queries: select queries and action queries
• ❑ Select Queries - selects and displays records for one more more tables
• ❑ Creating a Simple Select Query
• ❑ Simplest kind requests a list of only a few of the many available fields in a table.
• ❑ In access, you have to work with the QBE screen for any query
• ❑ QBE Example - pgs. 157-161
• ❑ Sorting Options
• ❑ Output Formatting: The Properties Box
• ❑ Example - pgs. 163-164
• ❑ Data Organization: Grouping
• ❑ Data summaries are often the basis for transforming data into information.
• ❑ Example - pgs. 165-166
• ❑ Parameter Queries
• ❑ queries whose output extent is defined by specified constraints
• ❑ ex. you want to find employee records for employees whose last name is Smith.
• ❑ Parameter query output generally varies with the circumstances of its applications
• ❑ Example - pgs. 167-169
• ❑ Segmented searches - using wildcards - pg. 170
• ❑ You can also place a constraint on a non-string field, such as date, money and so on. - pgs. 171-174
• ❑ Date Input and Output Formatting
• ❑ Some output formatting is essential if the queries are to yield the information required by your end users
• ❑ Ex. listing employee birthdays by month - November vs. Nov vs. 11
• ❑ Remember the formatting doesn't generate the output. It only controls the output presentation
• ❑ Example pgs. 174-182
• ❑ Queries Based on Multiple Sources
• ❑ To get the best-structured and most comprehensive information out of the database, we usually wind up using multiple tables
• ❑ We may use the results of other queries that are themselves based on multiple tables or mix and match queries and tables as data sources
• ❑ Example pgs. 182-184
• ❑ Queries Based on Outer Join Relationships - pgs. 185-188
• ❑ Crosstab Queries
• ❑ One of the most frequently used and useful data organization tools is the cross-classification table. This presents a two-dimensional summary of the available data
• ❑ A powerful tool to summarize huge amounts of data.
• ❑ A crosstab query is used to create a cross-classification table
• ❑ Example - pgs. 188-193
• ❑ Action Queries
• ❑ The Update Query
• ❑ Allows us to modify many table records at a time - very useful
• ❑ Example pgs. 194-197
• ❑ Delete Query
• ❑ Constructed just like the update queries
• ❑ Example - pgs. 198-200
• ❑ Append Query
• ❑ These add data to tables
• ❑ May be controlled through the use of parameters
• ❑ Example pgs. 201 - 204
• ❑ Make Table Queries
• ❑ This can perform adds, deletes and updates without an existing table structure
• ❑ This query makes the appropriate table structure based on the fields that are placed on the QBE grid
• ❑ Example - pgs. 204-205
• ❑ Query Wrap-up
• ❑ Focus in this chapter was on technique, rather than application
• ❑ In combination with other Access tools, queries play a crucial role in developing a well-organized and well-functioning system