ICT – The E-book.

Part two – Databases

Probably the most convenient place to store a list of names, beyond a diary, is in a database. Automatically updating the list, searching the data and producing a tidy print-out are examples of tasks often completed with the aid of a ‘database management system’. Let’s have a look at a typical list of names, a mythical class list, stored with the use of Microsoft’s Access, called SomeYearSevens2000.. (This is suitable for Access2000, but the alternative, SomeYearSevens97., may be of use.)

You can see that within the database file there is, as yet, only one table of data called Yr7Tbl1 (a shortened form of Year7TableOne). Double-clicking on this reveals a list of 59 records with each record (or row) consisting of 6 fields (or columns). Clicking on the design icon

allows us to take a closer look at the table’s make-up. Notice that one of the fields (now laid out in rows!) has a small key symbol alongside. This means that ‘ID’ in this database, is the key field. Each entry for ID has to be different, that is, unique, in order for the program to carry-out its duties (storing, searching and sorting, amongst others) without any hitches.

Ensuring the use of correct data is vital, not only in a business environment, and we could adopt the traditional technique of entering data twice (known as verification), but right now we’ll consider a validation technique. Still looking at our table in design mode and clicking on the Gender field, we can see that a ‘validation rule’ has been inserted that allows only male of female choices of gender. The ‘validation text’ is shown on the screen when a user tries to enter some other data – though lower case values are acceptable here.

A slightly more sophisticated validation rule is attached to the ‘Fullname’ field that insists on a ‘space’ being inserted on data entry. This means that this file is expecting two names (a forename and a surname), though most data lists of this type would provide two separate columns, often headed FirstName and LastName.

Another way of restricting data entry is to set up a ‘Look Up’ field. An example is in the Hobbies column where clicking on a cell provides an option to select Cycling, Dancing or Fishing from a look-up table.

Sorting a table is an essential feature of a database. This can easily be done without switching to design view; clicking firstly in the column on which you need to sort (say the ‘Fullname’ column) and secondly on the ‘Sort ascending’ or ‘Sort descending’ button on the formatting toolbar (hovering over the icon should show you which is which!).

In the diagram below, you can see that the table has already been sorted by (first) name – ‘Sort ascending’ means that the entries will increase alphabetically as you move through the file.

Searching a database is another indispensable technique; it can be referred to as interogating the database - so we now turn to the design of so-called queries.

Our demonstration database has its own database window revealed by closing the table. It is then possible to click the ‘Queries’ tab and choose to look at, in design mode, the ‘And’ search.

Here, we might imagine that 7g’s form tutor would like to organise a fishing trip for his tutees; searching the database on the two criteria “7g” and “Fishing” yields the seven records of those keen on fishing. Click on ‘Datasheet view’ to see them.

But if the tutor, Mr Jones, was keen not to upset the rest of his form, he might choose to take the whole of 7g, and to fill the coach, take keen fisherfolk from 7c. In which case he could search the database for 7g OR those keen on fishing, by putting the criteria on different lines:

Changing to ‘Datasheet view’ will reveal a list of 36 students, 6 of them from 7c.

The tutor of 7c, Mrs Smith, may wish to take the remaining students to the theatre. To find those students from the database she could search for all in 7c who are NOT keen on fishing. This time, we ought to go through the query design step by step.

1. Click on the ‘Queries’ tab in the database window.

2. Choose to create a ‘New’ query.

3. Select to create your query in design view.

4. Add ‘Yr7Tbl1’ – the only table on offer!

5. Double click on each of the field names Fullname, Form, Gender and Hobbies (to ensure their contents are displayed).

6. In the ‘Criteria’ row, type “7c” under Form and <> “Fishing” under Hobbies.

The Datasheet view should reveal a list of 23 students in 7c who have not declared fishing as their principal hobby.

The possibility of updating records automatically can be a real boon with a large commercial database and we can demonstrate that technique with our small, mythical, database of seventh years. How, for instance, might we update 7g to 8g for the start of a new academic year, with one keystroke?

The answer is by using an update query. Clicking on the queries tab and choosing to design a new query, we go through the familiar routine of adding our data-table Yr7tbl1 to the query design form and double-clicking on those fields with which we want to work (Form will do). This time, though, we must click on the ‘Query’ menu item and choose ‘Update Query’. This provides a slightly different look to the Form column and we can now type “8g” in the ‘Update To:’ row and “7g” in the ‘Criteria:’ row.

Running this query (or simply opening it from the Database Window) will update all of the records with a form of 7g to 8g. You might try updating 7c to 8c - or simply converting 8g back to 7g!

So much for being able to change the contents of a file, but how can we harness the power of a database to produce neat and tidy print-outs? The results of interogating a database are often called reports and Access allows us to generate and display these, very much as we like. Choosing ‘Reports’ and ‘New’ from the Database Window we then design our own report based on an earlier query – the ‘Not Search’.

Mrs Smith, the tutor of 7c, could now produce a neat list of all those cyclists and dancers who didn’t want to go fishing.

We might, after showing the field list by clicking the icon, show four of the fields in a larger font, with the field labels in a different colour.

The recommended technique is to drag a field name from the field list into the ‘Detail’ section of the report, right click and cut its label in order to paste it into the ‘Header’ section. Labels and fields can then be dragged until they are each in the preferred position. A group of labels or fields can be selected before right-clicking and changing their properties (such as background colour, font-size and border type) en-masse. After ensuring the ‘Toolbox’ toolbar

is available, clicking on the label button

allows a heading to be put into the header section (which will appear at the top of each page of your report) and a remark to be put into the footer section (which,naturally, will appear at the foot of every page). The results can be inspected with the usual print-preview button:

Viewing and editing data on-screen can be made easier with the use of forms. Clicking on the ‘Forms’ tab within the Database Window reveals Yr7Form1 which was created by asking Access for a New, Columnar form, based on our familiar table Yr7Tbl1:

The new form can then be tuned to your own requirements by dragging fields in much the same way as for the design of a new report.

After leaving design view, the form can then be used to navigate, one record at a time, through our data-table, editing if required. In fact, the creation of a form allows more subtle data-manipulation with the use of macros and even a little code. As an example, let’s imagine that Mr Jones or Mrs Smith would like to indicate the award of a ‘School Certificate’ to any student on merit and from time to time. There is a button on the form which, with one touch by the mouse, can change the starting (or default) value of the Achievement field from ‘No’ to ‘School Certificate’.

Taking a closer look at this feature within Design view and right-clicking the button, we can consider the button’s properties, in particular the on-click event.

Clicking on the ellipsis (three dots) we obtain the macro design screen, showing two actions. The first is necessary to remind Access that we are keen to edit the Achievements field (Go To Control with Control Name as Achievement) and the second actually does the work (Set Value of the Item with full address [Forms]![Yr7Form1]! [Achievement].[Text] to an Expression called "School Certificate").

As usual, there is more than one way of achieving our objective; right-clicking on the Achievements field within Form Design view allows access to the field’s properties - looking at the entry against its ‘Onclick’ event will eventually reveal a little code which runs when the user simply clicks inside the field. The critical line

[Achievement].Text = "School Certificate"

is very similar to that we have seen within our macro and you may wish to experiment by attaching code directly to our ‘command button’ on the form. Clicking on ‘Modules’ and ‘Mod MakeAward’ from the Database Window will reveal a few lines of dormant code.