Using Queries


A query is really nothing more than a question you ask Access about your data. Access does not understand questions the way you generally ask them, so you must ask your question with its special query format. A query differs somewhat from a filter. A query is the request that produces a subset of data. A filter, on the other hand, just temporarily hides certain data from your Access views so that you can see only a subset of data from a table.

A query is an object, just as tables and forms are objects. Therefore, you see the Queries object page on the Database window, and you create, edit, and execute queries from this page. As with other objects, queries have names that you give them. Many queries are nothing more than named filters; however, filters go away when you are finished with them, whereas you can recall a query later by its name . If you want to reapply a filter, you must reproduce it.

graphics/lightbulb_icon.gif

Although you cannot name filters, you can turn a Filter by Form request into a named query. When you enter the Filter by Form request, click the Save as Query toolbar button. Access prompts you for a query name and stores the filter as a query. Often, creating a named query from a Filter by Form is faster than generating a new query from scratch if you only want to create a simple query that filters records.


Queries are often much more advanced than filters. A query enables you to specify selected records from a table or from another query. You can create a query that selects records and fields from multiple tables. The data subset that a query generates often becomes a table-like Datasheet view from which you can report. You can build a query that extracts certain records and fields from three tables, for example, and then generate a report from those extracted records and fields.

Not only can you create a query that extracts fields and records, but also you can specify the exact order of the resulting data subset, sort the subset, and use powerful extraction criteria to select data based on very specific requirements.

Once you generate a query, you can save that query just as you save tables and reports . The saved query will contain all the instructions necessary to once again generate the data so that you do not have to build that query again.

graphics/bookpencil_icon.gif

The created data subset is called a dynaset .


Creating a Query with the Query Wizard

Although you can build a query from scratch, the Query Wizard can do the dirty work for you in most cases.

Access includes these four query wizards:

  • Simple Query Wizard Extracts fields from one or more tables and from other queries.

  • Crosstab Query Wizard Creates a worksheet-like query that summarizes field values and cross-tabulates matching values.

  • Find Duplicates Query Wizard Creates a data subset from two or more tables or queries that contain matching values in one or more fields that you select.

  • Find Unmatched Query Wizard Creates a data subset from two or more tables or queries that contain no duplicate records.

You use the Simple Query Wizard often because of its general-purpose design. When you create a new query by selecting Queries from the Database window's object list and then clicking New on the toolbar, Access displays the New Query dialog box (shown in Figure 19.2).

Figure 19.2. Begin creating your query in the New Query dialog box.

graphics/19fig02.jpg

graphics/alarmclock_icon.gif

When you create a new query, a list of the four query-based wizards appears and you can select from that list. In addition, a fifth entry appears labeled Design View . The Design View entry is misleading because it is not a wizard but a query-building screen from which you must build the query from scratch without the help from any wizard.


To Do: Build a Query with the Simple Query Wizard

Follow these steps to build your first query using the Simple Query Wizard:

  1. After opening your database, click Queries in the Database window.

  2. Click the Database window's New button to open the New Query window.

  3. Select the Simple Query Wizard from the New Query dialog box and click OK. Access starts the wizard and displays the Simple Query Wizard screen (see Figure 19.3).

    Figure 19.3. Use the Simple Query Wizard screen to generate your first query.

    graphics/19fig03.jpg

  4. Select a table or an existing query that holds the data you want the query to extract. (You can build queries from tables or from other queries.) Access displays fields from the selected table (or query) in the Available Fields list.

  5. Select one or more fields and click the button labeled >. Access includes these fields in the resulting query data subset. If you want the query to extract all customer names and balances from a customer table, for example, select those two fields and click > to send them to the Selected Fields list. The Selected Fields list is the query's resulting structure and holds a description of the data subset that the query eventually produces.

  6. Optionally, select another table or query from the Tables/Queries drop-down list and add more fields to the Selected Fields list. If Access prompts you to create a relationship between the tables, click Yes to create the relationship. Access relates the tables automatically if you created the relationship link elsewhere. You are now building a query that extracts data subsets from multiple sources. If you send the wrong field to the Selected Fields list, select the incorrect field and click < to remove it. To remove all your selected fields and begin again, click the button labeled <<.

  7. Click Next to select either a detail or summary query. A detail query includes every field of every record; a summary query does not show duplicate selected records and includes summary statistics if you select them by clicking the Summary Options button.

  8. Click Next and select a title for the query. Access bases the default name for the new query on the first selected table or existing query.

  9. Click Finish to complete the query. Access builds the query and displays the selected records from the query in the Datasheet view. When you close the Datasheet view, you see the new query listed on the Queries page of the Database window. Again, you can often create a filter that produces the same extracted record subset as a query, but you can later re-extract queries by name instead of rebuilding them from scratch as you must do with filters.

