Exercise 1 – scope a small database
A local medical practice have asked you to help design a simple database to track the costs of treating
patients. Their paper based system means that it takes a long time to work out how much they are spending.
What do we need to be able to do?
Record exact costs of treatments against each person
Record name, description, and cost of a range of treatment types
Track costs by treatment type
Track costs by age
Track costs by sex
Track costs by post code
Tasks
Decide what the main table names should be
Note down the fields that belong to each table
Choose an appropriate data type for each field
Exercise 2 – Create a table
Using the same technique as used to build the Patients table, create the Treatments table
Tasks
Create a new table in Design view (CREATE > Table Design)
Enter the fields as in the table below
Nominate the ID field as primary key
Add a validation rule to the currency field so that values must be greater than or equal to zero
Add some validation text to the effect that “Treatments costs cannot be below zero
Save the table and call it Treatments
Field Name
Data Type
ID
AutoNumber
Treatment
Short Text
Description
Long Text
Cost
Currency
