So far, this chapter has concentrated on designing relational databases and their tables, and adding or altering data. SQL SELECT queries return data to Access, but you don't need to write SQL statements to display data in forms or print reports from the data. Access has built-in graphical tools to automatically write Jet SQL for Jet databases and T-SQL for SQL Server databases. Access's query tools use a modern implementation of query-by-example (QBE, an IBM trademark). QBE is a simple method of specifying the tables and columns to view, how the data is sorted, and rows to include or exclude. Note As mentioned earlier in the chapter, fields become columns and records become rows in a query. This terminology is an arbitrary convention of this book and not related to relational database design theory. The reason for the change in terminology is that a query's rows and columns need not and often do not represent data values stored in the underlying tables. Queries can have columns whose values are calculated from multiple fields and rows with aggregated data, such as subtotals and totals. Linking related tables by their primary and foreign keys is called joining the tables. Early QBE programs required defining joins between tables; specifying table relationships automatically defines joins when you add records from two or more related Jet or SQL Server tables. Figure 4.10 is an example of Access's QBE implementation for Jet databases, called Query Design view. You add tables to the query, in this case, Northwind's Customers, Orders, and Employees tables. As you add the tables, join lines indicate the relationships between them. You drag the field names for the query columns from the table lists in the upper pane to the Field row of the lower pane. You also can specify the name of a calculated column (Salesperson) and the expression to create the column values ([FirstName] & " " & [LastName]) in the Field row. The brackets surrounding FirstName and LastName designate that the values are field names. Figure 4.10. Access's Query Design view for Jet databases uses graphical QBE to create queries you can store in the database.
Selecting Ascending or Descending in the Sort column orders the rows in left-to-right column priority. You can restrict the display to a particular set of values by adding an expression in the Criteria column. Running the query returns the result set, part of which is shown by Figure 4.11. You can save the query for later reuse as a named Jet QueryDef(inition) object in the database. Figure 4.11. These are the first 16 of the 408 rows of the query result set returned by executing the query design of Figure 4.10.
It's obvious that using QBE is much simpler than writing SELECT queries to concatenate field values, join tables, establish row selection criteria, and specify sort order. Access's Jet QBE features are powerful; many developers use Access to generate the SQL statements needed by Visual Basic, C++, and Java programs. The da Vinci QBE tool for creating T-SQL views is similar to the Jet Query Design view, but has an additional pane to display the T-SQL statement as you generate it. You add tables to the upper pane and drag field names to the Column cells of the middle pane. An SQL Server view is the client/server equivalent of a Jet QueryDef. Like Jet QueryDefs, you can execute a query on an SQL Server view. Despite their common ANSI SQL-92 heritage, SQL Server won't execute most Jet SQL statements and vice-versa. Copying the preceding Jet SQL statement to the Clipboard and pasting it into the SQL pane of the query designer for the NorthwindCS sample database doesn't work. The da Vinci designer does its best to translate the Jet SQL flavor into T-SQL when you paste, but you receive errors when you try to run the query.
For more information on the da Vinci toolset, see "Exploring SQL Server Views," p. 819. Note Access 2003 automatically creates the NorthwindCS database the first time you open the NorthwindCS.adp file after installing MSDE from the distribution CD-ROM.
For detailed instructions on installing MSDE and NorthwindCS.adp, see "SQL Server 2000 Desktop Engine Setup," p. 47 and "Exploring the NorthwindCS Sample Project," p. 808.
Figure 4.13. This Access Datasheet view of an SQL Server view is identical to the Jet version of Figure 4.11, with the exception of the display format of the OrderDate column.
|