Access 97/2000: Queries I

• What is a Query?
• Common Types of Queries
• Views in the Query Window
• Two Ways to Design Your Own Queries
• Using the Query Design Window
• Viewing a Dynaset
• Establishing a Sort Order
• Specifying Criteria
• Parameter Queries

What is a Query?

A query is a way of asking questions about data stored in your tables. Your query design tells Access exactly which data to retrieve. Queries allow you to look at selected data from one or more tables without viewing all of the fields in each table, and without having to save all of your data over again. They make it easy for you to combine two or more table fields into a single field, to summarize data, to use criteria to select and sort data, and to add, update or delete large groups of records. The only parts of a query that Access stores are its design parameters. The data displayed in a query is called a dynaset (dynamic dataset) or a recordset. Unless you design the query to save the dynaset as a new table or part of an existing table, Access will not store it.
 
 

Common Types of Queries

For the following examples, imagine that you own and operate a mail order business that sends out catalogs. To make your mailings more efficient, you have created a database to keep track of customer information. The database consists of two tables. Table One contains in separate fields the customers' First Name, Last Name, Street Address, City, Zip Code, Phone Number and Customer ID#. Table Twoís fields are Customer ID#, Order #, and Amount of Purchase.

Select Query

Select queries are the most common, and you will use them to select or extract specified data from your tables. For example, if you want to extract from Table One only the names of catalog recipients who live in Chapel Hill, choose ìChapel Hillî from all entries in the City field. Once you have mastered the select query, every other query in Access is a simple transformation of what you already know.

Parameter Query

A parameter query is a type of select query that asks you to input the criteria you want used to extract data. For example, after you designed a query to select Chapel Hill residents from Table One, you might want to modify it to allow you to input any city name each time you query. This change would create a parameter query.

Note: Parameter queries will be explained in more detail later in this document.

Action Query

An action query makes changes to many records in a table at once. For example, if you found that Table One had 2,000 Chapel Hill residents with an incorrect Zip Code, an action query would select the incorrect values and change them to correct ones. This way you donít have to edit each individual record. You can also use action queries to make a new table or to append records to an existing table.

Crosstab Query

A crosstab query summarizes data and presents it to you in an easy-to-read format. A select query may simply provide too much information. If your data can be numerically summarized, then a crosstab query can help access the data more sensibly. For example, you want to see how much money each of the recipients on your mailing list was spending when ordering from your catalog. The easiest way to do that is by designing a crosstab query that would show you amount of purchase from Table Two and names from Table One.

Note: Action queries and the Crosstab query are the focus of the Access 97/2000: Queries II .

Views in the Query Window

The query window has four views: Design View , Datasheet View , SQL View , and Print Preview . In Design View you can create and modify the design of a new or existing query, using graphical tools. In Datasheet View you see the data retrieved by your query in a spreadsheet format. If you know Structured Query Language (SQL), SQL View allows you to examine and modify the statements that underlie your query. In Print Preview you see how your query data will look in print.

Two Ways to Design Your Own Queries

Query Wizards

Wizards simplify the process of creating your own query, but they are very limited. With Query Wizards you can design the following types of queries:

• Simple Query. Creates a simple, straightforward select query.
• Crosstab Query. Organizes data for a summary appearance.
• Find Duplicates Query. Finds duplicate records in a table or query.
• Find Unmatched Query. Finds all records in one table that donít have related records in another table.

To Create a Query Using Query Wizards:

1. In the Database window, choose Query from the Objects list on the left side.
2. Select the New button.
3. Choose the Query Wizard that you desire.
4. Follow the directions as they are presented to you on the Cue Cards to create the type of query you want.

Do-It-Yourself (The Bob Vila Method)

For many queries you want to design, Query Wizards will only be a starting point, or will not help at all. To be flexible, you need to know how to create queries without resorting to Wizards.

To Create a Query Without Query Wizards:

1. In the Database window, click the Query button.
2. Choose the New button.
3. Choose Design View . Access will then open a Select Query window (you can tell the type of query by looking at the top of the query window) and display the Add Table dialog box, which lists the tables and queries in your database.
4. Select the table that contains the data you want to add to your query by clicking on it, then choosing the Add button. You can also double-click on the table. If you want to select more than one table, hold down the <Ctrl> key and click on each table you want to add. Then choose the Add button. Access will display a field list for each table you select in the query window. Note: The field list may be obscured by the Add dialog box.
5. Choose the Close button. If you have added tables that use the same field name and data type as earlier tables, you will see lines in your query design window linking the tables.

Using the Query Design Window


 

Matching Fields and Linking Tables

