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.
- Click
on Query tab and select 'terraced'.
- Click
on the Open button to see the resulting dynaset of this.
- The
Type, Address line I, Area, Price and Beds of fields of records
with Type -- 'T' are displayed.
- Close
the query.
- Select
'beds3' for another query to list records.
- Click
on the Open button to see the resulting dynaset of this.
- The
Type, ID, Address Line 1, Area, and Beds fields of records with
greater than 3 bedrooms are displayed.
- 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.
- Click
on the Queries tab and New button to create a new query.
- From
the New Query dialogue box choose Design View and OK.
- When
the Show Table box appears select Property and click the Add
button.
- 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.
- Click
in the each box in turn and enter the following.
- 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'.
- 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.
- To
print this dynaset choose the File menu and select Print.
- Save
the query with the name 'detached'.
- 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).
- Amend
the Type criteria to 'C'. Save this query calling it 'Cottages'
using the File menu and selecting Save as, using the box below.
- If
you close the query without saving you will be prompted to save
as Query1, 2 etc. Now you can name your query.
- 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:
- List
the Type, ID, Address Line 1 and Area for houses in Nomemby
- ie. Area = "No".
- 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
|