Trying the Simple Query Wizard

The Simple Query Wizard is aptly named; it's capable of generating only trivial select queries. If you don't have a numeric or date field in the table on which you base the query, the Wizard has only two dialogs one to select the table(s) and fields to include and the other to name the query. Following are the characteristics of the Simple Query Wizard:

  • You can't add selection criteria or specify the sort order of the query.

  • You can't change the order of the fields in the query; fields always appear in the sequence in which you add them in the first Wizard dialog.

  • If one or more of your selected fields is numeric, the Wizard lets you produce a summary query that shows the total, average, minimum, or maximum value of the numeric field(s). You also can include a count of the number of records in the query result set.

  • If one or more of your selected fields is of the Date/Time data type, you can specify summary query grouping by date range day, month, quarter, or year.

Tip

Use Crosstab queries for grouping records with numeric values, especially when you're interested in returning a time series, such as multiple monthly, quarterly, or yearly totals or averages. Crosstab queries deliver greatly enhanced grouping capability and show the query result set in a much more readable format compared to that delivered by the Simple Query Wizard. Chapter 11 shows you how to take maximum advantage of Jet SQL's powerful crosstab queries.


Creating a Simple SELECT Query

Northwind.mdb's Orders table has a Currency and several Date/Time fields, so it's the best choice for demonstrating the Simple Query Wizard. To give the Wizard a test drive with the Orders table, do the following:

  1. graphics/a_single_queries.gif Display Northwind.mdb's Database window and click the Queries shortcut.

  2. Double-click the Create Query by Using Wizard shortcut to open the Simple Query Wizard's first dialog.

  3. Select Table: Orders in the Tables/Queries list. All fields of the Orders table appear in the Available Fields list.

  4. Select the OrderID field in the Available Fields list and click the right-arrow (>) button to add OrderID to the Selected Fields list and remove it from the Available Fields list. Alternatively, you can double-click the field to add to the query.

  5. Repeat step 4 for the CustomerID, OrderDate, and Freight fields. The first Wizard dialog appears as shown in Figure 9.1.

    Figure 9.1. You select the source of the query either a table or a query in the first dialog of the Simple Query Wizard.

    graphics/09fig01.gif

  6. Click Next to open the second Wizard dialog that lets you select between detail and summary queries. Accept the Detail option (see Figure 9.2).

    Figure 9.2. The second Wizard dialog, which appears only for data sources with numeric or date fields, gives you the option of displaying all records or generating a summary query.

    graphics/09fig02.gif

  7. Click Next to open the final dialog (see Figure 9.3). Rename the query to qryOrders1 or the like, and click Finish to display the query result set in Datasheet view (see Figure 9.4).

    Figure 9.3. The final Wizard dialog lets you name the query. This book uses a naming convention, called Hungarian notation, which adds a two- or three-letter prefix (qry for queries) to Jet object names, except names of tables.

    graphics/09fig03.gif

    Figure 9.4. The Wizard's query result set substitutes captions for the field names you selected in steps 4 and 5, and the customer name for the CustomerID lookup field. Queries inherit table properties, such as captions and lookup fields, from the data source.

    graphics/09fig04.gif

  8. graphics/sql.gif Open the toolbar's View button and choose SQL View from the list to open the SQL window, which displays the Jet SQL version of the query (see Figure 9.5). Alternatively, press Ctrl+> to move from Datasheet to SQL view.

    Figure 9.5. The SQL window displays the Jet SQL statement that the Simple Query Wizard generated from your selections in steps 4 6.

    graphics/09fig05.gif

  9. Close the SQL window and save your changes if you altered the query's layout.

Jet SQL

The SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.Freight FROM Orders; statement generated by the Wizard is an example of a simple SQL SELECT query. The Orders. prefixes specify the query's source table name.

The SELECT keyword indicates that the query returns records; by tradition, SQL keywords in Jet and T-SQL are capitalized. Field lists contain field names, separated by commas. The FROM clause, FROM tablename, specifies the query's data source. Jet SQL uses the semicolon to indicate the end of a query; like the square brackets, the semicolon isn't necessary if the query includes only one complete SQL statement.

