Run MS-Access, select Open from the File menu, find the directory you saved the studrec database in and load it into Access.

Note: you may be able load directly from the list of most recently accessed databases which is displayed at the bottom of the file menu.

Sorting records in a database table

To sort the records in a database table on a single field

• open the student table
• select the field you wish to sort on by clicking on the field name at the top of the datasheet
• select sort from the Records menu
  select Ascending or Descending as appropriate
• the records will now be sorted.
• Note: the records are not permanently sorted in the table. If you close the data sheet and re-open it, the records will be in the original order.

Exercise:

Sort the Student table by tute_no (descending)

Sort the Student table by stud_surname (ascending)

To sort records on more than one field you can create and apply a filter/sort. For example

• Sorting the Student table on ascending surname within descending tutorial group.
• select Records → Filter → Advanced Filter/Sort from the Records menu. A filter window will open.
• By activating the pull down lists in the field and sort rows, specify a filter that will sort on descending tute_no and ascending stud_surname. You could provide an additional ascending sort on stud_givenname if you wish.
• Select Apply Filter/Sort from the Filter menu. The records should now be sorted into tutorial groups, with students sorted alphabetically within each tutorial group.

Using Queries

A database query is powerful feature that allows the developer to design specific views of the data stored in the system. These views (queries) may restrict fields that are displayed, may sort records in a particular way, may restrict which records are seen and might contain data from more than one table. Queries can be saved and activated whenever required, and can form the basis for screens and reports.

Building queries is very similar to the creation of a filter/sort introduced in the previous section.

1. Construct a query, StudentTutListQRY, based on the Student table that lists all students alphabetically into tutorial groups. The steps to construct this table are:
   • Select the Query tab in the Database window
   • select New → Design View
     (A SelectQuery window and a Show Table window will open).
   • Add only the Student table to the query and close the Show Table window.
   • The SelectQuery window is very similar to the Filter window used in the Sorting records part part of this tutorial. Activate the pull down menus in the field and row columns and construct a query similar to that shown in the previous figure. Save this query as StudentTutListQRY.
   • To run the query, choose Run from the Query menu. Close the query window.
   • To modify this query so that only those students in tutorial group 1 are shown, hightlight the query name and select design. In the criteria row of the tut_no field enter: =1
   • Save the modified query as StudentTut1listQRY (use Save As in the File menu). Run the query to check the results.
2. Construct a query, FullTutorialListQRY, which lists all students in alphabetical order and shows their tutorial groups and tutorial times. This query is based on both tables.
   • Select Query in the database window
   • select New → Design View
   • Add both tables to the query and activate the pull down lists in the field and sort rows to construct the query as in the screen:
     Notice the relationship is also shown!
   • Save the query as FullTutorialListQRY. Run the query
3. Construct queries (StudentTut2ListQRY, StudentTut3ListQRY and StudentTut4ListQRY) to provide tutorial listings for each tutorial group.
4. Construct a query which lists all students in stud_id order which shows the student's id number, surname, givenname and tutor number. Call this FullTutorialList2QRY.

Modifying the Tables

It may be that changes need to be made to the table structures after they have been defined. eg. adding a new field, deleting an existing field, changing the datatype of a field, etc.

Warning:

All decisions about the structure of the tables (fields, datatypes, key identifiers, relationships etc) should be made before the tables are specified and certainly before any data is entered or the relationships specified. If you make changes to key fields and validation rules these may not be reflected in some of the already created objects such as screens, reports and exsiting relationships.

This is why we insist that you design before you implement!

In the following exercise you will add a new field, gender, to the Student table, add a validation rule and some validation text to provide feedback during data entry.

• Highlight the Student table in the database window and select design.
• The gender field will be inserted between stud_givenname and tute_no. Highlight tute_no and select Insert Rows. A blank row should appear.
• Gender is an integer field with a default value of 1, so enter the validation rule exactly as: between 1 and 2.
• The validation text should be entered as "an integer entry of 1 or 2 is required"
• Save these modifications

To test this, open the Student datasheet and enter either 1 (for male) and 2 (for female) for each record. See what happens if you enter a number other than 1 or 2.

In this next exercise you will use a list box to choose available tute_rooms for the tutorials.

• Highlight the Tutorial table in the database window and select design.
• Select the tute_room field and then select the Lookup tab in the bottom section of the screen.
• Choose List Box from the Display Control option
• Choose Value list from the Row Source Type entry
• Then enter the following into the Row Source entry:
  G15;G16;G17;G23;E19;E20;E23     (note: include the semi colons too!)
• Save these modifications.

To test this, open the Tutorial datasheet and then place the cursor in any of the tute_room data entry fields. ie. Use the scroll option to now change the tute_room with ease without typing them in from scratch!!