Queries allow the selection and display of just part of the data in a database. For example, you might need to display all the tutors from a particular department - this would be a simple query using data from just the Tutors table. A more-complicated query might be to display details of all the courses each tutor offers: this would use data from both the Tutors and Courses tables.
| Click on the Query button in the Database
Window (Figure 2). 
|
|
| Click on the New button. |
You will see the New Query dialogue
(left).
The Query Wizards button can be used to create a query for you- although this can be useful, it is not appropriate for most types of query. For this exercise, to create a simple query yourself:
|
| Select New Query
|
|
| Click on Tutors, then click on the Add
button.
|
|
| Click on the Close button. |
The screen should now look like Figure 12 (opposite). Every table that you add to a query is shown in its own box in the top section of the Select Query window.
The next step is to decide which fields you need to use in the query. The following will illustrate how to build a query to display information on all tutors from one particular Department.
You need to copy all fields containing information to be displayed or searched into the query section in the lower part of the window. For example, to copy the Department field:
|
| Select the Department field name in the Tutors
table box.
|
|
| Drag it into the first cell on the Field: row. |
Repeat this for the Tutor Name and Extension fields, placing them in the second and third columns.
To select the records from one department:
|
| Click on the Criteria: cell in the Department
column.
|
|
| Type the name of the department you want to search for (for example, Computing Services). The Query design should now look like Figure 13 (overleaf). |
When the query is run, only records with exactly matching values will be retrieved.
To search for a sequence of characters that are contained within a field, use the * (asterisk) wildcard character in the Criteria: cell. For example, entering Comp* would find Computing, Computers, Computation and so on.
Figure 13 - Query Design: Selecting a Department
You may want to test the query at this stage. To do so:
|
| Click on the Datasheet View button  .
The screen should look similar to Figure 14. |
Since the records retrieved are all from the same department, you may decide you don't really need to display this field. To modify the query, you need to return to the design view:
|
| Click on the Design View button  .
|
|
| Click in the Show: cell in the Department column; the X will disappear from the box. |
You may also think it useful to have the records sorted by tutor's name when they are displayed. To do this:
|
| Click on the Sort: cell in the Tutor Name
column.
|
|
| Click on the  button at the right
of the cell.
|
|
| Choose Ascending (0-9 and A-Z) or Descending (9-0 and Z-A). |
Figure 15 - Design of Final Query Sorted by Tutor Name
The Design view should now look like Figure 15 (above).
(Incidentally, you may have spotted a problem with the design of this table: you cannot sort by the tutors' surnames. This is why it's almost always better to have separate fields for surname and first name.)
To check the effect of these changes:
|
| Click on the Datasheet View button again.
The result should be like Figure 16. |
Before going on to the next section:
|
| Save the query as, for instance, Computing Department
Tutors.
|
|
| Return to the Database window. |
This query uses the Tutors and Courses tables to find all the courses that each tutor is the author of. To do this, you will need to link the Author field in the Courses table to the Tutor Name field in the Tutors table, so that fields from both tables can be shown together.
|
| Start a new query and add the Tutors and Courses tables. |
To create the link between the two tables:
|
| Click on the Tutor Name field in the Tutors
table box.
|
|
| Drag the pointer from Tutor Name across to Author in the Courses table box. When you release the mouse button, a line will appear connecting the two fields (see Figure 17). |
The link you have just created will ensure that records where the Tutor Name and Author fields are the same will be joined. This means that fields from either table can be displayed together.
|
| Drag the Tutor Name field from Tutors, Course Name from Courses, and Department from Tutors, down to the query section. |
The screen should now look similar to Figure 17.
|
| Click on the Datasheet View button
to test the query.
The screen should look like Figure 18. Figure 18 - Result of Query Using Linked Tables
|
|
| Save the query as Course Authors (or similar) and return to the Database window. |
This will bring up the Add Table
box (left), which is where you choose the table or tables that
you will need in your query.
