Introduction
Over the next three weeks the classes cover two main areas:

• how to search an existing database; and
• how to construct your own database.

Learning to use an existing database should enable you to access material previously unavailable while constructing your own will provide you with a deeper appreciation of different types of data and their properties and a new way of storing and analys ing your own material.

This week's objective is to introduce you to the structure and features of a database management system, in this case ACCESS v2. NB All of the following is written for version 2 of ACCESS and while you may find earlier (v1.1) or later versions ( for WINDOWS95 or NT) elsewhere in the university they will work in slightly different ways and the datasets we have prepared for weeks 12-15 will not work with them. Thus, unless you have personal access to the correct version of ACCESS, you will have to w ork in the Faculty computing laboratories.

In the course of this week, you will:

• understand the structure of a database and become familiar with general terminology.
• be able to perform specific tasks using ACCESS v2 such as entering simple data and querying a database.

A database is the collection of information, for example a bibliography, a list of employees and addresses, population figures or election results, even a simple address book. A database management system (DBMS) is the software application which allows you to store and analyse a database on a computer. You can build your own database using a DBMS or you can search an existing one for information.

See structure and terminology of DBMSs for more detail.

Resources
A. Beck, M. Maynard and R. Rodger, A Student's Guide to Access 2 for Windows v.2.0.
M. Bull (1990) Student's Guide to Databases. Oxford: Heinemann
C. Harvey and J. Press (1996) Databases in Historical Research. London: Macmillan
M.J. Lewis and R. Lloyd-Jones (1996) Using Computers in History: a practical guide. London: Routledge, pp. 137-141.
M. Liskin (1993) Help! Microsoft Access. Emmervill, CA: Ziff-Davis Press
L. Weatherill and V. Hemingway (1994) Using and Designing Databases for Academic Work. University of Newcastle upon Tyne.

The official manuals are also useful, especially Getting Started and User's Guide (both available in the Open Learning Centre, as is a video giving a good introduction to v2 of ACCESS).

The Computer service produces a document (ho-t6) called 'Using the ACCESS database package'. This is available in WORD format.

To get help in ACCESS you can use what are called 'Cue cards'. These are designed to help you while you work. If at any point you are not sure, select Help/Cue cards from the menu bar and follow the instructions. They include infor mation on building tables, working with data and designing queries. They also include explanations about what databases are and how they can be used.
 

Exercise 1: Opening, closing and saving a database

Open ACCESS via Program Manager by double-clicking on the ACCESS icon. ACCESS files have the extension .MDB.

Select File/Open database. Find and select the file called HIST002.MDB as you would do if using WORD or EXCEL. Select the file then press OK. You will then get a screen which resembles that below:

There is only one table in this database which is already selected. Click on Open. A table or 'data sheet' will appear. It contains information on depositors to the Sheffield Savings Bank, 1861 from Lewis and Lloyd-Jones (1996), app. 6, p. 209.

You can move back to the database window by clicking on the Database Window button on the toolbar. You can close the table by double-clicking on the top left hand corner of the table.

Take some time to familiarise yourself with the layout of the screen, noting the buttons on the top ribbon (ACCESS may be slightly differently configured on your PC but all of these functions are also available through the main menu). These are (from l eft to right):

• Design View Shows the design view of a table, form or query.
• Datasheet view Shows the datasheet view of a table, form or query.
• Print To print the table, form or query.
• Print Preview Shows how the table would appear when printed, as in WORD.
• New Move to the new record at the end of the datasheet or form.
• Cut Delete selection and copy to clipboard.
• Copy Copy selection to clipboard.
• Paste Paste selection from clipboard.
• Find Allows you to search for particular text within a chosen field. (Useful in a very large table if you do not want to perform a full query)
• Sort in ascending order and Sort in descending order
• Edit Filter/Sort Specify the filter and sort criteria
• Apply Filter/Sort Apply the filter and sort criteria
• Show All Records Remove the filter and requery underlying records
• New Query Create a new query.
• New Form Create a new form. Forms will be dealt with in week 14.
• New Report Create a new report. Reports will also be dealt with in week 14.
• Database Window Show the Database Window.
• AutoForm Automatically creates a simple form based on the selected table or query.
• AutoReport Automatically creates a simple report based on the selected table or query.
• Undo Current Field/Record Reverts to previous value in field or record.
• Undo Undo most recent change.
• Cue Cards Display on-line coach to help you with your work.
• Help button Opens Help contents.

