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

graphics/table_joining.gif 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.

graphics/04fig10.jpg

graphics/sort_ascending.gifgraphics/sort_descending.gif 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.

graphics/running_query.gif 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.

graphics/04fig11.jpg

Jet SQL

Access QBE automatically converts query design of Figure 4.10 into the following Jet SQL statement:

 SELECT Customers.CompanyName, Orders.OrderID, Orders.OrderDate,       [FirstName] & " " & [LastName] AS Salesperson   FROM  Employees     INNER JOIN (Customers       INNER JOIN Orders       ON Customers.CustomerID = Orders.CustomerID)     ON Employees.EmployeeID = Orders.EmployeeID   WHERE ((Year([OrderDate])="1997"))   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 Jet QBE features are powerful; many developers use Access to generate the SQL statements needed by Visual Basic, C++, and Java programs.

graphics/table_joining.gif 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.

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 Jet SQL statement after tweaking the SELECT and WHERE clauses:

 SELECT TOP 100 PERCENT dbo.Customers.CompanyName,     dbo.Orders.OrderID, dbo.Orders.OrderDate,     dbo.Employees.FirstName + ' ' +     dbo.Employees.LastName AS Salesperson   FROM dbo.Employees     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) = 1997)   ORDER BY dbo.Customers.CompanyName, dbo.Orders.OrderID 

The TOP 100 PERCENT clause is needed to permit an ORDER BY clause in a view; prior to adding the TOP keyword in SQL Server 7.0, creating sorted views wasn't possible. 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. The only change to the WHERE clause is removal of the double-quotes surrounding 1997. Figure 4.12 shows the design of the T-SQL query generated by pasting the preceding statement into the da Vinci query pane.

Figure 4.12. Pasting a Jet 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 Jet query of Figure 4.10.

graphics/04fig12.jpg

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.


graphics/new.gif

Figure 4.13 shows the SQL Server view of the query design of Figure 4.12 and the preceding T-SQL statement. The only difference between the view and the Jet query result set is the format of the OrderDate column, which is Short Date for SQL Server and Medium Date for Jet. You can change the date display by selecting an entry from the Format list on the Columns page of the view's Properties dialog.

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.

graphics/04fig13.jpg



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net