| Designing a linked table | Using a value list | Implementing table relationships |
| Multitable queries | Form/Subform | Summary |
| Exercise |
Learning Outcomes
On completion of this chapter you will
know:
·
The nature of relationships in
a database
·
How to design multiple tables
so that a one-to-many relationship can exist between them
·
How to validate data in one
table using data from another table
·
How to create a multi table
query
Introduction
In order to examine table relationships we
shall expand our student application in order to include the entry of term
marks. From our knowledge up to now our first reflex would be to add extra
fields to the existing table, Students.
If we assume that 10 subjects are available
to the student, that the student stays at the school for 4 years, that there
are 4 terms in each year and 3 tests per term then we would need to add 10 X 4
X 4 X 3 extra fields or 480 fields. If we
were to include the date of each test then the number of fields would increase
to 960! This would be an extremely awkward and clumsy table to manipulate and
interfacing it with a form would be impossible in practice. Similarly queries based on the table would be
almost meaningless. Finally, as no
student takes 10 subjects and not every student stays at the school for 4
years, there would be quite an amount of wasted storage space involved.
For those reasons we shall abandon adding
the 480 or 960 fields and look at an alternative method. This will involve creating a separate table
exclusively for exam results.
Designing
a table that is to be linked to another table
A table that is to be used for holding test
results should have the following fields:
·
Subject – the subject that is
to be examined. Examples of the data
here would be “Mathematics”, “Mathematics A”, “Biology”, “Language and
Literature” etc.
·
Teacher – Name of the teacher
who set the examination
·
Year – the current academic
year
·
Term – the term in which the
examination was given
·
Marks – the marks out of 100
gained by the student
Of course the marks in question were earned
by a student and, therefore, we need to have some information in the table to
identify the student who earned those marks.
Our initial reflex would be to add three extra fields: Student ID, Surname and Name, to the table. This, of course, would work but it would be a
very inefficient way to proceed because the fields Student ID, Surname and Name
already exist in the Student table
and inserting them here again would be duplicating the data. One result would be wasted storage space due
to same data appearing a number of times.
Another result would be inconsistency of data as misspellings could
occur when entering the same data a second time.
Remember that all of this data is already
available in the Student table. Efficient use of the database would involve
being able to refer back to that table for the fields Name and Surname rather
than duplicating them in another table. In order to be able to do that referral
we need to include the Student Id field in the Results table. This will
have a number of advantages. One
advantage is that we can use the data in the Student ID field in the Student
table to validate the data in the Student
ID field in the Results
table. This means that we can cross
check the data entered in the Student ID
field in the Results table against
the data in the same field in the Students
table. We do this using a Lookup function which lists the data in
the Student table for us and then we
simply select the appropriate student from that list. This means that the only data that can go
into the Student ID field of the
Results table is data that already exists in the same field in the Student
table.
Figure
6‑1
Let us now look at how we implement that Lookup function. To begin with we
select the Student ID field in the
results table and then in the lower half of the dialogue box
click on the Lookup tab. The first item we alter here is the Display Control property. Here we can specify what type of control we
are to use to display the data from the Student table. The options available are “Text box”, “List
box” and “Combo box”. In our case we
shall pick the “Combo box.
The next property we modify is the Row Source Type. This specified where the data that is to be
displayed in the combo box is to come from.
The options are “Table/Query” and “Value List”. The first option “Table/Query” indicates that
the data is to come from a table or a query that already exist in the database
itself. The second option indicates that
we shall supply the combo box with a list of data ourselves. In the case of the Student ID that data is to come from the Student table and thus the
appropriate value for Row Source Type
is “Table/Query”.
Once we have specified that the data is to
come from a table or a query our next job is to specify which table or query
the data is to come from. To do this we modify the property Row Source. Once we click on this property a drop down
list shows us all of the tables and queries in our database. From that list we select the table Students.
The table Students has 6 fields, any one of
which could have its value stored in the Student
ID field of the Results
table. In order to specify which field
we use the Bound Column
property. In this case we have given it
a value of 1. This indicates that the
first field of the Students table,
i.e. the Student ID field, will be
the field that will store its value in the Student
ID field of the Results
table. How this would work is that in
the datasheet view of the table Results
the field Student ID would be in the
form of a drop down list or combo box, and once we clicked on it the list would
be filled with the values of the Student
ID field of the Students
table. This ensures that only values
from that field will be stored in the Student
ID field of the Results table.
If we are entering student marks then
picking a student using his ID can be difficult as we are more familiar with a
student’s name and surname than his ID number.
For this reason it would be helpful if our drop down list also displayed
the name and surname of the student.
This can be achieved by using the Column
Count property. In our case it is set
to 3. This means that the first three
columns or fields of the Students
table will be displayed in the drop down list.
As the first three fields are Student
ID, Surname and Name those are the fields that will be
shown in the list, as can be seen in Figure 6‑2.
Figure 6‑2
Using a value
list
In our use of the Lookup facility we have used the data from the Student ID field of the Students
table to fill up the for the Student
ID field of the Results table.
If we turn to the Subjects field
of the Results table we cannot use another table
as a reference since there is not any table in our database that stores data
about the subjects. We can, however,
use a combo box where we type in ourselves the values that will appear in
its drop down list. Let us assume that the values for this list are “Mathematics”,
“Mathematics A”, “Chemistry”, “Physics”, “Language and Literature”, “Geography”
and “History”
To create the value list we select the Subject field and go to its Lookup tab. Again we select Combo Box in the Display Control property but in the Row Source property we select Value List. In the Row Source” property we enter the list above with each entry of the
list separated by a semi colon. This is
shown below in Figure 6‑3
Figure 6‑3
When we turn to the datasheet view of the
table the drop down list appears as in Figure 6‑4
Figure 6‑4
Implementing
table relationships
Although the Lookup facility that we have used above
will prevent us from entering any student id number into the Results table that is not already in the
Students table, SQL statements and
programme code can still enter any value for the student id
that they wish, as they will bypass the Datasheet
view of the table. In order to protect the table fully from wrong
data being entered into it we will have to officially declare the relationship
between the tables Students and
Results.
In order to do this we click on the tab Database Tools and then on the icon Relationships. This gives us the window and dialogue box
shown in Figure 6‑5.
Figure 6‑5
Initially the window will be empty but the
dialogue box Show Table will contain
a list of all tables in the database. In
order to add a table to the Relationships
window we can either double click on the table name or else we click on the
table and then click on the button Add. Once the tables are added to the Relationships window we can now
commence establishing the relationship.
The relationship we wish to establish is a One-To-Many one. Let us first explain what this means. In the Students table each value in the Student ID field occurs only once since
the field, Student ID is the key
field. On the other hand, in the Results table, the same value can occur
in the Student ID field as often as
is required. In reality the each value
for Student ID will be repeated for
each test and examination that the student sits. Thus each value of Student ID occurs only once in the Students table but occurs many times in the Results table. For this
reason the relationship is referred to as a One-To-Many relationship.
In order to create the relationship we put
the mouse pointer down on the Sdudent ID field
in the Students table and drag it
across to the Results table. Here you drop it on the Student ID field. Once you
do this the dialogue box in Figure 6‑6 will
appear.
Figure 6‑6
Here the tables and fields are specified.
To the left is the table on the One side, the Students table. Under it the field is
shown – Student ID. To the right is the table on the Many side, the Results
table. Again the related field, Student
ID, is shown. In order to ensure
that the relationship is a One-To-Many one you must check the box labeled Enforce Referential Integrity. Leave
the Cascade check boxes unchecked. Once
you click on Create the relationship
is created as shown in Figure 6‑7.
Figure 6‑7
Using Queries on multi-table
databases
Our data is now separated between two tables.
From our previous examination of queries we know how to do a query
for a single table. Here we shall look at how to create a query
that takes its data from two tables - Students
and Results. As
before we fire up the query design box which gives us an interface as in Figure 6‑8. The difference between
that case and the present case is that we now have two tables which we can
add to the design. Here we add both
of them, which gives us a display as in Figure
6‑8
Figure 6‑8
We simply drag the fields we want from each
table into the grid below, then save and run the query.
Creating Form/Sub
form for related Tables.
In the two tables that we have been looking
at in this chapter we had to establish a relationship between them in order
that the system would be aware that the results stored in the table Results
belong to students from the table Students. In the table results itself the
field Student ID is the link between the two tables.
Entering data into the Results table can be
somewhat awkward as the only reference we have to the student is the Student Id
field. We would have to be continually
referring between the two tables in order to make sure which student we are
dealing with at any time.
Access has a way out of this problem for
us: forms/sub forms. Using this type of
interface we have two forms in one. The
main form will be the Students form and the sub form will be the Results. While the results form is a subform of Students it will only display the results of the
current student record. And as we move to a new student record the display in
the results form will alter to the results of the new student.
The first stage in creating a form/sub form
is to create the main form. This form is
shown below in Design View. Notice that there is a large space between
where the fields end and the bottom of the form. This is the area where we are going to put
our sub form.
Figure
6‑9
Next we create our sub form. It is created just like any normal form, but
its layout is normally Datasheet. If you are creating it using a form wizard
ensure that in the stage where you are asked for the layout that you specify Datasheet as shown below in Figure
6‑10
Figure 6‑10
Once the wizard has completed the form,
save and close it. Next open the Student
Results form in Design View
again. In the left hand panel beside the
form are the names of the other forms in the database as shown in Figure
6‑11
below.
Figure 6‑11
Here click on the icon of the Results form
and drag it over to the Students Results form. Drop the Results form where shown above. Now if you go into Design View the forms will appear as in
Figure 6‑12.
Figure 6‑12
Summary
Efficient use of a database involves having
the data separated into different tables. Most of the time we will have tables
that contain reference data that will be used to check the data in other tables.
An example of this is the Students
table in our database here. It is used as a reference for the Results table. More precisely the Student ID field in the Students table is used as a reference for
the Student ID field in the Results table. There are two ways to establish this relationship.
One way is to use the Lookup property of the Student ID field in the Results table. We use this property to ensure that
the user can only enter values that already exist in the Student ID field of the Students table. The other way to do this is to use the Relationships window to formally establish
a One-To-Many relationship between the two tables.
Exercise 6
Do the following modifications to your library
application:
1)
Create a table called Authors
according to the following specifications
|
Field name |
Data type |
Size |
|
Author Code |
Text |
6 |
|
Surname |
Text |
20 |
|
Name |
Text |
20 |
|
Address |
Text |
20 |
The field Author Code will be the key
field.
Enter about 6 records into the table.
2)
In the Books table delete data
in the Author field and modify it as follows:
Change the size to 6
Add a validation rule so that it accepts 6
numeric digits
Add an appropriate validation text
Modify the Lookup so that it receives data
from the Author Code field of the Authors table through a combo box.
3)
In the same table modify the
Status field so that it gets its data from a value list. The values will be “On shelf”, “On Loan” or
“Missing”.
4)
Add a further table called
Publisher. One of the fields will be
Publisher Code. This will also be the key field. This is to be interfaced with
the Books table in exactly the same way as the Author table.