Creating and Using Queries

So far we have listed data in the Table function in Access that allows tables of data to be created, designed and viewed. The Query function allows tables to be searched in order to list a dynaset - a temporary subset (listing or grouping of data) that satisfies search conditions. Restart Access and open the 'Houses' database.

Listing Dynasets
The Table function by itself will list all records and all fields. Often there is a need only to list specified subsets - eg. to list terraced houses. There is already a query to produce this report.

1. Click on Query tab and select 'terraced'.
2. Click on the Open button to see the resulting dynaset of this.
   
   
3. The Type, Address line I, Area, Price and Beds of fields of records with Type -- 'T' are displayed.
4. Close the query.
5. Select 'beds3' for another query to list records.
6. Click on the Open button to see the resulting dynaset of this.


7. The Type, ID, Address Line 1, Area, and Beds fields of records with greater than 3 bedrooms are displayed.
8. Print this dynaset.

Creating a Query
Queries are useful to produce information which is often asked for and can be easily created. For example, there could be continual requests for a list of detached houses.

1. Click on the Queries tab and New button to create a new query.
2. From the New Query dialogue box choose Design View and OK.
3. When the Show Table box appears select Property and click the Add button.
4. Close the Show Table dialogue box and the Select Query window will be displayed. Select fields by clicking on drop-down arrow. There is a table in the lower part of this window with a column for each field to be displayed in the query dynaset - a temporary listing of the table.
5. Click in the each box in turn and enter the following.


6. The fields are chosen by clicking in the top cell of each column and clicking on the down stop arrow to display a field list in the current table. This query has a criteria (or search condition) in the Type field that has a value 'D'. This is the code for 'detached'.
7. Click on the Run icon to see the resulting dynaset of the query - ie. a listing of the Type, ID, Address Line 1 and Area fields in ascending ID order. Note that the status line indicates the number of records satisfying the criteria.


8. To print this dynaset choose the File menu and select Print.
9. Save the query with the name 'detached'.
10. Amend this query for a list of cottages by opening the 'detached' Query design (either click on the Design button or highlight the query and with your mouse arrow over the blue highlight, right-click and select Design from the menu).
11. Amend the Type criteria to 'C'. Save this query calling it 'Cottages' using the File menu and selecting Save as, using the box below.


12. If you close the query without saving you will be prompted to save as Query1, 2 etc. Now you can name your query.
13. Repeat the above for a list of flats by editing the Type criteria.

Note that Access will tolerate incorrect upper/lower case characters for tile search string - eg. 'd' for 'D' will produce the same dynaset. Open the 'Cottages' Query design and amend the above query to:

1. List the Type, ID, Address Line 1 and Area for houses in Nomemby - ie. Area = "No".
2. Repeat for houses in Fitten - ie. Area = "Fi".

Field columns can be amended, deleted and inserted. To delete a column place cursor above column to display the down-arrow cursor and click to select the column. Press the Delete key to delete a column and the Insert key to insert a column.

Contents: Databases

Skill Check: Databases