Access automatically creates joining lines between two tables if you created a relationship between the tables in the relationships window (see Access 97/2000: Tables. Your query is essentially inheriting the properties of a larger object in your database. You can, however, add or remove these relationships among your data on a query-by-query basis. It works much like the relationships window. To add a link, drag and drop a field from one table to another. To remove a link, click the proper line and hit the delete key on your keyboard. You will notice that Access uses the symbol ì 1î for the primary or source table and the ì ?î symbol for the secondary side. This represents the ìoneî and ìmanyî of the ìone-to-manyî relationship.

Query By Example Grid

After you add the tables to your query, a field list for each table appears in the upper half of the queryís Design window. The lower half of the window is known as the Query By Example (QBE) Grid . This grid is where the actual fields of your query appear. You can adjust the size of the QBE grid by clicking the mouse pointer on the line that separates the two portions and dragging.

The QBE grid shows the fields and properties that make up this queryís dynaset. The Field cell indicates the field that is being queried. The Table cell shows from which table the field is taken. The Sort cell tells you how the information in that field will be ordered in the query. The Show cell indicates whether the field will be shown in the queryís datasheet. For example, you sort an address book by last name, but because you have already combined first and last names as an expression in separate QBE row, you might choose not to show the last name field. Criteria cells indicate how you will limit the search for the field in question. You can place criteria on each field being queried. You should remember that the selection process will follow the criteria from left to right on the QBE grid and that this may change the results of your query.

Adding Fields to the QBE grid

The field list in the upper portion of the query design window displays the fields available for your query. To add a field to the query, select the field in the appropriate field from your table and drag it to a cell in the Field row of the QBE grid . You can also double-click on the field in order to add it to the QBE grid . (You can even click within the Field cell in the QBE grid to select your field!) To add more than one field simultaneously, hold down the <Ctrl> key and select all the fields you want to add, and then drag them to a Field cell. Repeat this procedure until all the fields you want are added to the QBE grid . If you would like to add all the fields from a table or query to the grid, then simply click on the ì*î in the tableís field list and drag it to the QBE grid . This action means that all fields in the table will be added to the query.

Rearranging, Inserting, and Deleting Fields

Editing field selections is almost as easy as adding fields. You can move existing fields, insert new fields, or delete current fields by dragging and dropping.

To move a field or fields:

1. Select the field by clicking the column selector above the field name.
2. Click and drag the column selector to its new location. If you watch the lines separating the columns, the line where the field will be repositioned becomes darker and thicker.

To insert a field:

1. From the field list, select the field you want to insert.
2. Drag the field from the field list to a column in the QBE grid . If you drop the field on top of a pre-existing field, the new field will be added to the left of the old one.
3. If you added the new field to a blank column at the end of the QBE grid , move it by selecting and dragging its column selector to its desired location.

To delete a field:

1. Select the field to be deleted by clicking the column selector.
2. From the Edit menu, select Delete (or press the <Delete> key). You can delete all of the fields in the grid by choosing Clear Grid from the Edit menu.

Renaming a field

To rename a field in your query, click to the left of the first letter in the field name in the QBE grid . Then, enter the new name followed by a colon. The new name is displayed as a datasheet column heading and will appear on any forms based on the query.
 

Viewing a Dynaset

After you run your query, you will want to look at the dynaset it generates. The easiest way to do this is in Datasheet View . Select Datasheet from the View menu or click the Datasheet View button on the toolbar. To look at the dynaset in another way, run the query by clicking the Run button or selecting Run from the Query menu. With this method you should be careful when using a query other than Select Query . Access will actually run your query and manipulate your data as it has been instructed. Only Select Queries will end up in the Datasheet view.

Establishing a Sort Order

Possibly the most useful task that a database program can do is sort your data. Access sorts data alpha-numerically. Creating a query that sorts data can be useful when you want to display data in a form or report, or even if you want that data to appear in order in a combo box or list box on a form. You create a query that sorts the data and then use this query as the data source in a form, report, or box. To specify a sort order in the QBE grid , click the Sort cell for the field you want to choose, then click the arrow in the cell. You then need to select from the list Ascending (0-9 and A-Z) or Descending (9-0 and Z-A). You can also specify a sort order for more than one field, like asking Access to sort by both First Name and Last Name fields in a mailing list. The sort order will start sorting from the left to the right of the QBE grid from left, so make sure that the Last Name field in the above example occurs on the grid prior to the First Name field.

Note: You cannot sort on Memo or OLE Object fields.

Specifying Criteria

Selecting a criteria-based subset of your data is useful for managing a database. You can specify certain criteria to be met for a record to be placed in the dynaset. When you specify criteria for a query, you use an expression. The expression tells Access which records to include in the queryís dynaset.

Entering Expressions

Access gives you a great deal of flexibility in how you can enter expressions. To specify criteria as either a simple word, like ìFaulkner, î or as a complex definition, ìBetween 100 And 500,î you can type the expression in the Criteria cell in the fieldís row on the QBE grid . For example, you want to find only works written by authors whose last names are Faulkner, then you could type any of these in the Criteria cell: Faulkner, =Faulkner, ìFaulknerî , or =ìFaulknerî. After you have entered your criteria and moved to another cell, Access checks behind you and display the criteria in a standard format, ìFaulknerî in this case. For the complete list of rules on expressions, consult Accessís on-line Help.

Using the Expression Builder

Access includes a helpful tool for creating complex expressions called the Expression Builder . It is opened by clicking the Build button on the toolbar, once the cursor is in a QBE grid Field or Criteria cell. The Expression Builder then gives you the option of entering the expression by typing it yourself or by selecting from lists of common operators, references, and other expression elements. If the expression is simple and you have learned Accessís syntax rules, it is often easier and faster to type the expression into the cell.

Access uses Boolean operators to carry out its queries. You will usually base every select query on these And, Or, and Not operators. The And function allows you to specify two or more criteria for selection. If you search a database for ìpeople living in Chapel Hill And people who own dogs,î the only matches found will be those that fit both criteria. The Or operator lets you specify two or more criteria, and your data must meet at least one of the criteria. In an address book database, you might use this operator to find people living in ìChapel Hill or Durham or Raleigh.î The Not operator simply eliminates certain criteria from consideration. If you wanted to search your address book for everyone who does not live in the 27514 Zip Code, then you would type ì Not 27514î as your criteria. Commonly, you will use some combination of these operators. To specify several criteria for just one field, the And and Or operators are your best bets.

Using ìOrî

 

If you were using a card catalog and wanted to see which books have been written by authors with the last names Faulkner and Joyce, in the Criteria cell for Last Name you would enter: ìFaulknerî Or ìJoyceî. Access provides a helping hand for designing ì Orî queries. Instead of entering the full statement just mentioned, you could have entered ìFaulknerî in the Criteria cell, then moved down one cell and entered ìJoyce.î Access assumes that each extra Criteria row under the first one uses an Or operator.

Using ìAndî

 

If you were concerned about limiting this search to just William Faulkner, then you would need to use the And operator over two fields: First Name and Last Name . If you enter your criteria into the fieldsí Criteria cells (on the same row), Access assumes that you want it to use the And operator as it reads the fields in QBE grid from left to right.

There will be times when you need to use the And and Or operators at the same time. You can combine the above operations. Access reads across each row of criteria from left to right, then starts moves down row by row. In order to combine your searches, you would need to know which items should go on the same row. To add anyone with the middle name of ìScottî to the previous search, move down one row in the Criteria cells and enter ìScottî in the Middle Name field.

Using Wildcards

If you want to select records that contain certain patterns of characters or cannot remember an exact data entry, use the wildcards ( * and ?) in your search. The question mark ( ?) stands for any single character, while the asterisk ( *) stands for any number of characters in the same position. For example, if you enter ì*thî in a Criteria cell, both ì125thî and ìPerthî will be selected. The format for entering wildcards differs slightly from entering complete data. If you enter ì*th,î Access inserts the Like command before the entered expression.

Ranges of Records

You can identify a range of records in an expression by using the Between . . . And operator or the comparison operators ( <,>,<>,<=, and >=). The following examples show various operators.

 

Expression	Meaning
>123	Numbers greater than 123
<123	Numbers less than 123
>=ìFaulknerî	All text from Faulkner to the end of the alphabet
<=ìFaulknerî	All text from the beginning of the alphabet to Faulkner
Between 10 And 20	All numbers from 10 to 20, inclusive
<>ìFaulknerî	All text before and after Faulkner
Like "Faulk*"	All text beginning with Faulk
Not Like ìFaulk*î	All text not beginning with Faulk. (The inverse of <Like ìFaulk*î>.)

Blank Field Searches

You might also want to know when a field contains no information. For example, while running a mail order business, you might want to see which customers who have received catalogs but have not placed an order. To select this value, find the ìAmount of Purchaseî field and enter Is Null as its criteria. To select only those customers who have made purchases, you could use the Is Not Null statement in the same field.

Note: Is Null is a good way to check your data in general, making sure you havenít forgotten to fill out any fields .

Creating Calculated Fields

When you design fields to be included in a query, you are not limited to those in the tables and queries that you are basing the query on. You can create new calculated fields by using expressions. For example, in a table for calculating annual interest on a loan, you might have a field called ìPrincipalî and an interest rate of 8.25%. With this information, you could create two different calculated fields: Interest and Total Amount Due. To calculate the total interest, the expression you enter is: [Principal]*0.0825. To calculate Total Amount Due, enter: [Principal]+[Interest]. In each case, the brackets indicate that you are using a field as part of the expression.

Parameter Queries

 

People often run and design the same kinds of queries, but unfortunately, they do not always use exactly the same criteria. You may want to design your queries to alter the criteria each time you run it. This kind of query is called a parameter query . When you run a parameter query, you will not need to open the queryís Design window and make changes.

To Create a Parameter Query:

1. Open a new query without using query wizards.
2. Add the tables or queries on which youíre basing the new query. Add the desired fields to the QBE grid .
3. In the Criteria cell, under each field that you plan to change with each query, enter a prompt statement enclosed in square brackets ([ . . . ]). Access will display this prompt whenever the query is run.
4. Choose the Datasheet view from the Toolbar.
5. When Access displays the Enter Parameter Value dialog box, enter a value and click OK.