To close a database, close all your tables and queries, then click on the top left of the main database dialogue box and select Close. Alternatively use File/Close database.

Using File Manager save a copy of HIST002.MDB to your floppy disk, renaming it according to the usual rules (Gordon Brown's file would be gobr13.mdb).

Exercise 2: Adding to an existing database

Open your file from your floppy disk.

It contains 25 records. Adding further records to an existing database is straightforward.

Using the mouse, place the cursor in the empty row at the bottom of the table. Add the following five records to the table, using TAB to move between cells.

26    M    Tinner                        20    County Mayo
27    M    Spring metal cutter     24    Sheffield
28    M    Tailor                        12    Sheffield
29    M    Chaser                      16    Sheffield
30    M    Saw maker               14    Portmahon

The first field (Depositor's ID) is called a PRIMARY KEY. This means that this is the field which uniquely identifies each record in a table. The primary key of each record must be different. Try to change the ID number of one of the new records to a n umber below 25. Try to input one of the records without entering an ID number at all. You will not be allowed to. The significance of the primary key in database design will become clear in week 14.

Exercise 3: Searching an existing database: Query 1

One of the most common tasks you will need to perform is to search an existing database to get information on a particular issue.

To perform a select query on the database, for example to discover how many depositors were born in Sheffield, select Query and then New from the dialogue box.

From the Add Table dialogue box, select Savers, then click on the Add button. Then click on the Close button. If the database consisted of more than one table, you could select all the relevant ones at this stage.

The Savers dialogue box appears, containing a list of the fields available (as below).

This allows you to define which fields and records which you want to include in the query. In this case we want all of them. Double click on the title bar of the savers box, then click on the highlighted field list and drag it to the first field cell o f the query grid. The fields will appear as column headings. To select individual fields, double click on the respective field name or click and drag it down to the head of a column.

In the criteria cell of the Place of Birth field type: "Sheffield" including the inverted commas. If you type in one word or number, ACCESS will automatically add the inverted commas but if there were two or more words you would need to add them yourse lf. To perform the query and display the results click on the Datasheet view button. This will display a table containing all the records of depositors who were born in Sheffield. There should be 18 if you have added the new records listed above. < BR>  

Exercise 4:  Searching an existing database: Query 2

To alter your query or make it more specific, go back to the query grid using the Query view button. Now we want to find depositors born in Sheffield who also worked as miners. Type "miner" in the criteria cell of the Occupation field. The resul t of the query should be one depositor, ID no. 11.

You can also be more general in describing your criteria. To find all the silver workers, type "silver*" under occupation. The resulting query would include silver polishers, brushers and silversmiths. The asterisk represents a 'wildcard', enabling you to search for records which contain the word silver in combination with others. If you put in 'silver*' you would be searching for any records which begin with 'silver' such as silversmith; if you put in '*silver*', you would be searching for any records which contain the word silver, such as 'polished silver worker'.

To save the query, select File/Save As. At this point you can change the name or save it as Query 1. To close the query select File/Close.

Exercise 5  Printing a database

Open the table or query containing the data you want to print. If you want to print the whole table, select File/Print. If you want only part of it, select the rows you want before you select File/Print and the n choose Selection from the dialogue box. Before you print out you may need to check the page set-up (using File/Print Setup).

Conclusions

By the end of week 13 you should be able to

• Open, close and save a database
• Open and add data to an existing table, saving the changes
• Perform simple queries on one table using one or more criteria and wildcards
• Print the results of your query

You should also feel confident about the terminology used in describing databases
 

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

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