If you want to test the Simple Query Wizard's capability to base a query on another query and check the Wizard's summary query capabilities, do the following:

  1. graphics/a_single_queries.gif Return to the Database window and double-click the Create Query by Using Wizard shortcut to open the Simple Query Wizard's first dialog. Select Query: qryOrders1 or Orders Qry in the Tables/Queries list.

    Note

    Jet calls a query whose source is a query, rather than a table, a nested query. You sometimes see the term subquery incorrectly applied to a nested query. A subquery is an SQL statement for a query within a query.

  2. Add only the OrderDate and Freight fields to the Selected Fields list.

    Tip

    Include only the field(s) by which the data is grouped (OrderDate) and the numeric value(s) to be summarized in a summary query. If you add other fields, such as OrderID, every record appears in the summary query, and you don't obtain the summary you're seeking.

  3. Click Next to open the second Wizard dialog (refer to Figure 9.2). Select the Summary option and then click Summary Options to open the identically named dialog. Mark the Avg check box to calculate the average freight cost and mark the Count Records in Orders Qry check box to add a column with the record count for the group (see Figure 9.6).

    Figure 9.6. If you select a Summary query, you must specify one of the functions in the Summary Options dialog.

    graphics/09fig06.gif

  4. Click OK to return to the second Wizard dialog and then click Next to move to the third Wizard dialog. The Wizard has detected the OrderDate Date/Time field and offers you the choice of date grouping; select Quarter (see Figure 9.7).

    Figure 9.7. Summary queries with a field of the Date/Time data type open an additional Wizard dialog that lets you group the result set by a date interval.

    graphics/09fig07.gif

  5. Click Next to open the last Wizard dialog and replace the default query name, Orders Qry Query, with a more descriptive name, such as qryOrdersSummary.

  6. Click Finish to execute the summary query. The query result set appears as shown in Figure 9.8. Open the SQL window to display the SQL statement that generates this considerably more complex query (see Figure 9.9).

Figure 9.8. The query result set displays the average freight charge and number of orders for each quarter within the range of dates for which data is available.

graphics/09fig08.gif

Figure 9.9. The SQL statement for the Wizard's summary query belies the simplicity of the steps required to create the query.

graphics/09fig09.gif

graphics/troubleshooting.gif

For problems with attempted updates to a summary query, see the "Non-Updatable Summary Queries" topic of the "Troubleshooting" section near the end of the chapter.


Jet SQL

The SQL statement for the summary query, which is a bit advanced for this point in your SQL learning curve, requires formatting for better readability:

    SELECT DISTINCTROW       Format$([qryOrders1].[OrderDate],'\Qq yyyy')         AS [OrderDate By Quarter],      Avg(qryOrders1.Freight) AS [Avg Of Freight],      Count(*) AS [Count Of qryOrders1]    FROM qryOrders1    GROUP BY Format$([qryOrders1].[OrderDate],'\Qq yyyy'),     Year([qryOrders1].[OrderDate])*4+     DatePart('q',[qryOrders1].[OrderDate])-1; 

DISTINCTROW is a Jet SQL keyword that isn't required in this query. The Format$ function determines the appearance of the first column (Q# YYYY). AS specifies the caption (alias) for the column. Avg is the aggregate function you selected in step 3. Count(*) adds the third column to the result set. Jet treats queries as if they were tables, so the FROM clause specifies the source query. GROUP BY is the clause that specifies the date grouping range you chose in step 4 to calculate the values in the second column.

Square brackets ([]) surround query column names such as OrderDate by Quarter that have spaces or punctuation symbols, which aren't permitted by the SQL-92 specification. In some cases, the Jet query analyzer adds square brackets where they're not needed, as in [qryOrders1].[OrderDate].

Avg, Year, DatePart, and Format$ are VBA functions executed by the Jet expression service. The next chapter shows you how to apply these functions to queries. SQL-92 and T-SQL don't include DISTINCTROW and VBA functions, but do support the COUNT function.

Summary queries more commonly called aggregate queries are a common element of decision-support applications that deliver time-based trend data to management. Aggregate queries also are the foundation for graphical data analysis, which is one of the subjects of Chapter 12, "Working with PivotTable and PivotChart Views." PivotTables and PivotCharts must be based on queries to present meaningful information. PivotTables have built-in aggregation features, so you can use detail queries as PivotTable data sources.



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