Web based School

Chapter Four
Queries and Filters

Queries and filters are primary ways to find and organize your data. A filter is saved with a table and can't be reused for other objects unless you save it as a query. Queries can be used to answer questions themselves, but can also be used as the foundation for forms and reports.

Creating queries and filters involves tasks such as sorting data, choosing data (setting criteria), and selecting tables and fields. Queries are more versatile than filters and you can use them to mass update, delete, and add records to a table. You can also do substantial calculations with queries.

NOTE: For more on the calculation capabilities of queries see the Calculations part of this guide.

Most of the following procedures start in Query Design View. You can enter this view by selecting an existing query on the Database window and choosing the Design button. You can also get to the design view by selecting New on the Queries tab of the Database window and choosing Design View.

Action Query: Append Query

An append query provides a method for adding records to one or more tables. This is useful when you want to transfer records from one table, or set of related tables, to another. You can also use the append query to write data to the same fields in matching records between two tables. For notes about action queries, see "Action Query: Create" first.

Steps

1. In the Database window, highlight the table or query for the basis of the append query and choose Query from the New Object button on the toolbar. The New Query dialog box is displayed. Click OK to accept the default.

2. Click the Query Type button on the toolbar and select the Ap pend Query option. Enter the name of the target table in the Table Name box. Click either Current Database or Another Database to specify the target table; then choose OK.

3. Drag the fields from the Field List that you want to append to the Query Design grid, along with any fields you will use for selection criteria.

4. Enter the criteria for creating the result set in the Query Design grid.

5. Click the View button on the toolbar to preview the records to append.

6. Click the Run button to append the records to the table(s) you specified.

NOTE: If you want the target table to automatically add new AutoNumber values, do not drag the AutoNumber field onto the Query Design grid. To copy the AutoNumber values from the source table, drag the AutoNumber field onto the Query Design grid. If the target table already has AutoNumbers that match the source table, the Append Query will not append those records when you Append to the AutoNumber field.
Also, when all fields have the same name in both tables, drag only the asterisk for the table to the Query Design grid.

Action Query: Create

An action query collects records that meet your search criteria, and changes the data contained in those records in a single step. Access allows four different action queries: delete, update, append, and make-table query. A delete query removes the groups of records returned from your query from your table. An update query alters the information contained in your result set and writes the changes back to your table. An append query adds the records from your result set to your table. Finally, a make-table query creates an entirely new table from your result set.

CAUTION: Because action queries modify your data, it is a good idea to first create a select query with the fields and criteria you want. View the results of the query to make sure you have the correct records. Then, turn the select query into an action query by selecting one of the options on the Query Type button on the toolbar.

First follow the steps to create a select query. (See "Query: Create with Design View" or "Query: Create with Wizard.")

Steps

1. In your query's Design View, enter criteria, add a sort order, and create any calculated fields. Click the Datasheet View button to make sure your criteria are OK.

2. Return to the design view by clicking the Design View button.

3. Turn the query into an action query by clicking the drop-down arrow on the Query Type button and choosing one of the four action query types (Make-Table, Update, Append, Delete).

4. If prompted, identify the target table. Click the Run button to perform the action query.

5. Click the Save button on the toolbar. Enter a name in the Save dialog box, then choose OK.

The action query will appear on the Queries tab of the Database window. To indicate that it will run when opened, the icon next to the name includes an exclamation point.

CAUTION: Be careful when you double-click or choose the Open button for an action query. The query runs every time. You might want to delete action queries when you no longer need them to avoid accidentally updating your data. You can also hide the query by choosing the hidden object property. (See "Database Object: Properties" in the File Management part of this guide.)

Action Query: Delete Query

The delete query enables you to remove a set of records from the table you specify. If you have already created a relationship that enforces referential integrity with cascade deletes, you will only need to delete records from parent table. (See "Relationships between Tables" in the Table and Database Design part of this guide.) The records from the related child table will delete automatically.

Steps

1. In the Database window, highlight the table or query for the basis of the delete query and choose Query from the New Object button on the toolbar. Click OK.

2. Drag any fields you need to identify records into the design grid. Then click the Query Type button on the toolbar and select the Delete Query option.

3. Enter any criteria for deleting records, and they appear under Where in the Delete cell.

4. Click the View button on the toolbar to preview the records to be deleted.

5. Click the Run button to delete the records from the table.

After closing the dialog box, the records in your result set are removed from the table you specified.

Action Query: Make-Table

