Welcome back to our course on Access 2016.
In this section we’re going to start to look at Relationships.
Something that I haven’t mentioned before is that Access is a relational database management
system.
And with Access you create relational databases.
What we mean by a relational database is that at the basis, at the core of the system is
a set of tables and the relationships between the entities in those tables.
Now in order to demonstrate what we mean by this I’m going to create a relationship
between two tables.
And since we only have one table so far in the Esprit de Tour database, that’s the
trip table, I need to create a second table.
And that’s what I’m going to do first in this section.
Now very early on in the course I talked about a trip type.
And I pointed out at the beginning that I wasn’t going to store trip type in the trip
table.
What I’m going to do now is to create a new table and that table will hold a list
of the types of trip that Esprit de Tour offer.
So first of all we go to Create.
Now on this occasion I’m going to click on Table Design and that gives me an empty
table without even an ID field in it because on this occasion, and this is a little bit
controversial, not everybody will agree with this, but on this occasion I’m not going
to have an ID AutoNumber-type field.
I’m only at this stage going to have a single field in this table and it’s going to be
Type and it’s data type will be short text.
Now given that it is the only field in the table I need to make it the primary key of
the table.
So the type itself will uniquely identify a record in this table.
So I select this field, then I can make it the primary key by clicking the Primary Key
button in the Tools group on the Design tab.
That is now the primary key of this table.
Now let’s check some of the other properties.
Field size.
Trip types won’t generally be very long so I’m going to set a maximum size of 30.
I’m going to say that definitely a type is required.
It’s the only field so without that there is no record.
Do I allow zero length?
No, I don’t.
And is it indexed?
Yes, but no duplicates.
As it is the primary key we couldn’t have a duplicate value here.
So basically that is the single field in my new table.
Now let me save that table.
And I’m going to call it tblType.
So let me open up my type table.
Of course it’s completely empty at the moment but I know how to enter data.
And the first record that I’m going to enter is Activity.
So that is one of my types.
Now by the way I mentioned in a preceding section how you can always save these records
by just clicking on the next one.
There are various ways that you can move on to the next record to enter the data.
If I press the down arrow key now it takes me to the next record.
Of course it validates the one that I’ve entered to make sure that it’s a valid set
of fields.
In this case a single field.
So the next one is, so I’ll just type in the rest of these and join me again at the
end of me doing that.
Now having done a set of typing like this don’t forget there is a spell checker in
the Records group.
It will only apply to the current data set in this case.
But if I click on Spelling I’m hoping it will tell me if I’ve misspelt anything.
Nope, it seems to be happy with all of that.
So they’re the values for my types.
Obviously at a later stage I may introduce other types of tour but that’ll certainly
get me started.
Let me close the type table.
Now what I’m going to do is to go the Database Tools tab and I’m going to open the Relationships
window.
Now this window shows the relationships that exist between the tables in my database.
At the moment I have no relationships.
But I do have this Show Table dialog.
And what this Show Table dialog does is to enable me to control which tables are shown
in the relationships window.
Now I need to show both the trip table and the type table.
And what I now need to do is to create a relationship between them.
And the relationship between them will define the fact that a trip can be of many types.
Now in order to do this we normally introduce a third table.
And this third table in this case will be what many people call a link table, linking
trips to types.
And I’m going to create that link table next.
So I’m going to close the Show Table dialog for the moment and I’m going to click on
Create and this time I’m going to say Create Table and I get that ID field.
I’m going to need an ID in this case but let me go into Design View.
This table is going to be called tbl and it’s going to be TripType.
And this is the table that is going to enable me to link each trip to the type of trip that
it is.
So let’s click on OK and what I need to do in this table is to specify apart from
the unique ID two things.
I need to specify a trip and I need to specify a type.
Now by default each of these fields is a short text field but that is not always the case
that these actually turn out to be short text fields, as you’ll see in a moment.
Now the trip field needs to be able to uniquely identify which trip I’m talking about, which
trip is being linked to by a record in this table.
And as you know with a trip the unique identifier, let’s go into the trip table Design View.
Notice how I’ve now got three different things open, but this is the trip table.
The unique identifier, the primary key in the trip table, is the ID.
And that is an AutoNumber field.
And in order for the trip type table to uniquely identify a trip it needs to store the primary
key value for the trip it refers to.
Now as you will discover and you will use many, many times when we are referring to
an AutoNumber field the type we want here is not short text.
It’s actually a numeric field and it’s actually a long integer.
And in order to make it clear what I’m referring to here I’m going to change the name of
this field to TripID just to emphasize the fact that this field refers to a trip ID,
which is the primary key of a trip record.
So.
Now when a field in a record points to a primary key in another record, in this record this
is what’s called a foreign key.
So within the table TripType TripID is a foreign key and it points to the primary key in a
trip record.
Now let’s turn our attention to type.
Now in this case, if you remember, let me just open up the type table again in Design
View.
Now in the type table the primary key is actually a short text field.
It’s the trip type.
And there’s absolutely no reason that I shouldn’t use that as the primary key here.
And therefore in the trip type record in the trip type table when I refer to the type I’m
referring to a short text field.
So this needs to be short text as well.
And this is, but it’s a unique identifier in the form of a piece of short text.
And in fact it is the primary key of the type.
Now one of the reasons that I created the type records in the way that I did this time
is to show that although you will very often refer to AutoNumbered ID fields and the foreign
keys will be of type long integer you will sometimes use short text fields as well.
In fact there are one or two other possibilities but those two cover most situations, either
long integer or short text.
Now I’ve just got to check the properties for each of these now.
So I’m looking at the table trip type.
The type field it says maximum 255.
Well of course it only needs to agree with the maximum for type which is 30.
Is it required?
Yes, it is.
Do we allow zero?
No, we don’t.
Is it indexed?
Yes, it is.
And it’s a case that duplicates are okay because the same type may be used by many
trips.
Now let’s just check TripID.
This is a long integer of course because it’s referring to an AutoNumber field elsewhere.
Is it required?
Yes, it is.
And is it indexed?
Well yes it needs to be indexed as well.
And again duplicates will be okay because the same trip may occur in many trip type
records.
So we’ll save that and we’ll close that and we’ll close that and we’re back to
our relationships table.
If I right click in the relationships window one of the options is Show Table.
That brings up that dialog again.
And I’m now going to add the TripType table to the relationships window.
Close the Show Table dialog again.
I can drag these table rectangles around.
And on this occasion it’s very helpful to see what’s going on if I put the TripType
table between the other two.
What I now need to do is to set up the relationships between the tables in my Esprit de Tour database.
And that’s the point at which we’re going to start the next section.
Please join me for that.