Introduction
This week we will develop your ability to query an existing database, using more complex queries and a relational database. Your specific objectives are:

• to become familiar with the structure and construction of a relational database
• to be able to perform queries on a relational database
• to be able to perform queries using Boolean logic
• to present each record in a Form rather than in a datasheet

Resources

As for last week, especially:
Lewis and Lloyd Jones, pp. 160-170.
Student's ACCESS, §4.1-§4.3.
 

Exercise 1: Querying a relational database with two tables

Open the file HIST003.MDB. This contains three tables which store information about individual students and their course choices. If you wanted to find out the names of the students doing a particular course, you would need to retrieve information from two tables with a shared field, i.e. one column which contained the same category of information. In this case you will want the tables called Students and Courses. Select Query/New, then select each table in turn.

The tables are linked by the columns or fields which are common to them, in this case StudID. In ACCESS v.2.0 the line showing the link will appear automatically between the two boxes.

Select the fields you want and drag them to the grid. Alternatively you can double click on the field to enter it in the query grid. In this case you want the StudID and Name fields from the Students table and the Ccode field and Year from the Courses table.

In the criteria cell of the Ccode field, specify the course you are interested in e.g. NN102. The result of the query should be 3 records. If you wanted to know who took that course in a particular year, return to the query grid and type "1994" in the criteria cell of the Year field. There should be one student who took NN102 in 1994.
 
 

Exercise 2: Querying a relational database with three tables

If you wanted the query to include the name of that course (NN102), you need to involve the third table, Options.

Select Query/Add table, then Options, Add, Close. The third table will be added to your query. You can use the Query menu to add or remove tables to adapt queries. Add the field Cname to the query grid by double-clicking on it.

Still using NN102 as your criteria, the result will show that in 1994 Steven Hughes took a course called Ballet for beginners. You may want your results table to show the course name but not the course code: click on the show box in the Ccode field of the query grid. This will include the field in the search but not show the information in the results table.

Save the query as Query 1

NOTE: If you have more than one table open, you can switch between them by selecting Window and then the name of the table or query from the sub-menu.
 

Exercise 3: Sorting and Counting in a Query

You can sort the results of your query in a number of ways, usually alphabetically or alphanumerically. For example, open Query 1 above and amend it so that there are no specific criteria, then select the sort cell of the Name field. Select descending. The resulting table will place the students in reverse alphabetical order.

Alternatively you could sort the results according to course code by selecting ascending from the sort cell of the Ccode field.

Using the same query we can ask ACCESS to perform a simple calculation. Delete all the columns except those for Name and Ccode (to do this select the column, then press delete). Click on the Totals button (). A new row will appear in the query grid called Totals. Select count in the totals cell of the Name field and Group by in the totals cell of the Ccode field. Select descending in the sort cell of the name field.

NB Remember to click on the show box of the Ccode field so the information is displayed in the query.

The resulting table will show you how many students are taking each course beginning with the largest. You could also use this facility to find the maximum, minimum and average of your data, if it were suitable.
 
 

Exercise 3  Using strings like AND and BETWEEN and OR

Sometimes you may wish to search for two or three categories at the same time, for example students doing any of three different courses or students doing one course but in any of two or three years. In order to perform such a query you need to use strings such as and, or and between.

In HIST003.mdb, perform a new query to search for all those students doing a PN101 or NN102. Type into the criteria cell: "PN101" or "NN102". The result should be 5 students.

(Click on the Totals button once more to get rid of the Totals row)

Exercise 4: Presenting your records in a Form

You can also present and enter data in a form rather than a table. Each record is then presented as an individual form enabling you to see the individual records more easily if they have many fields.

Open HIST003.mdb.

Click on the Forms button in the database window. Click on New, then select the table whose data you wish to use. Click on FormWizards and select a form type, usually single column. Select the fields you want to be displayed on the form. By clicking on the double-headed arrow button you can select all the fields at once. The list of fields will move to the right-hand box. Press Next. Select standard then Next again. Give the form a name (it can be the same as the table it derives from) then click on the Open button. If you pressed Design instead the form would be presented in a layout which enables you to change the size and position of the components of the form.

ACCESS will display the first record in the table as a form. You can move between records using the buttons in the lower left corner.

Close the form and save it as Form 1.
 

NOTE:

You can add a new records via a form (rather than in the table). With the form open, select Records/Go to/New. Type the new data into the blank form which appears. Use TAB to move to the next record if you want to add more than one new record.

You can customise the appearance of your form. With the form open, click on the Design View button on the toolbar. In design view you can move the boxes containing the data around, enlarge them and customise the form in many different ways. Cue Cards could be very useful to you in this process.

The other way to present data is in a 'report'. This enables you to print out records in a customised layout. See Getting Started, ch. 9; User's Guide, part 5; Student's ACCESS, pp. 31-37 and Liskin, chs 7, 10, 11.

Conclusions

You should now be able to:

• Query a relational database of two or more tables.
• Perform more complex queries using strings.
• Present your data in a Form rather than a table

 
To IT-MA home page
To Department of Historical Studies home page.

(c)R. Middleton 1997. Last modified 30 June 1998.