The make-table query enables you to create a table for export; it provides the basis for a report; it provides a method for making backups; and it gives a snapshot of your data at a point in time. The make-table query also enables you to improve the performance of your forms and reports by working from a set of records stored to disk that don't have to be retrieved from a large data set or from a network. You can also copy a table from the Database window and if necessary, delete or add any additional fields you want. (See "Database Object: Copy" in the File Management part of this guide.)

Steps

1. In the Database window, highlight the table or query for the basis of the make-table query and choose Q_uery from the New Object button on the toolbar. Click OK.

2. Drag any fields you need to identify records into the design grid. You can also add an additional table and any other fields from the second related table if you want to use fields from more than one table. Then click the Query Type button on the toolbar and select the Make-Table Query option.

3. Enter a name in the Table Name text box, select either the Current Database or Another Database option button, then choose OK.

4. Enter any criteria for choosing records in the Criteria row. Click the View button on the toolbar to preview the records to be included.

5. Then Click the Run button to create the new table.

After dismissing the dialog box indicating that a new table will be created, Access writes your result set to disk as a table in the database you specified.

Action Query: Update Query

An update query can make global changes to selected records in a table or a set of related tables. This type of query is useful for replacing information quickly.

A common example for an update query would be where you would increase the price for an item. In the Update To cell under the price field in the Query Design grid you would type the expression [Price]*1.02 (to increase the price by 2%). For more information on calculations, see "Calculated Fields: Queries Create" in the Calculations part of this guide.

Steps

1. In the Database window, highlight the table or query for the basis of the update query and choose Query from the New Object button on the toolbar. Click OK.

2. In the Query Design View, click the down arrow next to the Query Type button on the toolbar, then select the Update Query option.

3. Drag the fields from the Field List to the Query Design grid that will be in the new table. Enter the criteria for your result set, and any sort you want.

4. In the Update To cell, enter the expression or value to be used as a replacement for the field(s) selected.

5. Click the View button to see a list of records that will be updated. Then click the Run button on the toolbar.

After closing the dialog box, Access replaces your old data with the new values in the result set you specified.

Criteria: Blanks, Nulls, and Empty

When you create select or action queries or use the Advanced Filter/Sort option to create a filter for a table, you often might want to see which records have no values. For example, you might want to find all addresses without zip codes so you can look them up before you do a mailing. You may also want to find which fields have zero-length strings.

When you use a null field in a calculation, the result of a calculation is null. You can use the NZ function to convert nulls to zeros. (See "Blanks, Empty, and Null: Work With" in the Calculations part of this guide.)

NOTE: For a field to be capable of accepting zero-length strings, that field property must be set to Yes. (See "Data: Blanks, Nulls, and Zero-Length Strings" in the Table and Database Design part of this guide.)

Steps

1. In Design View of a query, click in the criteria row under the desired field.

2. To find all records with no entry, enter Null. Access will translate this to Is Null. If you want to find all records except blanks, type Not Null (which Access enters as Is Not Null).

3. To find records with zero-length strings, enter "" (two quotes with no space).

4. In the Field cell of the design grid, if you are calculating values that have nulls in some of the fields, enclose each field name in the NZ([Fieldname]) function.

5. Choose the Datasheet View button to see the results of the query or choose the Run button to update records if the query is an action query.

TIP: To display zeros for a numeric field that has nulls, type the following in the Field cell of the Query Design grid: NZ([Fieldname])+0.

NOTE: When you sort ascending on a field with Nulls, the records with the Null fields display first.

Criteria: Date

When you are working with date fields, there are some criteria you might want to keep in mind. You can choose to show just today's date or a specific month, day, or year. Another option is to include a range of dates.

Steps

1. In Design View of a query or filter, click in the criteria row under the desired field.

2. If you want to enter a specific date, type the date. Regardless of the format for the date, you can enter the date in its simplest format for the criteria: For example, 1/1/98. When you leave the criteria cell Access translates this to #1/1/98#.

3. If you want to find all records with today's date, type Date(). If you want all those after today's date, type >Date(). If you want all those up to and including today's date, type <=Date().

4. If you want all records between two days use the Between And expression as in the example: Between 1/1/97 and 3/5/98.

5. To find all records for a specific month, year or day, use the * (asterisk). 1/*/98 finds all records in January 1998. */*/92 finds all records in 1992.

6. Choose the Datasheet View button to see the results of the query or choose the Run button to update records if the query is an action query.

For date functions, see also "Functions: Date" in the Calculations part of this guide.

Criteria: Multiple

If you are searching for data that meets more than one criteria, you can use the Query Design grid or the keywords AND and OR.

To enter criteria, be sure you are in the Design View of a query or filter.

Steps

1. To set criteria for multiple fields where all criteria must be met (for example, City is Denver and Address includes Broadway), type the expression for the first field, move to the second field in the same criteria row and type the second expression. Repeat for all necessary fields.

