« Sorting and Filtering	Queries	Forms »

Introduction to Queries

Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. The resulting collection of records, called a dynaset (short for dynamic subset), is saved as a database object and can therefore be easily used in the future. The query will be updated whenever the original tables are updated. Types of queries are select queries that extract data from tables based on specified values, find duplicate queries that display records with duplicate values for one or more of the specified fields, and find unmatched queries display records from one table that do not have corresponding values in a second table.

Create a Query in Design View

Follow these steps to create a new query in Design View:

1. From the Queries page on the Database Window, click the New button.
   
2. Select Design View and click OK.
3. Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query.
4. Click Close when all of the tables and queries have been selected.
   
5. Add fields from the tables to the new query by double-clicking the field name in the table boxes or selecting the field from the Field: and Table: drop-down menus on the query form. Specify sort orders if necessary.
   
6. Enter the criteria for the query in the Criteria: field. The following table provides examples for some of the wildcard symbols and arithmetic operators that may be used. The Expression Builder can also be used to assist in writing the expressions.
   
   

   Query Wildcards and Expression Operators
   Wildcard / Operator	Explanation
   ? Street	The question mark is a wildcard that takes the place of a single letter.
   43th *	The asterisk is the wildcard that represents a number of characters.
   <100	Value less than 100
   >=1	Value greater than or equal to 1
   <>"FL"	Not equal to (all states besides Florida)
   Between 1 and 10	Numbers between 1 and 10
   Is Null Is Not Null	Finds records with no value or all records that have a value
   Like "a*"	All words beginning with "a"
   >0 And <=10	All numbers greater than 0 and less than 10
   "Bob" Or "Jane"	Values are Bob or Jane

   
   
7. After you have selected all of the fields and tables, click the Run button on the toolbar.
8. Save the query by clicking the Save button.

Query Wizard

Access' Query Wizard will easily assist you to begin creating a select query.

1. Click the Create query by using wizard icon in the database window to have Access step you through the process of creating a query.
   
2. From the first window, select fields that will be included in the query by first selecting the table from the drop-down Tables/Queries menu. Select the fields by clicking the > button to move the field from the Available Fields list to Selected Fields. Click the double arrow button >> to move all of the fields to Selected Fields. Select another table or query to choose from more fields and repeat the process of moving them to the Selected Fields box. Click Next > when all of the fields have been selected.
   
3. On the next window, enter the name for the query and click Finish.
4. Refer to steps 5-8 of the previous tutorial to add more parameters to the query.
   

Find Duplicates Query

This query will filter out records in a single table that contain duplicate values in a field.

1. Click the New button on the Queries database window, select Find Duplicates Query Wizard from the New Query window and click OK.
   
2. Select the table or query that the find duplicates query will be applied to from the list provided and click Next >.
   
3. Select the fields that may contain duplicate values by highlighting the names in the Available fields list and clicking the > button to individually move the fields to the Duplicate-value fields list or >> to move all of the fields. Click Next > when all fields have been selected.
   
4. Select the fields that should appear in the new query along with the fields selected on the previous screen and click Next >.
   
5. Name the new query and click Finish.
   

Delete a Query

To delete a table from the query, click the table's title bar and press the Delete key on the keyboard.

« Sorting and Filtering	Queries	Forms »
Office 2000 Tutorials · Word · Excel · Power Point · Access · FrontPage FGCU does not provide personal support for Office 2000 programs to outside parties.