Creating a Query
IIHE  |  Education  |  Getting Started With Microsoft Access  |  Creating a Query

A query provides a custom view of a table. With a query, you can choose which fields to display, and you control which records are displayed. The standard type of query is the Select Query. Each time you run a select query, it interrogates the table before displaying the matching records.

To create a query, open the database and then click on the Queries tab. Now, click on the New button:

The New Query dialog box appears:

We are going to use the default, Design View.

Selecting Tables to include in the Query

Click on the OK button:

Now, you pick the table or tables that you want to get information from. For this example, click on HFHS Visits, then click on the Add button and finally click on the Close button:

Notice that a small window labeled HFHS Visits appears on the upper part of the query window. It contains a list of the fields in the HFHS Visits table. The bottom half of the screen shows the fields that will be used in the query. The asterisk is used to include all the fields from the table into the query. Unfortunately, when you use the asterisk, you cannot specify the sort order or criteria for any field very easily.

You add fields to the query by either double clicking on one of the field names or by dragging a field name from the upper window to the lower window. For this example, double click on these fields (be sure to do it in this order):

Field	Description
SITE_CD	site
DX_CD_PRI	diagnostic code
AGE_YR_CT	age at service date
SERV_DT	service date

Now, your screen should look like this:

These are the fields that will be displayed.

Setting the Criteria

We want to display only the records from Detroit Northwest which is 80. So, in the criteria row under SITE_CD, enter the number 80:

Now, we would like to only look at the visits between 1/1/97 and 6/30/97. To do this, enter:

between 1/1/97 and 6/30/97

in the Criteria cell under the SERV_DT and press the Enter key:

 

Note that after you press Enter or click the mouse in another cell, Access changes the text to

Between #1/1/97# And #6/30/97#

Access wants dates to be enclosed in pound signs and automatically does it for you.

Sorting the Data

Now, we would like the data sorted by the diagnostic code. You do this in several ways.   If you click in the Sort SERV_DT cell, a pop-up indicator will appear in the right-hand side of the cell. Clicking on the cell displays a menu for Ascending, Descending, and Not Sorted. Another way to change the setting is to double click in the cell. The setting will change from none, to Ascending, to Descending, and then back to none. Click in the Sort SERV_DT cell to choose Ascending.

 

Running the Query

There are many ways to run the query:

• From the Toolbars, you can click on the Run button, .
• From the Toolbars, you can click on the Datasheet View button, . There is a difference between the two buttons. The Datasheet View button performs the basic query while the Run button applies all settings that have been set, such as making a new table when the Make Take option is selected (we'll go over this in a later chapter).
• Another way is to choose Run from the Query menu or choose Datasheet View from the View menu.
• Lastly, you can close the query and then double click on it by the Database View.

When you run the query that we designed here, it will take a minute or two for the matching records to be displayed. That's because the HFHS Visits table has over 400,000 records.

You can return to the design view by clicking on the Design View button, on the Toolbar. At this point, you can modify the query by adding additional criteria, adding fields, or changing the sort order.

Using the Supplied Forms  |  Getting Started With Microsoft Access  |  Editing a Query