2. To set criteria for multiple fields where any criteria can be met, type the expression for the first field, move down to the next criteria row (labeled Or) and type the second expression under the second field. Move down again if there is a third criteria.

3. If you are looking for multiple possibilities in the same field, type in one criteria cell and type OR between each expression (for example: CO or NE). If you have many entries you can use the IN keyword (for example, IN(CO, AL, NE, KS).

4. If one field must meet two criteria use the AND keyword (for example >5 AND <10).

5. Choose the Datasheet View button to see the results of the query or choose the Run button to update records if the query is an action query.

Criteria: Numeric

When you are looking for numeric data, there are some handy expressions for use with numeric criteria. To enter criteria, be sure you are in the criteria cell of the Design View of a query or a filter.

Steps

1. Type the number in the criteria cell to see just this value.

2. Type <Number or <=Number to see all numbers less than or less than or equal to the number.

3. Type >Number or >=Number to see all numbers greater than or greater than or equal to the number.

4. Type Between Number1 and Number2 to find all numbers between two numbers (including both numbers).

5. Type >Number1 and <Number2 to find all numbers between two numbers (not including both numbers).

6. Choose the Datasheet View button to see the results of the query or choose the Run button to update records if the query is an action query.

Criteria: Specify

You can enter selection criteria into filters and into queries. Queries are more generally useful in that they can work with related tables of records and direct the output of the result set to several different places. Filters apply only to the current table or query and cannot be used elsewhere.

Steps

1. Move to the criteria row and enter the expression into the design grid of the Query or Filter.

2. If you cannot see the entire criteria, press Shift+F2 to expand the cell into a window. Choose OK when finished editing the criteria.

3. If you want to enter complicated criteria, click the Build button on the toolbar and use the Expression builder.

Access allows for a wide range of values and expressions, and the Query Design View is a powerful tool for composing queries and applying criteria for selection. For some of the criteria types, see the other Criteria sections in this part. (See also "Expression: Create with Builder" in the Calculations part of this guide.) Access translates the query into SQL and applies it to the appropriate data sources (tables and grids) that the query operates on.

Criteria: Text

Most of your data will be text data type. There are some handy expressions for use with text criteria. To enter criteria you need to be in the criteria cell of the Design View of a query or a filter.

Steps

1. Enter in the complete text match you want. Access puts quotes around the expression.

2. Include the asterisk (*) wild-card character for any text you are not sure of. A single asterisk can take the place of any number of characters. You can also use multiple asterisks in one expression. Jo*ns*n will find Johnson, Johanson, Jonsen, and Johnsen. Access enters Like "Jo*ns*n" in the criteria cell.

3. Enter Not and then the text you don't want to match. Not NY will find all states except NY.

4. Use the question mark (?) as a wildcard character for one letter. For example, C? would find CO, CT, and CA.

5. Choose the Datasheet View button to see the results of the query or choose the Run button to update records if the query is an action query.

Criteria: Use Another Field

In some instances you want to use one field in a criteria for finding records based on another field. For example, you might want to find all records where the Promised Date is less than the Shipped Date and send the clients an apology.

Steps

1. In Design View of a query or filter, move to the criteria row of the field.

2. Type any operators and then the second field's name in square brackets. For example, <[ShippedDate].

3. Choose the Datasheet View button to see the results of the query or choose the Run button to update records if the query is an action query.

Duplicates: Find

One of the most frustrating parts about managing data is trying to dispose of duplicates. Access can show which records have exact duplicates. You can then decide which record to delete. Before you create a primary key on a table with existing data, you also need to search for and remove duplicates.

Steps

1. On the Queries tab of the Database window, choose New and double-click Find Duplicates Query Wizard.

2. On the first step of the wizard, double-click the table or query.

3. Double-click the field (or combination of fields) that will identify duplicates; choose Next.

4. Double-click any additional fields you want to display in the query; choose Next.

5. Give your query a name and click the Finish button.

The query will list only duplicated records in the table. You can modify the fields or delete the unnecessary records. (See "Records: Delete" in the Database Essentials part of this guide.)

TIP: Access cannot show you which records have close matches. For example, if you have 491 Fox St and 491 Fox Street, Access will not show these as duplicates. You might want to develop standard rules for entering data (always spell out or use abbreviations, for example).

Duplicates: Remove

You can delete duplicates one at a time from the result of a Find Duplicates Query Wizard. (See "Duplicates: Find".) However, if you have many duplicates, you might want to use the following procedure. This procedure does not allow you to choose which record of the two duplicates you want to delete.

Steps

1. In the Database window select the table and click the Copy and Paste buttons on the toolbar. When prompted copy the structure only and give the new table a name.

2. Open the new table in Design View, hold down Ctrl, click the field(s) with duplicated information, and click the Primary Key button on the toolbar. Save and close the new table.

3. Select the first table and choose Query from the New Object button. Include all fields from the table in the query grid by double-clicking the asterisk in the Field List.

4. Choose Append Query from the Query Type button and enter your new table name.

5. Click the Run button to run the query and answer Yes to the prompts.

This query works because the primary key in a table cannot be duplicated. After you view your second table to make sure the information is correct, you can delete the first table and rename the second. (See "Database Object: Delete" and "Database Object: Rename" in the File Management part of this guide.)

Filter Data: Advanced Filter

The most complex filters you can create enable you to both filter and sort records in a single operation for a single table or query. The Advanced Filter/Sort window is similar to the Query Design View in construction in that you work in a design grid where you specify the criteria used to filter your records and the sort order. The Advanced Filter/Sort feature can operate on tables or queries, but cannot provide related tables.

Steps

1. Open a table, query, or form in the Datasheet View, or a form in the Form View.

2. Choose Record, Filter, Advanced Filter/Sort.

3. Enter the criteria and sort you want in the design grid.

4. Click Apply Filter on the toolbar.

Filter Data: By Form

If you want to select a set of records in a datasheet or a form, the simplest way to do this is to set a filter. A filter is a single set of criteria that can be applied to your data set. When you apply another filter to a result set from a previous filter, you narrow your result set even further.

Access remembers your last filter in a session and lets you reapply it at any time. If you apply a filter to a table or form, Access remembers that filter until you apply a new one. Filters applied to queries are not entered into the query grid, but can be applied later separately. You can also sort filtered records. You might find that filters provide much of the find and query capability you need in your work.

Steps

1. Open a table, query, or form in the Datasheet or Form View.

2. Click the Filter By Form button on the toolbar to open a Filter By Form window.

3. Click the field you want to filter by and enter the selection criteria that records must match to be returned in a result set.

4. To enter a value to search for, select that value from the list in that field; or enter the value manually.

5. To enter a value of a check box, option button, or toggle button, click that button. Entering a value in two fields on the same line in the Look For tab (see the bottom of the window) requires both values to be matched in the result set.

6. To perform a filter based on alternative values, click the OR tab at the bottom of the window and enter additional criteria. Click the Apply Filter button on the toolbar to perform the selection.

NOTE: Check boxes display three states: checked (on), not checked (off), and mixed state (grayed). Make sure you place a check mark into the condition you want. Also, you can select a field based on the conditions Is Null or Is Not Null.

You can reapply the filter by clicking the Apply Filter button at a later time. You can use the Remove Filter button to remove your current filter.

Filter Data: By Selection

You can filter the records shown in a form, subform, or datasheet by applying a Filter By Selection. When you click the toolbar button, all records that match your selected field display. This procedure is very simple to use, but more limited than Filter By Form. (See "Filter Data: By Form.")

Steps

1. Open a table, query, or form in the Datasheet View, or a form in the Form View.

2. Select the record that has the value you want to limit in a form or datasheet.

3. Click the insertion point in the field (this will cause the entire field's contents to be the criteria for the filter).

4. Click the Filter by Selection button on the toolbar.

NOTE: As mentioned in the preceding Step 3 you can click in a field (with no text selected) to have Access use the entire field for the filter. You can also select part of a field starting with the first character to return records where the value in that field also starts with those characters. Another option is to select any value after the first character in a field to return all or part of a value in that field with the same characters.

Filter Data: Filter With Shortcut Menu

Access lets you enter a filter directly into a field on a field's shortcut menu as a filter request.

Steps

1. Open a table, query, or form in the Datasheet View, or a form in the Form View.

2. Right-click a field and enter a value or expression in the Filter For text box; then press Enter.

By successively filtering, you can narrow your result set. You can press Tab while still in the Filter For text box in Step 2 to apply the filter and add a new filter criteria as well.

Filter Data: Save as Query

After working with a filter you might want to use it again under different circumstances. You can save the filter as a query.

Steps

1. Create a filter from any of the methods mentioned in the filter tasks in this part of the guide.

2. Go to the filter design window by choosing Record, Filter, Advanced Filter/Sort.

3. If desired, modify the filter grid and click the Save As Query button on the toolbar.

4. Enter the name of the query in the Save As Query dialog box.

If you want to use the specification from this query in a filter again, go to the filter design grid by choosing Record, Filter, Advanced Filter/Sort, click the Load from Query button and double-click the query name.

This query now appears on the Query tab of the Database window. You can use it like any other query. One difference is that all fields are displayed rather than just the fields that are in the grid. This is because the Output All Fields property is set to Yes.

List of Values

Sometimes you want to just see a list of all the different values in one field with none of the items duplicated. You might want to create a lookup table with these values, in which case you could create this query and then turn it into a Make-Table Query (see "Action Queries: Make-Table Query") and use the table in a lookup column (see "Lookup Column: Create with Wizard" in the Table and Database Design part of this guide).

Steps

1. In the Database window, select the table or query from which you want to find the values. Choose Query from the New Object button on the toolbar.

2. Right-click the mouse button on the query's title bar and choose Properties on the shortcut menu. The Query Properties sheet opens.

3. Change the Unique Values property to Yes.

4. Double-click in the Field List the one field for which you want to see values.

5. Close the Property sheet, save, and run the query.

Query Design: Expand Column

At any point during query design you might not have enough room to see your field or criteria. You can change the column width temporarily to see what you need at the moment, or you can permanently widen the column.

Steps

1. If you permanently want to see a wider column width, move to the gray border above and between two field names. The mouse pointer will be a double-headed arrow. Drag the mouse to the right to increase the column width.

2. If you want to temporarily see the entire entry, press Shift+F2 to open the Zoom window. Choose OK when you are done viewing or editing.

Query Parameters: Prompt for Input

When you want one query to work as many, consider using a parameter query. For example, suppose you need all the same information but want to change the salesperson each time you run the query. When you create a parameter query, Access will prompt you each time you run the query. This is true even when you open a form or report based on this query.

Steps

1. Create the query with all the fields and other criteria you want and open the query in Design View.

2. Move to the criteria grid under the field you want to check. Type an open square bracket, any text you want for the prompt, and a close square bracket. For example, type [Enter the salesperson's name:].

3. You might have more than one prompt and need to change the order of the prompts or define a data type for the prompt (so Access displays a warning if the wrong data is entered in the prompt). Choose Query, Parameters. Enter the exact text of your prompt and choose a data type for each prompt.

4. Click the Run button. Access will show a dialog box with whatever prompt you added. Enter your text.

5. Save and close your query.

Whenever you run the query (including just opening it) Access will display a dialog box with the prompt you created.

TIP: Enter the keyword Like before your prompt if you want to use wildcards.

Query: Add Field

When you edit or build a query you might need to insert fields in the appropriate place on the grid. Open up any query in Design View to add a field.

Steps

1. To add a field at the end of the query grid, double-click the field.

2. To add a field between two existing fields, drag the field name from the list box onto the right field.

3. To add all fields in the grid, first clear the grid if necessary with Edit, Clear Grid.

Double-click the asterisk (*) in the Field List.

4. If you want to add all fields to the grid and you are going to use sort or criteria from any of the fields, double-click the title bar of the list box and drag from one of the fields to the grid.

Query: Add Table

You can use more than one table in the query. In almost all cases the tables need to be related. Open up any query in Design View to add a table.

Steps

1. In Query Design View, click the Show Table button on the toolbar.

2. Double-click each table or query you want to add; choose Close when done.

3. If necessary, draw a join line between the two related fields in the Field Lists. Generally, you will be drawing a line between two fields with the same or similar names (for example, EmpID and Employee ID).

4. Double-click fields from any of the Field Lists to add them to the design grid.

For more details on the joins, see "Tables: Combine with Join" and "Tables: Create a Self Join."

NOTE: If you accidentally add a table twice to the design grid, click in the table and press the Delete key to remove it. Don't forget that you may not see all the tables. Use the scroll bars in the upper part of the grid to see if you have any more tables hiding in the grid.

Query: Change Column Heading Names

When you make calculated columns in a query, you will probably want to add a column name instead of the Expr headings that Access defaults to.

Steps

1. In Query Design View, click before text in any Field cell.

2. Type the new column name and a colon (:).

Query: Create with Design View

Queries are recipes for finding, selecting, and sorting data in tables. Access stores the definition of a query, runs the query, and returns a result set when the query is requested. Queries can be used as the underlying data set for forms and reports. Queries can also be used to select, append, delete, and update records in the database (see the appropriate "Action Query" sections).

You create a new query using either the Query Wizard or the Query Design window. Both are relatively easy to use, though the Query Wizard will walk you through the process step-by-step. The Query Design Wizard provides you with additional options. (See "Query: Create with Wizard.")

NOTE: You can select records by applying a filter and sort those records. Access 97 remembers the last filter you applied without you having to re-enter it (see the Filter tasks earlier in this part). This is an alternative to running some queries.

Steps

1. Click the Query tab in the Database window, then choose the New button. Select Design View in the New Query dialog box; then double-click the table(s) you want from the Show Table dialog box, choose Close.

If you add related tables, Access will automatically create join lines between the tables.
To create a relationship between two tables, drag the key field of the parent tables to the related field of the child table.

2. Double-click any fields from the Field Lists for each table you want as part of the query.

3. Click in the Sort row and select Ascending or Descending for any field that you want to sort.

Fields are sorted in their order from right to left, the left field being the primary sort key.

To re-order the fields, click and drag the column header.

4. Uncheck the Show check box for any field you want to hide in the Query.

Fields can be used in queries that do not appear in the query output.

5. Enter any expression in the criteria cell for that field. Click Save in the Query Design toolbar. Enter the name of the form in the Query Name text box of the Save As dialog box, then choose OK.

See the Criteria sections of this part for examples of criteria. When you run a query, the data you see reflects the data that conforms to your query at runtime. You can update, delete, or append the data in a result set for most queries. (See "Updatable Queries.")

Query: Create with Wizard

The Query Wizard is easier for novices to create queries but provides less flexibility than Query Design View. (See "Query: Create with Design View.")

Steps

1. On the Query tab of the Database window, choose the New button and choose Simple Query Wizard.

2. Choose a table or query from the Table/Query drop-down box.

3. Double-click any fields you want as part of the query. Repeat Steps 2 and 3 for additional tables if necessary; choose Next.

4. If you want to see every record, click the Detail option button.

If you want to only see summaries of the records such as totals or counts, choose the Summary Option button and choose the types of summaries you want; choose OK.

5.On the next choices in the wizard choose any additional options for grouping your data and naming your query; choose Finish when done. Save and close your query.

Query: Delete Field

To remove a field from a query you need to be in Design View. If that field is needed in sorting or criteria, uncheck the Show box in the design grid.

Steps

1. In the query or filter design grid, click the black down arrow mouse pointer on the gray field selector above the field name and press Delete.

2. To remove all fields, choose Edit, Clear Grid.

Query: Description

If you want to explain your query in more detail than the field name allows, you can use the Description property.

Steps

1. If you are in the Database window, right-click the query name, choose Properties, and type the text in the Description text box.

2 If you are in Query Design View and the properties window is not in view, right-click the gray background behind the Field List(s) and choose Properties. The Query Properties dialog box opens On the General tab type the text in the Description text box.

Query: Editing

When you want to edit the design of a query, you need to go into Design View. You might want to add more fields, delete fields, change your criteria, or change other query properties.

NOTE: You can edit the data in most queries the same way you edit a table. (See "Data: Edit" in the Database Essentials part of this guide.)

Steps

1. On the Query tab of the Database window, select the query and choose the Design button.

2. Select any criteria expression and press the Delete key to remove it or type a new expression.

3. Change a field by clicking the drop-down arrow in the field box and choosing a different field name.

4. If you want to use the same fields but a different table, add the other table with the Show Table button and change the table name in the design grid for each field, and then click in the old table Field List and press the Delete key.

NOTE: If the table name row does not appear in the Query Design grid, choose View, Table Names.

Query: Format Field

Often when you create a calculation field (see "Calculated Fields: Queries" in the Calculations part of this guide), the data is not formatted the way you might want. You might also want to change the format of a field to be different than the Format property of the field in the underlying table. (See "Data: Format" in the Table and Database Design part of this guide.)

Steps

1. In Query Design View, right-click the field you want to format and choose Properties.

2. Click in the Format property text box and choose one of the formats from the drop-down list.

You can also type customized formats in the Format box. Some examples include: For a required text character, type @ and the required text characters. For example, if you want the word Unknown displayed when there is nothing typed in the field, or No Value when two quotes have been typed in the field, type @;"Unknown";"No Value"

For dates, type mmm, or mmmm to see just the month (abbreviation or full name) or yyyy to see the year.

NOTE: For text you can also type > to change the entry to be all caps or < for all lower cases. For a description of customized format characters, click in the Format property and press F1.

Query: Hide and Show Columns

Sometime you will need to use a field but not want to see it displayed in the query datasheet.

Steps

1. If you are in Datasheet View, right-click the column and choose Hide Columns from the shortcut menu.

To redisplay the column choose Format, Unhide columns, pick the column, and choose Close.

2. If you are in Design View, uncheck the Show box in the design grid. Recheck the box to display the column again.

Query: Move Column

You can move a column either in the Datasheet View or Query Design View. Once you've changed the column order in Datasheet View for a query, you need to change it in Datasheet View each time rather than depend on Query Design View.

Steps

1. In either Design View or Datasheet View, move the mouse to the column or field header until the mouse pointer becomes a black down arrow. Click and release the mouse to select the column.

2. With the mouse pointer still on the column header (it is now a white arrow), drag the column to the new location.

Query: Properties

There are a number of useful properties that you can set as part of a query. These properties help you control the number of records returned in your view of the result set, optimize performance, and specify other useful options.

Steps

1. Open a query in Design View.

2. Click a field in the Field row, a Field List in the data environment, or elsewhere on the Design View window to select the query.

3. Click the Properties button on the toolbar to display the Property sheet for the selected object.

4. Add your properties or expressions; then close the Property sheet.

Both the query and field have a Description property for notes or comments. The field also has a Caption property that shows as the column header or becomes the default label for forms and reports. The Format property on the field changes the way data is displayed. (See "Query: Format Field.") The field's Input Mask property works the same as for fields in a table. (See "Input Mask: Phone Number and Other Entries" in the Table and Database Design part of this guide.)

Query: Run

You can run a query from Query Design View or directly from the Database window.

CAUTION: Be careful when you run action queries--they will update or delete data. These queries are indicated with an exclamation point (!) as part of their icon in the Database window.

Steps

1. From the Query tab of the Database window, double-click the query name to run it.

2. From the Query Design View, click the Datasheet View button to show the results of a select query or preview the results of an action query.

3. From the Query Design View, click the Run button to execute the action query. This button functions the same as the Datasheet View button for select queries.

4. From Datasheet View, if you want to requery (perhaps you want to redisplay the input box for a parameter query), press Shift+F9.

Query: Show All Fields

No matter which fields are used in the grid, a filter shows all fields. When a filter is saved as a query, all fields also display. When you create a new query through Design View or through a wizard, only those fields you use in the grid (and that have the Show box checked) display in Datasheet View.

Steps

1. Select the query in the Database window and choose the Design button.

2. If the Properties window is not displayed, right-click the gray background behind the Field Lists(s) and the title bar of the query, and choose Properties. The Query Properties sheet opens.

3. Change the Output All Values property to Yes.

Query: Sort Columns

You can apply a sort order to the result set of a query in either Form or Datasheet View. You can also sort the result set of a filter or an advanced filter/sort.

The sort order for a query is usually determined by the Sort row of the Query Design grid. You can sort the query independent of the property, however, with the following steps.

Steps

1. Click the column(s) or field(s) you want to sort on.

2. Click either the Sort Ascending or Sort Descending buttons on the toolbar.

NOTE: If your sort is more complex (with multiple non-adjacent fields) choose Records, Filter, Advanced Filter/Sort and create your filter.

The primary sort key is the leftmost sorted column in the Query Design grid. A sort order is saved with the datasheet or form and reapplied when you open it.

To remove the sort, go into Design View for the query, click the right mouse on the gray background behind the Field List(s) and choose Properties. The Query Properties sheet opens. Delete the text in the Filter By property.

SQL Statements: View

SQL stands for Structured Query Language. This is the underlying language that Access uses to create its queries, filters, and select data for reports. Most of the time you do not have to see the actual SQL statements because Access builds them for you in design grids. In a few cases you have to work with SQL if you want to perform some features of Access (see "Tables: Combine with Union Query" especially).

Steps

1. Open a query in Design View.

2. Click the View button on the toolbar and choose SQL View. The SQL window opens where you can copy or edit the text instructions for the query.

Keywords in the query include the type of query (SELECT, DELETE or, for Append, INSERT INTO), source of data (FROM), criteria (WHERE), and sort (ORDER BY). Fields are indicated by the name of their table, a period, and their field name. If field names include spaces, they are enclosed in square brackets.

Subqueries: Create

Once in awhile you might want to create a query that uses the results of another query to run. One option is to create a query and then use that query as the basis for the next query. Another option is to copy the SQL statement and place it in a Criteria cell.

You might need to find the average price for all products and then find all products that exceed the average price.

Steps

1. If you want to have both query and subquery listed in the Database window, highlight the first query name in the Database window, click the New Object button and choose Query. Create the second query using the first queries fields.

2. If you want to have only one query in the Database window, first create the subquery with only one field and one record as an output (create a Totals Query with no grouping). Display the subquery in SQL view and copy all text. Create the main query and move to the Criteria row under the field. Type any operator (such as =, <, >), then type an open parenthesis, paste the SQL statement, and then type a close parenthesis.

Table Names: Display

When you are designing a query with multiple tables, the grid can be confusing unless you have table names displayed.

Steps

1. In Query Design View, choose View, Table Names to turn the names on or off.

Tables: Combine with Join

When you have two tables that are related to each other (for example, if you have Customers and Orders tables), you might want to see fields from both tables. In the Order table you have a Customer ID but no other customer information. To see the customer name and phone, as well as order information, you can join two tables together in a query.

Steps

1. From the Design View of a query, click the Show Table button to add additional table(s), double-click the child table(s) you want and choose the Close button.

Access will use any relationship you created in the Relationships window.

2. To create a relationship between two tables, drag the field of the parent tables to the related field of the child tables. A line appears between the two tables. Generally, you will be dragging the primary key from the parent table (which is in bold) to a non-primary field in the child table.

3. To set the join properties, right-click the line and choose _Join Properties.

4. The default is the first choice, which only shows records where both joined fields have data. (This is called an Inner Join.)

Choose the second choice to see matched records and all records from the parent table without a match in the child table. (This is called a Left Outer Join.)

Choose the third choice to see matched records and all records from the child table without a match in the parent table. (This is called a Right Outer Join.)

5. Choose OK and finish building the query by adding any fields, sort options, and criteria you want.

6. Click the Run button to run the query.

NOTE: If you design your database relationships (see "Relationships Between Tables" in the Table and Database Design part of this guide ) with referential integrity checked, the Right Outer Join should yield the same results as the Inner Join because there should be no "orphan" records in the child table.

TIP: To find all records that have no match, create a query with both linked fields and choose a Left Outer Join or Right Outer Join. Under one of the fields set the criteria to Is Null. You might want to create this type of query before you create relationships with existing data to see which records you need to delete.

Tables: Combine with Union Query

The join query in the "Tables: Combine with Join" task combines related records and adds additional fields when you have an additional table. In some cases, you do not have related records and yet all fields of two tables are identical. This can be the case when you have a current table and an historic table. When you combine the two tables you want to see one record set below the other. You do this through the Union Query, which must be written in SQL. Luckily, you can use the Query Design View and then paste the results.

Steps

1. Create a query with the first table. Use field names that are the same in both tables. Click the View button and choose SQL View.

2. Select the entire SQL statement if not already selected, and click the Copy button.

3. Close the query without saving it. Create a query with the second table and the matching fields. Click the Design View button and choose SQL View.

4. Move to the end of the SQL statement and press Enter. Type UNION. Press Enter again and click the Paste button to paste the SQL statement from the first query.

5. Click the Run button to run the query.

NOTE: If there are any duplicate records (all fields), they do not display in a Union Query.

Tables: Create a Self Join

When you have a field in the database that refers to another field in the same database, you can create a self-join where you join the database to itself to display the information you want. This can occur, for example, when you have an employee database where one field is the employee ID of the employee's supervisor and the supervisor is in the same database. Instead of the supervisor's ID, you want to see the supervisor's name.

Steps

1. In Query Design View, select all the fields you want from the table.

2. Click the Show Table button and double-click the same table so you have two Field Lists from the same table; click Close to return to the design grid.

3. The second table name is identical to the first table name with the number 1 added. To avoid confusion, right-click in the second Field List and enter a name in the Alias property to give your second table a name.

4. Drag from the field in the first table that is related to the field in the second table to create a join. These fields will be different names but have the same data type.

5. Finish building the query by adding any fields, sort options, and criteria you want.

6. Click the Run button to run the query.

Top Value Query

If you want to see the top (or bottom) five salespeople or top 10% of customers, you can create a Top Value query.

Steps

1. Create a query with all fields you want to see, especially the field that you will use to rank the records. Go to Design View of the query.

2. Click in the ranking field. For top values, choose Ascending in the Sort row. For bottom values, choose Descending in the Sort row.

3. Click the Top Values button and choose one of the items in the list or type a value if it is not one of the drop-down choices.

4. Click the Run button to run the query.

Unmatched Query

As you are designing your database you might need to check how data from two existing tables are related. This will be especially true if you plan on creating a relationship between the tables and enforcing referential integrity. (See "Relationships between Tables" in the Table and Database Design part of this guide.)

Steps

1. In the Query tab of the Database window, choose the New button and choose Find Unmatched Query Wizard.

2. Double-click the table where the parent records are located.

3. On the next screen, double-click the table where the child records are located.

4. On the third screen, choose the name of the related field in both lists and choose the <=> button; choose Next.

5. On the next two screens, choose which fields you want to display and the name for your query; choose Finish to create the query.

The query will only display records from the first table because there aren't any related records from the second table. You can edit these records or delete them from this query