Database - worked example

A gardener wishes to keep a record of the shrubs in their garden using a database.
1. Load a database application and prepare to create a file.
2. Create fields for the following:

The name of the shrub (NAME)

Its flowering period (FLOWERS) Its flower colour (COLOUR) Whether it is
evergreen (EVERGREEN)

For evergreen use the following code letters:

N=NO Y=YES

The year it was planted (PLANTED)

Maximum height of the shrub in metres (HEIGHT) (this should be a numeric
field).

3. Enter the data below for the current collection of shrubs.

FLOWER COLOU EVERGREE PLANTE HEIGH

NAME
S R N D T

Forsythia
Viburnum Spring Yellow No 1987 3.0
Eleagnus Winter White Yes 1988 3.5
Clematis Summer Silver Yes 1989 10.0
Escalloni Spring White No 1989 3.0
a Summer Pink No 1988 1.2
Pyracanth Summer White Yes 1987 4.5
a Autumn Pink No 1988 0.2
Sedum None None Yes 1987 1.0
Juniperus Spring Yellow No 1987 1.5
Cytisus Summer Yellow No 1990 1.2
Hypericum Autumn Purple No 1987 1.8
Hibiscus Summer Purple Yes 1989 0.5
Hebe Spring Yellow No 1990 1.8
Kerria Summer Purple Yes 1988 0.6
Erica Spring Pink Yes 1990 0.3
Daphne

4. Save your database and print all the data in table format.
5. The gardener planted a new shrub in 1990 which is not evergreen: a LAVATERA
which has pink flowers in summer and is two metres high. Add this to the
database.
6. Since planting the CLEMATIS, she has discovered that the flowers are pink and
the height is 5.5 metres. She has also realised that she planted the HIBISCUS in
1989 and the flowers are orange. Amend the database to show these changes.
7. The CYTISUS has died, so delete this record from the database.
 8. Sort the data into ascending order of HEIGHT. Print all the data in table format.
9. Sort the data into alphabetical order of NAME. Print all the data in table format.
10. Search the database for EVERGREENS and print the NAME and YEAR OF
PLANTING only.
11. Print all the data for SPRING flowering shrubs PLANTED BEFORE 1989.
12. Save your file and close the database application in the correct sequence with the
data secure.

Database - worked example

1. Click the ACCESS icon to load the database, then select the New Database option
from the main menu. Select Blank Database, then click OK:

Select the drive where you wish the database to be saved, and give a file name:
2. Make sure that the Table button is selected on the index box. Select 'New', then
'New table'. Choose Design View:

Enter the first field heading 'NAME', then press ENTER to move to the Data
Type column. The NAME field will contain text (e.g. Forsythia), so set the Data
Type to 'Text':

NOTE: The default Data Type is 'Text'. You can accept this by just pressing
ENTER. To select any other data type, click in the Data Type column to produce
a drop down menu.

Enter the other Field Names in a similar way, as shown below. All the Data Type
entries can be 'Text' except for the 'HEIGHT' field, which must be set to
'Number':
When entering a number fiel you may wish to set the number of decimal places to
be displayed. Do this by selecting the Field Properties in the box at the bottom of
the window. To give one decimal place, use the drop down menus to choose:

Field Size Double

Format Fixed
Decimal places 1

When you have finished entering the field names, click the cross at the top right
tpo close the Table window. Click 'Yes' to save changes, then accept 'Table1' as a
name for the table:

You will receive a message that no primary key is defined. Answer 'No' when
asked if you want a primary key to be created.
Entering data
3. From the database index window, select the 'Forms' tab. Click 'New', then select
'AutoForm: Columnar':

Use the drop down menu to select Table1 as the source of the data, then click 'OK'. A
data entry form will be created. Use this to enter details of each of the shrubs. Remember
to use the code letters 'Y' or 'N' in the 'evergreen' field, as specified in the question.

You can move forwards and backwards through the set of data by clicking the arrow
symbols below the form:
When data entry is completed, close the Form window by clicking the cross in the top
right corner.

4. There is no need to save the data at this stage- this was done automatically by the
program while you were entering the data.

Click the 'Tables' button of the index window. Make sure that Table1 is selected then
click the 'Open' button. The records should be displayed as a table.

If necessary, make the columns wider so that all the text entries are visible. The column
width can be adjusted by moving the mouse pointer to the grey headings, then dragging
the dividing line between two columns:
A printout of the data can be obtained by choosing the File/Print option from the main
menu and selecting the room in which you are working.
NOTE: do not click the print button at the top of the screen - the printout may appear on
some printer at the other end of the College!

5. It is easiest to add the entry for the new shrub by typing directly into the empty bottom
row of the table:

6. Make the changes requested for the CLEMATIS and HIBISCUS records by overtyping
the entries in the table:
7. To delete the record for the CYTISUS, click in the grey border to the left of the name.
The whole row of the table will be highlighted in black:

Select the Edit/Delete option from the main menu. Click 'Yes' when asked to confirm
that the record should be deleted.

Sorting and searching data

8. Click in a cell of the HEIGHT column, then click the 'Sort Ascending' button. The
records will be sorted into height order:
Use the File/Print menu option to obtain a printout of the sorted table.

9. Click one of the cells in the NAME column, then click the Sort Ascending button to
produce the table in alphabetical order:

10. Close the table by clicking on the cross in the top right corner. Click the 'Queries'
button in the database index window. Click the 'New' button, then check that 'Design
View' is selected before clicking 'OK'.
With 'Table1' highlighted in the 'Show Table' window, click the 'Add' button. A box will
appear listing the fields which you entered for Table1.

Click the 'Close' button and the 'Show Table' window disappears. Take the mouse pointer
to the NAME entry. Hold down the mouse button and drag downwards until the mouse
pointer reaches the box in the first column of the white grid, labelled 'Field'. Release the
mouse button to deposit NAME in the box.
Use the same method to drag EVERGREEN to the second column, and PLANTED to the
third column, as shown below.

Type Y on the criteria line below EVERGREEN to indicate that only database entries for
evergreen shrubs are to be displayed. Remove the tick from the 'Show' box of the
EVERGREEN column by clicking the mouse in the box.

Close the query window by clicking the cross in the top right hand corner. From the
database index window click the 'Open' button to display a table of records:
 Only entries for evergreen shrubs are displayed.
Notice that the EVERGREEN column is not visible
although this was used in the selection mechanism.

Use the File/Print option to print out the query.

11. With the queries button still selected in the database window, click 'New Query'.
Select 'Design View'. Make sure that Table1 is selected in the 'Show Table' window, then
click 'Add' and 'Close'. Drag every field title down to the grid, as shown below:

On the 'Criteria' line of the FLOWERS column, enter the word SPRING.

We require shrubs planted before 1989. This is indicated by typing:

<1989
on the criteria line of the PLANTED column. The symbol < means 'less than...'

Close the query window by clicking the cross in the top right hand corner. At the
database index window, click the 'Open' button to display the results of the search.
Only one plant meets the criteria specified. Print this result.

12. There is no need to save the data - the computer has been doing this automatically for
you.

Close the database by clicking the cross in the top right corner of the database index
window, then exit from Access by selecting File/Exit.