The resulting query is just a smaller table, a subset of the original table, that your query generated. For the first time, you have the power to extract records from multiple tables to create a dynaset, or subset, of your database records.

You can also create a new form (using one of the form wizards) to display the results of a query. When you created a new form in Hour 18, "Entering and Displaying Access 2003 Data," you knew only how to create forms from single tables. If multiple tables contain data you want to display in a form, however, create a query to extract from the two tables and base the form on that query.

graphics/alarmclock_icon.gif

To synchronize a multiple-table query, all tables must have a common field (such as a customer number), or you must use advanced Access commands to relate the two tables in some way. Without a relationship, such as a common field, the query cannot combine the fields from the two tables.


Access does not save your query results, just the query structure. Therefore, if you want to see a data subset twice, you must open the Database window's Queries page, select the query, and click Open to generate the query extraction once again. Although the extraction requires a little time to generate (usually the speed is negligible unless the tables contain many records), the query is always fresh. If you change one value in any table and open the query again, your most recent table edit appears in the query.

Another advantage of generating the query every time you need to use the data subset is that Access does not have to store the data twice (once in the source tables and again in the query).

graphics/lightbulb_icon.gif

If you edit data from the resulting query's Datasheet view (or from the query's Form view), Access updates the data in the original tables. Suppose that you want to edit the pay rate for every employee who works in your company's Northeast division; just create a query to extract only the Northeast division employees, make the edits, and close the query. Find and edit the Northeast employees ' pay rates without the other employee records getting in the way.


Using the Query Design View

When you create or edit a query from scratch, you can use the Query Design view (such as the one shown in Figure 19.4).

Figure 19.4. The Query Design view enables you to create powerful queries.

graphics/19fig04.jpg

Although the Query Design view looks somewhat strange at first, the view's design is logical. The top of the Query Design view contains the source tables and queries, and the bottom of the Query Design view displays the criteria (the query-selection commands).

You use the Query Design view when you must

  • Create an advanced query that the query wizards cannot create.

  • Edit an existing query.

After you learn to create a query with the Query Design view, you will also understand how to use the Query Design view to edit existing queries.

To Do: Create a Query from Scratch

To use the Query Design view to create a query, follow these steps:

  1. Select Queries in the Database window.

  2. Click the New button to open the New Query dialog box.

  3. Select Design View and click OK to open the Query Design view. Access displays the Show Table dialog box that contains all your table and query details (similar to the one shown in Figure 19.5).

    Figure 19.5. Select from your database's tables and queries for the new query.

    graphics/19fig05.jpg

  4. Click the table or tables that you want to include in the query and then click Add. As you add tables to the query, Access displays a new table in the Query Design view showing in the background. If you want to base your new query on another query, click the Queries tab to display a list of them from which you can choose and then add the selected query. If you want to add both tables and queries, click the Both tab to display all your database tables and queries and then select the ones you need.

  5. Click Close to close the Show Table dialog box.

You have yet to build the entire query, but you have selected tables, existing queries, or both on which to base your new query. Next, enter the query's criteria so that Access knows which records and fields to select from your tables and existing queries.

graphics/alarmclock_icon.gif

It's easy to accidentally add the same table twice to a query. If you do, just click the "extra" table in the upper pane of the query and press Delete.


Figure 19.6 shows the start of a new query. This query extracts from two tables, one named Customers and one named Employees. The line connecting the tables' common field, City, appears when you drag your mouse between the common field, as you do when you want to create a relationship. You might recall from the preceding section that Access must base queries on related tables and queries, and the common field relates the two tables.

Figure 19.6. This query can now extract from two related tables.

graphics/19fig06.jpg

graphics/lightbulb_icon.gif

Think of the top half of the query's design window as starting the request, "Given these tables and queries" and the lower half of the window as finishing that request, "extract all data that meets these conditions." The Query Design view contains your instructions when you want Access to extract data from one or more tables or queries and display the result in a table subset.


Each column in the Query Design view's lower half contains the resulting query's fields. Therefore, if you want the resulting data subset to contain four fields, you fill in four of the Query Design view's lower columns. These columns contain the instructions that indicate to the query how to extract the data from the table, tables, query, queries, or combinations of tables and queries that you have specified for the query.

To Do: Work with Complex Queries

