Hello again and welcome back to our course on Access 2013.
It’s time now to get started on design and build of a database in detail, and we’re
going to start with tables.
The main sample database we’re going to use is a movies database.
We won’t get terribly far in the design and build of a movies database in the time
of this course, but we should make a pretty good start and I hope you’ll get plenty
of ideas to be able to carry it on later on and after the course is finished.
Now in terms of exercises for you to do, I will be setting these and there is absolutely
no reason why you shouldn’t work on a completely different database yourself as we go along,
maybe something associated with your work, the job that you do, maybe something associated
with an interest you have at home.
And it’s entirely up to you.
The examples I’m using will mainly be around the movies database, although there will be
one or two others from time to time.
If you follow along with what I’m doing, then you will find that you cover all of the
main things that you need to know.
One thing I should warn you about though before I start is that in the early stages, this
movies database is going to be pretty badly designed as you’ll see.
I’m deliberately going to do a bad job for a while in order to illustrate what a good
job is.
So let’s get started.
Okay, I’ve been working on a database here.
I’m going to close it in the usual way.
No changes.
And I’m going to go for a new database.
Now in this case, I’m not going to use any of the existing templates.
I’m going to go for Blank desktop database.
I’m going to give it a name and I’m going to number these versions of this database
as we go along and that will tie in with some of the exercises that there are to do.
So this is Movies 1.accdb and I’m going to create it in that sample folder that I’ve
used from time to time so far.
So I’m going to put it in that folder with the contacts ones, and then I just click on
Create and my database is created.
Now note that when it’s created, the number of objects is very small.
In fact, it opens a dummy table.
One thing’s for sure in an Access database, you’re going to need a table.
Now let’s suppose that I just want to start getting some data into this table.
Supposing I type in here the name of a movie, here’s a new movie, press the Tab key, and
now let me put in the year of release and let’s suppose at the moment all I want to
record is the name of the movie and the year of release.
Now you may recall from earlier on that in order to save a record to this database or
specifically into this table, I just need to go to the next one, the next record, the
one that says New, and I’ve got a record in my database table.
It says Identity Thief and it says 2013.
But that’s a pretty messy job to be honest with you because I haven’t really looked
at the structure, I haven’t really looked at what Identity Thief and 2013 are and so
on.
Now you may recall that earlier on we talked about the different views of a table and we’re
currently in Datasheet View.
So we’re looking at the data.
What I want to do is to switch to Design View.
Now there are various ways of doing this.
If you look at the buttons at the bottom right hand corner of the screen on the status bar,
there are two buttons.
The one on the left currently selected is Datasheet View.
I can click on the one on the right, Design View.
There’s also a button on the left of the Ribbon to switch views as well.
But when I’m in Design View, I get a little bit more idea of the structure of my new table,
although I can’t see the data anymore at all in Design View.
And what Access 2013 has done is to give me three fields.
The first field ID I’ll come back to in a moment.
The second one where I typed in Identity Thief it’s called Field 1.
That’s a default name.
I’m going to change that name.
I click in there and I’m going to change that to, say, Title.
And of course the third one should be Year of Release.
Now before I do that, let me just switch back into Datasheet View again.
Note whenever I’ve made changes, it says you must first save the table so I’m going
to save the table.
Back in Datasheet View.
I can actually click in the top, in the title here, and if I right click on there, I can
say Rename Field and I could put in there YearOfRelease.
Now my preference is not to put spaces in field names, but it is a preference.
There are one or two technical reasons why it helps me a little bit not to have spaces.
If you wanted to put Year, space, Of, space, Release that would work fine.
Now let’s go back into Design View and as you can see we can put those field names in
either way.
So we’ve got Title and we’ve got YearOfRelease.
Now for each of these fields when we enter the data to begin with, Access 2013 took a
look at the data and tried to work out what the ideal data type would be.
Now in the case of Title, it’s selected a data type of short text.
A data type of short text if you look down at this grid at the bottom means that there
is maximum field size of 255 characters.
Now I’ve no idea what the longest movie title in the world is but 255 sounds about
all right for me to cover the vast majority of movie titles.
And to be fair, in many situations it’s possible to change the data type and/or the
field size later anyway.
But for the moment, I think 255 looks fine.
Now when it comes to YearOfRelease, Access 2013 has looked at the number we typed in
and decide yes this is definitely a number data type.
And the type that it’s put in is one that’s called long integer.
Now long integer at the bottom covers very, very large numbers and we wouldn’t need
them for a year of release, given that in terms of the year we’re not far above 2000
now, it’s going to be a long time before we need something that goes into the billions.
So again we could take the value that Access 2013 has suggested here but we may want to
review that later and in some way restrict it to being for example a year bigger than
1890 which is roughly where movies started and probably less than say 2100 which will
give us several years for our database to work in, but more of that later.
For the moment, I’m going to stick with the Title is short text and the YearOfRelease
is number.
Now I’d like to go back to this first field again, this ID field, and talk about this
because if you look at the Ribbon, at the Design Tab, note that it’s a contextual
tab under Table Tools that is there when I’m in Design View on a Table, you’ll see that
that’s highlighted and it’s highlighted because the ID field is the primary key for
this table.
Now when I create a table in Access 2013 under normal circumstances, it will always insert
an ID field and the ID field will have a data type of AutoNumber and it will be defined
to be the primary key for this table.
Now there are quite a few important concepts in all of that.
Let’s start with primary key.
Now as a general rule, we need to be able to uniquely identify any record in a table.
In the case of our movie table, we need to be able to uniquely identify any movie.
We must have one thing that says that is specifically that movie.
Now you may say “Well, isn’t the title a unique identifier for a movie?”
Well, let me give you an example.
What about the movie True Grit that you may or may not have heard of?
Now True Grit is a 2010 movie but in fact it was a remake of a much earlier movie, a
1969 John Wayne movie that both have exactly the same title, True Grit.
So True Grit is not a unique identifier.
You can’t use the title of a movie as a unique identifier.
In that particular case, there was also a TV movie made in 1978 called True Grit.
Now one option would be to combine together in some way the title and the year of release
and say “Well, if you put the two together isn’t that unique?”
Because in one case we would have True Grit 1969, then we would have another one True
Grit 1978, and another one True Grit 2010.
Well, that would probably work most of the time and provided that nobody ever brings
out a movie or a couple of movies with the same title in the same year, you’ll be fine.
When somebody does, there may be a problem.
But in fact the preferred method almost always is to use an independent identifier to uniquely
identify a movie.
What we’re doing in this case is saying this number, the number that is assigned automatically
by Access 2013, is the unique identifier for this movie.
Now that doesn’t mean to say that users have to know what this number is or in fact
that users ever see this number, but within our database system the unique identifier
is the one that we use to uniquely identify an object in the database.
Now that is the purpose of this first field, this ID field, and we will very often have
an ID field for a table.
Now let’s suppose that I did want to use the title and year of release to uniquely
identify a record in a table.
Now it’s very straightforward to do that because all I have to do is to select the
two fields, using the mouse I can just sweep over those two fields, and then if I click
on the primary key button on the Ribbon, they become together the primary key.
They’re not individually the primary key.
They are only the primary key when used together.
So you can easily change, but I don’t want to do that.
I’m going to go back to making the primary key just ID.
The Undo button comes in here.
So if I change my mind, click on Undo and I’m back to just the ID on its own being
the primary key the unique identifier for a record in the table.
Now I’d like you to see AutoNumber at work.
So I’m going to go back to Datasheet View and enter some more data.
Watch what happens when I switch back to Datasheet View.
As always, you must first save the table.
Do you want to save the table now?
Yes.
Access knows that I’ve made changes so I click on Yes.
Now then let me go to New record and the title of the new record is going to be True Grit.
Now as soon as I start entering that next record, it is given an ID, a unique identifier.
That number three will never be reassigned in this table again.
Three is unique.
It will only ever be used once.
It’ll be completely unique.
Press the Tab key to go the next field and enter there 1969, the year of release, then
I can click in Title on the next new record, type that again, Tab, put in the year, and
so on.
Now, if I make a mistake or delete one of these records or I have to go back, Access
2013 will never reuse these numbers.
So those numbers, those ID numbers are always unique.
That’s an important point.
And they’re AutoNumbered so you don’t have to worry putting the numbers in.
Access 2013 does that for you.
Now it’s time for you to do some work on this database or on your equivalent.
I’d like you to take what’s in movie-01, if you’re following along doing this movie
database, and basically what’s in movie-01 is what you can see here.
I’d like you to add any other two movies.
In fact, you can add more if you like, but you need to finish up with at least five movies.
In each case, you’ll need the year of release as well.
And I’d also like you to create a second table.
The second table is going to be called Actor and in that I would like first name and last
name and year of birth.
So straightforward enough, first name, last name, year of birth, and put in, say, three
of the actors that appear in one or more of the five movies that you’ve added to the
first table.
Now if you’re a bit short of knowledge about movie titles and actors and so on, what you
want is www.imdb.com, the Internet Movie Database.
Thousands of films, thousands of actors, all the information you’re going to need for
this course.
You can get it all from there.
My answer to that is example-02 in the files provided with the course.
There’s just one thing I need to mention before you start using the sample files for
the course.
When you open the sample files with all or any of them, you may see a message like this;
a security warning.
Some active content has been disabled.
Click for more details.
I can assure you that the content for the course is safe.
So when you see that message, you need to just click on Enable content and it will go
away.
Later on in the course when we look at security issues, you’ll get an explanation of why
that sort of thing appears.
But for the moment, if you see that, click on Enable content and all will be well.
I’ll see you in the next section.