3 4
If you’re new to queries, you should start your adventure with the Simple Query Wizard. Although the wizard won’t limit data, it will retrieve data and evaluate expressions, creating what’s known as a "simple" query. For example, you could create a simple phone list for all your customers, but you couldn’t limit that list to customers with a particular ZIP code.
To use the Simple Query Wizard to create a phone list for the Northwind customer database (the database that comes with Access), follow these steps:
Figure 9-3. Use the Simple Query Wizard to select fields to be included in the query.
Figure 9-4. - Name the query qryPhoneNumbers.
The results of the query, shown in Figure 9-5, contain just the fields specified in the Simple Query Wizard.
Figure 9-5. This simple query returns just the specified fields in the Customers table.
Changing Field Order and Column Order
When you work with Access wizards, you select fields from the Available Fields list and move them to the Selected Fields list. The wizard then bases the form, report, or query on the fields you moved to the Selected Fields list, displaying the fields in the same order as they appear in the Selected Fields list. If this order isn’t suitable for the new object, select the fields in the Available Fields list in the order in which you want them to appear in the finished form or report.
You can also change the column order in a query by dragging a column to a new location in the design grid. To move a column, click on the column selector (the gray bar at the top of the column) to highlight the column. Then click again on the column and drag it to its new location. While you are dragging the column, a vertical gray line indicates the location where the column will be dropped.
You’ve seen the results of the wizard’s work. Now it’s time to go back and review the query in Design view and SQL view. Figure 9-6 shows the query in Design view (click the View button on the Database toolbar), which lists the query’s data source—theCustomers table—in the upper part of the window and identifies each selected field in the design grid.
Figure 9-6. The qryPhoneNumbers query displayed in Design view.
note
To view the Jet SQL statement for the qryPhoneNumbers query, click the arrow to the right of the View button, and then select SQL View. The SQL view displays the following Jet SQL statement:
SELECT Customers.CompanyName, Customers.ContactName, Customers.ContactTitle, Customers.Phone FROM Customers;
The use of the SELECT clause indicates that this is a select query. The statement then identifies the query’s four fields, separating them with commas. The FROM clause identifies the data source (Customers), and the semicolon indicates the end of the statement.
Common SQL Keywords
SQL keywords typically appear in all capital letters. (Operators have only their initial letter capitalized.) The most commonly used SQL keywords are listed here: