Query Database Access

Queries can be used to quickly analyze and sort information that is in an Access database. A query allows you to present a question to your database by specifying specific criteria.
Queries allow you to specify:

• The table fields that appear in a query.
• The order of the fields in a query.
• Filter and sort criteria for each field in a query.

Select queries are the most commonly used type of queries in Access. They allow you to choose which fields and records to display in a new datasheet. Other types of queries include crosstab queries and four types of action queries.
Queries have two views: Design view and Datasheet view. In the Design view, you specify which tables you want to see, which tables they come from, and the criteria that records have to meet in order to appear on the resulting database. Criteria are tests that records have to pass. In the Query Datasheet view, you view the records that are found to meet your criteria.
When you run a query, Access pulls data out of tables and puts the data in a database for you to see. The original table and database stay connected, so that if you make changes to the data on your database, the results of the query also change. When you save a query, you save the query design, rather than the results, so that you can ask the same questions again.
A select query can be used to select certain data from a table or tables. It basically filters and sorts the data and can perform simple calculations, such as summing and averaging.
An easy way to create a select query is to use the Simple Query Wizard. It allows you to select the table fields you want to include in a query. This type of query is very useful when you want to eliminate extra fields, but still want to view every record in a database table.
A limitation of the Simple Query Wizard is that it does not allow you to set sorting parameters for records in order to sort by particular criteria, such as less than, greater than, equal to, etc. You must use a create a query from scratch in order to be able to set parameters.
Creating Queries using the Query Wizard
To Create A Query Using the Simple Query Wizard:

1. Open your Access database. (Note: Because Access uses data from previously created tables and queries to create new queries, the database must contain at least one table or query before a new query can be generated.)
2. Click on the Queries icon in the window that appears.

3. Double-click on Create Query by Using Wizard. The first dialog box of the Simple Query Wizard then appears.

4. Choose the table that contains the fields you want to select from the Tables/Queries drop-down list.

5. Click a field name in the Available Fields list; then click the Add (>) button to move the field name to the Selected Fields list. Add fields as needed, or move them all at once with the Add All (>>) button. Click Next to move to the next screen.

6. On the next screen, enter a title for the query. Then click Finish to view the query results.

Notes: Access automatically saves queries, so you do not need to do anything special to save your query. You can rerun your query at any time. This can give you updated results whenever data is edited. To rerun a query: open the database; select the Queries icon in the database window; in the Query list, double-click the query you want to run, or click it once and then click the Open button.

Creating Queries from Scratch
To Create a Query from Scratch:

Part 1: Starting a New Query
When you create a query from scratch, you can select the tables and fields that you use to build a query and set parameters for the fields.

1. Open Access, then open the database that contains the table or tables you want to use to build the query.
2. Click the Queries icon in the window that appears.

3.Double-click on Create Query in Design View. The Show Table dialog box then appears, listing all of the tables in the database.

4. Click the name of the table that contains the fields you want to use in the query. Click the Add button. Repeat for each table you want to add.

5. Click Close when you finish adding tables. The Query Design view window then opens. The tables you selected appear in the top pane of the Query Design view. Field names will not appear until you add them.

Query Design View Window

Part 2: Adding Fields to a Query
The Query Design View allows you to add the table fields you want in your query. Be sure the tables that contain the fields you want to use are present in the design window.

1. In the first field column of the query grid, click in the Field box. A drop-down arrow list then appears.

2. Click the drop-down list and select a field. (You may need to scroll through the list to find the field you want to use.)

3. Click in the next field column and repeat the procedure. Repeat to add all of the desired fields.

Note: The order that you add fields will be the order in which they appear in the query. If you need to change a field that you've placed in a particular column, use the Field drop-down list in the column to select a different field. You may delete any field you have added to a query. Click anywhere in the column and select Edit, then Delete Columns.

Part 3: Adding Criteria
You can set criteria for a query that can control how field information in selected fields appears in a completed query.

1. In the Query Design view, click the Criteria row in the desired field's column.
2. Type the criteria you want to use. See the Sample Criteria for Queries below.

Sample Criteria for Queries

• < (less than) - Matching values must be less than (or before in case of dates) the specified numerical string.
• > (greater than) - Matching values must be greater than (or after in the case of dates) the specified numerical string.
• <= (less than or equal to) - Matching values must be equal to or less than the value used in the criteria.
• >= (greater than or equal to) - Matching values must be equal to or greater than the value used in the criteria.
• =(equal to) - Matching values must be equal to the criteria string. This symbol can be used both with text and numeric entries.
• Not - Values matching the criteria string will not be included in the results.

3. Queries can contain multiple criteria. Repeat step`s 1 and 2 as needed to add additional criteria to the field columns in the query.

Part 4: Adding Simple Calculations
You can set up a query to do simple calculations, such as totaling information in a specific field or averaging information. To add calculations to a query, a Total row has to be added to the Query Design grid. After the Total row is available, different calculations can be chosen from a drop-down list for any of the fields that have been selected for the query.

1. In the Query Design view, click the Totals button on the Query Design toolbar. A Total row is then added to the Query Design grid (just below the Table row).
2. Click the Total row for a field in the Query Design grid that contains numerical data. A drop-down arrow then appears.
3. Click the drop-down arrow to select the type of formula you want to place in the field's Total box.
The following are some commonly used types of formulas:

Sum - Totals all values in the field.
Avg - Calculates the average for all values in the field.
Min - Displays the lowest value (the minimum) found in the field.
Max - Displays the highest value (the maximum) found in the field.
Count - Calculates the number of entries in the field (this basically counts the entries).
StDev - Calculates the standard deviation for the values in the field. (This basically measures how widely the values in the field differ from the field's average value.)

4. Repeat steps 2 and 3 to place formulas into other field columns.

Part 5: Viewing Query Results
After you have setup your query, you are ready to run it.

1. Click the Save icon in the Query Design toolbar. Type in a name for the query and then click OK.

Save Icon

2. Click the Run icon on the Query Design toolbar, or choose Query, then Run. The query results then appear in a datasheet that looks like an Access table.

Run Icon

3. After you have reviewed the results, click the Design View button on the toolbar to return to the Query Design view.