To complete the query, follow these steps:

  1. Click the first column's row labeled Table and select the table that contains the resulting query's first field. If you want the query's data subset to start with a field from the Tenants table, for example, you select Tenants from the drop-down list.

  2. Click the first column's row labeled Field and select the field that you want to place first in the resulting data subset. You might select Customers from the list, for example.

  3. If you want to sort the resulting query's subset based on the first field, select either Ascending or Descending from the row labeled Sort. You don't have to sort on the first field that you add to the query; you can sort on any field that you add. The query sorts all the resulting data based on the value of the field by which you sort. If you sort on two or more fields, Access sorts the data in the leftmost Sort column first.

  4. Leave the Show option checked if you want the field to appear in the resulting query's data subset. Generally, you want the field to appear. If you want to sort the subset on a field but not send that sort field to the resulting extracted subset, uncheck the Show option for that field.

  5. Click the first column's row labeled Criteria and enter a criterion. If you type a value, such as JJ1 , Access extracts only those records with a field containing JJ1. You can continue adding criteria values beneath the first one. You can type the values JJ1 , BR1 , BR2 , and BE1 for five rows of criteria (still in the first column). It is like asking Access to extract only those tenants whose Tenant ID is JJ1, BR1, BR2, or BE1, for example. If the field is a text-data type, Access encloses the criteria in quotation marks (like the fields shown in Figure 19.7). Access encloses dates inside pound signs ( #1/6/1898# , for example) if you enter dates in the criteria.

    Figure 19.7. This query must match several criteria values for Customer ID.

    graphics/19fig07.jpg

  6. It gets fun here. Instead of selecting the field from the second column's drop-down list labeled Field, drag the field name from a table in the upper half of the Query Design view (such as the Order Date field in the Orders table). Access automatically fills in the table and field name in the second column of the query! (You might also double-click a field name from the table in the upper portion of the Query Design view to use that field name in the query.)

  7. Enter the selection criteria for the new field. The criteria indicate exactly how you want to pull records from the table. If you want to extract all the records (all that fall within the criteria of the first field that you have entered), leave the second field's criteria blank. You can further limit the extraction by entering an additional criterion for the second field. Suppose that you not only want customers with the IDs listed in the first criteria, but you also want to limit the selection to any of those five who have an order date of January 6, 2005. You enter #1/6/2005# for the criteria.

  8. Continue adding fields that you want to appear in the resulting query. When you execute your query, these fields appear in the resulting table.

As you work with the query, you might want to add more tables. Add the tables in the Query Design view by selecting Query, Show Table. Add as many tables (or queries) as you prefer to the query from within the Query Design view, but remember that the tables and queries must relate somehow. Remove a table or query from the Query Design view by right-clicking the table name and selecting Remove Table.

graphics/bookpencil_icon.gif

You can use all the cut, copy, and paste methods that you learned earlier in the book for the query-extraction fields. You can move a field from one location to another by selecting, cutting, and pasting it, for example. You can also resize column widths by dragging the field column edges, and you can change any value by clicking that value in the Query Design view.


If you really want to get fancy, use the relational operators in Table 19.1 to add to your extraction power.

Table 19.1. Access's Relational Operators

Operator

Description

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

<>

Not equal to

=

Equal to (not needed for simple matches)

Suppose that you want to include customer-order details in a query, but you only want to include orders with more than 19 units. Enter >19 for the Quantity field's criterion to limit the selection to those records that match the other criteria and that have order quantities of more than 19 units. The relational operators work with numbers , text values that fall within a range of words, and dates.

The Between keyword is useful when you want to extract values that fall between two other values. If you type Between #1/1/2005# And #1/31/2005# for a date criterion, for example, Access extracts only records whose date falls between 1/1/2005 and 1/31/2005 (including the days 1/1/2005 and 1/31/2005).

Access uses an implied Or when you specify multiple criteria. Instead of typing five separate Customer IDs such as 101 , 102 , 103 , 104 , and 105 , for example, you could enter 101 Or 102 Or 103 Or 104 Or 105 . (Of course, entering Between 101 And 105 is even easier.)

Be sure to save your query when you finish with it. Click the Query Design view's Close Window button and name the query so that you are able to refer to it later. When you select the query and click Open from the Queries page of the Database window, Access runs the query, extracts the data, and displays the result in your Datasheet view.

Although Access queries are useful and not extremely complex, the screens might seem daunting at first for Access newcomers. The Access online help screens provide many examples and explanations that you might want to peruse to familiarize yourself better with database queries.



Sams Teach Yourself Microsoft Office 2003 in 24 Hours
Sams Teach Yourself Microsoft Office 2003 in 24 Hours
ISBN: 0672325535
EAN: 2147483647
Year: 2003
Pages: 272
Authors: Greg Perry

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