In the Real World Optimizing Multitable QueriesChapter 9's "In the Real World Optimizing Query Design" section discusses the art and science of query design to optimize the presentation of information and query performance. The single-table query design recommendations apply equally to multitable queries. This chapter is one of the longest in the book because of the importance of multitable SELECT and SQL aggregate queries in production database applications. Joins are fundamental to relational databases. You're likely to find that more than 75% of the queries you create require at least one join, and a substantial percentage need two or more joins. SubdatasheetsAccess 2003's subdatasheet feature is useful for browse-mode editing of related tables, but not much else. Browse-mode is a term for editing table data in a datasheet. As mentioned earlier in the chapter, it's easy for inexperienced users and data-entry operators to make errors when editing datasheets, so minimize or eliminate updatable datasheets and subdatasheets in production Access applications. Instead, use multitable forms the subject of Chapter 15, "Designing Custom Multitable Forms" that display a single record of the base table in the main form and show related records in a subform. Note Browse-mode editing operations in multiuser and client/server environments are the bane of database administrators (DBAs) because they require multiple database connections and create a substantial amount of network traffic.
For more discussion of the browse-mode method and other alternatives, see "In the Real World Alternatives to Action Queries," p. 514. Aggregate QueriesAggregate queries generate summary data that's critical for decision-support analysis. Aggregate queries offer quick and easy totaling of orders, sales, and other financial data for one or more time periods. Aggregation methods create large-scale data warehouses and smaller data marts, which are used in online analytical processing (OLAP) applications. Experience with SQL aggregation techniques is a necessity for understanding OLAP methodology. Tip Apply "reasonableness" tests against every summary query you design. Testing becomes increasingly important as the significance of data to others grows. It's very embarrassing to find that you provided data for 1998 when your manager needed 1999 information. Become familiar with trends in the summary data generated by your queries, and compare new query result sets with previous values. If the comparison shows unexpected changes (good or bad), run a simple summary query for one or two periods to verify your data. If the summary data still fails the reasonableness test, you must review the underlying detail data (called drilling down). Familiarity with the detail data you summarize is job insurance when your manager says the "numbers don't look right to me." Crosstab QueriesMost executives prefer the crosstab formats for time series and other comparative financial analyses. Access's Crosstab Query Wizard does a respectable job of generating simple crosstab queries for you. Designing crosstab queries that are more complex than the Wizard can handle requires that you first gain experience writing conventional summary queries. One of the primary issues with Jet crosstab queries is that the Jet SQL reserved words, PIVOT and TRANSFORM, both of which you need to generate crosstab queries, aren't available in SQL Server's T-SQL or any other client/server SQL dialect. Thus you can't automatically upsize Access 2003 Jet applications that include crosstab queries to ADP with the Upsize Wizard. (You can't automatically upsize UNION queries, either.) ADP substitute SQL Server views for select queries; you create views by writing T-SQL SELECT queries for views, functions, or stored procedures. ADP are the subject of Chapter 20. If your data source is a client/server RDBMS, you can use linked server tables with the Crosstab Query Wizard. Alternatively, you can generate the summary query on the server with an Access passthrough query, and then use the Jet query result set as the data source for the crosstab query. Passthrough queries, which are more network-efficient than linked tables, are one of the subjects of Chapter 19.
Figure 11.79. The Visual Basic 6.0 CrosstabUpsizer program (Crosstab.exe) detects Jet crosstab queries in a database and generates SQL Server views, tables, and stored procedures that emulate the Jet queries.
|