Displaying Data with Queries and Views
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 Access SQL for Access 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 notand often do notrepresent 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 Access or SQL Server tables.
Figure 4.10 is an example of Access's QBE implementation for Access databases, called Query Design View. You add tables to the queryin 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.
Access's Query Design View for Access 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 in Figure
4.11. You can save the query for later reuse as a named Access QueryDef(inition) object in the database.
These are the first 16 of the 408 rows of the query result set returned by executing the query design of Figure
Access SQL - Access QBE automatically converts the query design of Figure
4.10 into the following Access SQL statement:
SELECT Customers.CompanyName, Orders.OrderID, Orders.OrderDate,
[FirstName] & g g & [LastName] AS Salesperson
INNER JOIN (Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
ON Employees.EmployeeID = Orders.EmployeeID
ORDER BY Customers.CompanyName, Orders.OrderID;
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 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 Access 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 an Access QueryDef. As with Access QueryDefs, you can execute a query on an SQL Server view.
Despite their common ANSI SQL-92 heritage, SQL Server won't execute most Access SQL statements, and vice versa. Copying the preceding Access 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 Access SQL flavor into T-SQL when you paste, but you receive errors when you try to run the query.
T-SQL - T-SQL uses + rather than & to concatenate strings, uses a single quote (') as the string delimiter, and requires a numerical instead of a string criterion for the YEAR function. Here's the T-SQL version of the preceding Access SQL statement after the SELECT and WHERE clauses have been tweaked:
SELECT TOP (2147483647) dbo.Customers.CompanyName,
dbo.Employees.FirstName + e e +
dbo.Employees.LastName AS Salesperson
INNER JOIN dbo.Customers
INNER JOIN dbo.Orders
ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ON dbo.Employees.EmployeeID = dbo.Orders.EmployeeID
WHERE (YEAR(dbo.Orders.OrderDate) = 2006)
ORDER BY dbo.Customers.CompanyName, dbo.Orders.OrderID
The TOP modifier is needed to permit an ORDER BY clause in a view; prior to the addition of the TOP keyword in SQL Server 7.0, creating sorted views wasn't possible. The da Vinci query parser adds the TOP 100 PERCENT modifier if an ORDER BY clause is present. However, TOP 100 PERCENT ... ORDER BY doesn't sort SQL Server 2005 views. Replacing 100 PERCENT with a large integer (<= 2147483647) sorts the view.
The dbo. prefix to table and field names is an abbreviation for database owner, the default owner for all SQL Server databases you create as a system administrator. Figure
4.12 shows the design of the T-SQL query generated by pasting the preceding statement into the da Vinci query pane.
→ For more information on the da Vinci toolset, see "Exploring SQL Server Views."
Pasting an Access SQL statement into Access's version of the da Vinci query design tool and making a few minor changes to the T-SQL statement results in an SQL Server view equivalent to the Access query of Figure
Note - Access 2007 automatically creates the NorthwindCS database the first time you open the NorthwindCS.adp file after installing SSX from the distribution CD-ROM.
→ For detailed instructions on installing SQL Server Express and NorthwindCS.adp, see "SQL Server 2005 Express Edition SP2 Setup," and "Exploring the NorthwindSQL Sample Project."
The Datasheet View of the SQL Server view generated by the preceding SQL statement is identical to the Access query's Datasheet View shown in Figure
In the Real WorldWhen and Why Learn Relational Theory?
A corollary of the Law of Preservation of Matter is, "Everything has to be somewhere." Most books about Microsoft Access deal with relational database design theory in the first few chapters, and this latest of the 11 editions of Special Edition Using Access is no exception. Most previous editions categorized this subject as an "Advanced Access Technique" that appeared toward the end of the book.
Understanding relational database design requires familiarity not only with the objects that make up the database, but with the use of these objects. After you're comfortable with table and query basics, have a feeling for form and report design, and gained an introduction to Web-based database applications, you're probably better prepared to delve into the arcana of relational algebra, such as normalization rules. That's why the previous editions didn't include this chapter in the first part of the book.
After you've gained experience working with the sample relational databases in Access and this book, their design appears intuitive and entirely logical. Experienced database designers envision even the most complex business processes as collections of related tables. So, it's somewhat surprising to many that Dr. Codd's relational database theory originated in 1970, well after the development of complex network and hierarchical architectures. If you're embarking on the road to relational database development, however, you're not likely to find database design topics at all intuitive.
An argument in favor of moving the relational database design topic to the beginning of the book is that many readers decide to use Access to accomplish a specific task that involves creating a special-purpose database. In this case, you advance through the book's chapters using the sample databases as examples rather than learning models. If you're using Access as a learning tool, starting with database design and implementation also makes the structure and relationships of the tables in the sample databases of the book more meaningful.
Increasing sales of desktop and client/server RDBMSs has spawned a multitude of booksranging from introductory tutorials to graduate-level textson relational database design theory and practice. Michael J. Hernandez's Database Design for Mere Mortals (Addison-Wesley Developers Press, ISBN 0-201-75284-0, 2003), subtitled "A Hands-On Guide to Relational Database Design, Second Edition," is an excellent resource for folks who want more than this chapter offers in the way of database design guidance. Mike and John L. Viescas, a well-known Access writer and developer, are co-authors of SQL Queries for Mere Mortals (Addison-Wesley, ISBN 0-201-143336-2), which delivers thorough coverage of SQL SELECT queries for Access and SQL Server users.
If you're serious about getting the most out of Access 2007, consider purchasing a copy of Mike's book or browse the bookstore shelves for titles on relational database design. Your investment will pay handsome dividends when you're able to create the optimum design to start, instead of attempting to restructure a badly designed database after it's grown to 20 or 30 tables containing thousandsor millionsof rows.