Hello again and welcome back to our course on Access 2016.
It’s time now to do some real work on creating a working database.
And the main example that I’m going to use during the course is the Tour database.
We created an empty starting point for that database a little while ago.
And what we’re going to do in this section is to start to add some content.
Now before we actually begin the first thing I want to do is to change what’s shown in
the title bar there.
By default I get the file name, including the location on my device, the format, etcetera.
Let’s go into Access Options and give that a title.
Now really the core of any Access database, in fact any database is the data.
And the data in modern databases are held in tables.
And for the Tour database we are going to have a number of tables.
Now the first table we’re going to create is the table that really contains the details
of what we call our core trips.
And a trip is one particular entity, something that one of our travelers can do.
And during a course of a year we may run a trip several times and each instance of a
trip we’re going to call a tour.
So say in a trip that we run all year round, say it’s a two or three week trip, we might
run it 15 or 20 times throughout the year.
So that one trip will have many tours.
So the first thing that I’ve done is to put together an example of the kind of information
that I’m going to keep about a trip.
Each trip will have a code.
And the code for this trip is GCA.
And GCA, those letters are arrived as sort of abbreviation of the name of the trip.
The name of the trip is the Grand Canyon Family Rafting Adventure.
The type of trip.
Well it’s actually got several types.
It’s a family trip, it’s escorted, it’s a group trip, involved activity, and there
are landscapes to admire on this trip.
The activity level is moderate.
The country visited is the U.S.A.
And then there is a description of the trip, just a sentence or two normally.
Now of course later on we may add much more detail about the trip, such as a full itinerary,
but for the moment we’re just setting up a description.
The duration of the trip is stored in days.
So eight days normally means seven nights.
And the price range, excluding flights, is in the range $3,080 - $3,112.
Individual tours, so instances of this trip, the prices will vary depending on, for example,
the time of year, whether it’s in school holiday, school vacation time, for example.
And also to some extent will tend to vary the prices to reflect demand.
So we maybe drop the price when the trip is not quite so popular.
Now all of that information ultimately will be stored for a trip.
But there’s one particular part of it, the type, that I am not going to store in the
Trip table at the moment.
Now the reason for that will become apparent to you later on in the course.
I’m going to come to that in a few sections from now.
You may be tempted once you’ve looked at how I store the other information about the
trip to store the type in a similar way to the way that we’re going to store activity
level.
But in fact from a database design point of view the facility to do that in Access is
not a very good one.
And also if you do use the facility in Access to store those multiple type values it will
cause you a certain number of problems later on.
So I’m going to skip type at the moment but we’re going to setup everything else
in our first table.
Now there are a couple of other things I’d like to point out before we get started.
One of them is that one or two of things that I’m going to do here I will correct or change
or replace later on.
It’s almost making deliberate errors really, not quite but almost, because some ways of
doing some of these things really come later on when you become more familiar with other
aspects of Access.
And I want to do things in a pretty straightforward way to begin with.
Now let me give you an example of the sort of thing that I’m talking about.
If you look at that trip definition, Country U.S.A., you might say to yourself, “Well,
hang on a minute.
Surely some of the trips are going to go to more than one country.
Shouldn’t I have a list of countries that are visited on the trip there?”
Well the answer to that is of course yes you should, but I’m going to deal with that
later on.
And in fact that reflects the fact that even the most experienced database designer and
developer will need to change their design, improve and extend their design later on in
the life of a database.
So it’s just not possible to get everything right at the outset because the requirements
change for one thing, never mind any increase either in your own knowledge or in the facilities
of the database system that you’re using.
It may be that the next version of Access brings in a facility that will give us a better
way of doing something that we’re doing here.
But of course we don’t know that yet.
Having said all of that it’s a very good idea to get things as right as you can early
on because changing things in database design is itself a bit of a fraught process.
Some things are easy to change, some of them involve quite a lot of what I would call jiggery
buggery in order to make them work.
So some things you need to change can be a real pain.
So let’s get started creating this first table, the table that’s going to hold the
trip information.
Now in order to create a table I go to the Create tab on the Ribbon and click on Table.
And that creates the first table.
Now that table has a default name of Table 1 and it’s opened in what is called Datasheet
View.
You can tell that it’s Datasheet View initially by looking down at the left-hand end of the
Status Bar.
And you may recall when we looked at the Status Bar we looked at the Status Bar when we had
a form open.
And the options at the right-hand end of the Status Bar enabled us to switch between the
three available views for a form.
Now with a table we have two available views.
We have Datasheet View which is the one that you can see at the moment and then if I use
the button at the extreme right-hand end we have Design View.
Now one of the things that’s going to happen a lot when you’re working on a table is
that whenever you do something to a table in terms of design and in this case creating
the table in the first place is treated as a design operation, you will be asked whether
you want to save changes.
And in fact when you first create a table with in this case a default name of Table
1 the first thing that Access 2016 asks us to do as soon as we even try to go into Design
View is to give the table a name.
Now this is the trip table so I might be tempted to give it the name Trip.
But I’m going to follow a convention that a lot of database designers and developers
follow whereby the objects in a database have a prefix which basically tells us what type
of object it is.
Now in the case of tables the prefix that we use is TBL.
So I’m going to call this tblTrip and that will remind me when this table name crops
up in all sorts of situations later on that it’s the trip table.
Now this might seem like a strange thing to do to you at the moment.
I can assure you that it isn’t.
But I might later on have a form where I maintain trip information.
I might call it the trip form.
But that would have a prefix of FRM.
So I’d call that frmTrip.
And when I wanted to refer to something called Trip that would make it clear whether I meant
the table because I’d write tblTrip or the form because I’d write frmTrip.
So the trip table is called tblTrip.
Now I’m in Design View for the trip table.
And in Design View I can see the individual fields that make up the table.
And by default I have a single field.
It’s name Field Name, which you see in the first column there is ID.
It has a data type of AutoNumber.
And I can put a description in as well.
Now the description I’m going to put in here is Unique Trip ID.
This is not the same as the trip code.
This is a unique identifier which in many of the entities that we’re going to design
tables for is a very important part of the design of the table because the contents of
this field will be a number.
And this number will be absolutely unique within the table.
So once we’ve given a trip a numeric ID it will absolutely uniquely identify that
trip.
Now the importance of that unique ID is best explained with an example.
Let’s suppose that I set up a trip, let’s say this GCA, the Grand Canyon Adventure trip,
GCA.
We set it up, we run it for a few years and then we drop it again.
Now if at a later stage we create a different trip called GCA we would want to be able to
differentiate between the old GCA and the new GCA.
And we would do that using the unique ID.
One very important aspect of unique ID’s is that very often they are not visible to
users of the system.
They’re used, if you like, for internal housekeeping.
They’re not a code that users of this trip database will refer to.
Users will refer to it by the trip code, for example GCA, probably be completely unaware
that there is a unique numeric code as well.
Now before we start to add the fields to the trip table.
I’m going to call it the trip table even though it’s real name is tblTrip.
Let’s call that property sheet on the right.
That property sheet on the right relates to the whole table.
I’m not going to worry about table properties overall at the moment.
And now let’s add a second field.
Now the second field will be the first one that was in that little list that I showed
you just now.
So that’s going to be the trip code.
I’m just going to call it Code.
Now when I enter a field name for that, Code, click in data type.
There is a default data type of Short Text.
And in fact there are many, many data types in Access and we’re going to working our
way through these over the coming sections.
Short Text is one that we’ll use quite a bit.
Now as a description of that I’m just going to put in here at the moment three character
trip code.
And with this particular field selected, so I’ve got the second row in Design View of
this table.
If you look at the bottom of the screen you can see the properties of that field.
There is a field properties grid.
And the very first item in that grid, Field Size, gives us the maximum number of characters
that can be held in that field.
By default it’s 255.
I’m going to change it to three.
Now there are many properties and some of these, such as Format and Input Mask and Caption,
I’m going to come back to later on.
I’ll also come back to Default Value, Validation Rule, Validation Text.
But I am going to look at Required.
In order to store a trip in this table must if have a trip code?
Yes, it must.
So Required which defaults to No needs to say Yes.
Now I can either delete the word No and type the word Yes or I can use the dropdown arrow
at the right hand end there and change No to Yes.
Could the trip code be a zero length?
No, it couldn’t.
It has to actually have three characters.
So the answer to that is No.
And finally will it be indexed?
Now I’m going to explain keys and indexes at the start of the next section.
For the moment the answer is Yes but the trip code cannot be duplicated.
So I’m going to click on that one, click on the dropdown and change it to Yes, no duplicates.
And what I now have is the definition of the second field in my trip table.
It’s code, it’s a three characters short text field, it’s required, and it is indexed
but you can’t have duplicate values in there.
So that’s it for this section.
In the next section we’re going to carry on and set up a few more fields for the trip
table.
Please join me for that.