The Simple Query Wizard

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:

  1. Locate the Northwind.mdb database. By default, it’s installed in C:\Program Files\Microsoft Office\Office10\Samples. (If you don’t have this database installed, you’ll need to install it from your Microsoft Office XP CD-ROM.)
  2. Make a copy of the database. (Some of the procedures in this chapter will modify the database, so it’s a good idea to make a copy.)
  3. Open the copy of the Northwind database.
  4. On the Database window’s Objects bar, click Queries, and then click New on the Database window toolbar. (Or on the Database toolbar, click the arrow next to the New Object selector, and then click Query.)
  5. In the New Query dialog box, double-click Simple Query Wizard. If you get a message stating that this feature isn’t currently installed, let Access install it.
  6. In the Tables/Queries drop-down list, select Table: Customers.
  7. In the Available Fields list, double-click CompanyName, ContactName, ContactTitle, and Phone (in that order) to add these fields to the Selected Fields list, as shown in Figure 9-3. Then click Next.

    figure 9-3.use the simple query wizard to select fields to be included in the query.

    Figure 9-3. Use the Simple Query Wizard to select fields to be included in the query.

  8. On the last page of the wizard, name the query qryPhoneNumbers, as shown in Figure 9-4, and click Finish.

    figure 9-4. name the query qryphonenumbers.

    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.

    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.

Figure 9-6. The qryPhoneNumbers query displayed in Design view.

note


The expression qry is the standard tag (prefix) for queries in the Leszynski Naming Convention (LNC). You can use qry as the tag for all queries, or you can give each query type its own tab; the tags for the basic database objects are listed in Table 20-4 of Chapter 20, "Customizing Your Database Using VBA Code."

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:

  • AS. Creates a clause that specifies an expression or a value and the field name associated with it (sometimes called an alias).
  • DISTINCTROW. Excludes duplicate records from the query.
  • FROM. Creates a clause specifying the table or query from which fields are taken for the query.
  • GROUP BY. Specifies the field used to group records in a summary or crosstab query.
  • ORDER BY. Creates a clause that specifies the order in which query records are to be sorted.
  • SELECT. Creates a clause containing a list of fields to be included in the query.
  • UNION. Combines two sets of records into a single set.
  • WHERE. Creates a clause with a condition (or set of conditions) for filtering query records.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

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