Hello again and welcome back to our course on Access 2013.
In the previous section we created the first draft of a movies database and we created
the movie table, put some data in it, and I left you with an exercise to do to create
an actor table and put at least three actors in it.
I’ve got a copy of my answer to that in front of me here.
My answer was example-02 in the files that you get with the course.
This is a copy of that.
There are the two tables.
I want to look at the actor table first.
So if I want to look at the data, I just double click and it opens the table in Datasheet
View.
Now as we can see, I’ve got three actors in here, fairly well known, although perhaps
if you’re a young person you may not have heard of any of them.
I don’t know.
And these three actors I’ve got very basic information: first name, last name, year of
birth.
Now I want to add some more information about each of these so what I need to do is to add
some fields to this table, and the first field that I want to add is each actor’s gender.
Now I can do that either by using the space on the right here.
So for instance, if I thought, well let’s just put M and F in for Male and Female.
So Humphrey Bogart is M, Ingrid Bergman is F, John Wayne is M and automatically Access
2013 gives me a new field called Field 1 and then allows for another inserted field on
the right here.
So it’s very dynamic in terms of letting you add fields when you realize you need more
information.
Having added that data though, if I go back into Design View, so click on Design View.
I can see that it’s added my field.
Now I don’t want it to be Field 1.
I want to change the name to Gender, and the type I also want to change because I don’t
want it to be short text.
I only want there to be two possibilities here.
I either want the gender to be M or F. So in the drop down at short text, if I click
on that, I want to change from short text to lookup.
Now the idea of lookup is that I can look up potential values from a limited list of
the possibilities and in this case the limited list is just M or F. So let’s look at the
Lookup Wizard.
So the first screen says: The Wizard creates a lookup field which displays a list of values
you can choose from.
How do you want your lookup field to get its values?
You get a choice.
I want the lookup field to get the values from another table or query or I will type
in the values that I want.
Now I’m going to type in the potential values.
So I’m going to choose the second option and click on Next.
What values do you want to see in your lookup field?
Now if there is more than one piece of information to choose from in the sense that you need
to show somebody say a number and an explanation you’d have more than one column.
But for this we only need one column and the possible values, well the first value is going
to be M and the second value, potential value, is going to be F, and they’re the only two
values that people can choose from.
So click on Next.
What label would you like?
Well, the label of Gender would be fine.
And then under that there’s a very important checkbox, Limit to list.
Do you want to limit entries to the choices?
Sometimes when you give people a choice of what to put in a field you say to them you
could have this or this or this and in fact you could also type in your own choice if
you wanted to.
So you’re helping them by offering them a choice but you’re not limiting them to
those choices that you’ve given them.
Now in this case there are only two possible choices, an actor is M or F. So in this case
we’re going to check Limit to list.
The next question on that page we’re going to come back to later on.
Do you want to store multiple?
Well, let’s just ignore that for the moment and click on Finish.
Now what we get is a still a short text value but on the Lookup Tab in the grid at the bottom
we’re told that the row source, the third entry here, lists the possible values that
will appear as our choices.
Now I’m going to save the changes we’ve made to this table and then show you how this
works in Datasheet View.
So let’s do a save and then let’s switch to Datasheet View.
Now watch what happens when I add another actor.
Now having put in the basic information, when I get to the Gender field now I get a control
that is effectively what’s called a Combo box.
And I have a drop down arrow to the right.
If I click on the drop down arrow I see the choices M or F. I can’t type in anything
other than M or F. I’m limited to this list.
But I select M and that’s it and then I’m ready to move on to entering details of the
next actor.
Now I want to do a similar thing to the movie table next so I’m going to close actor,
save the changes, and I’m going to go into the Design of the movie table.
And what I want to add here is the genre of the movie.
So I’m going to add a new field called Genre and I’m going to say that it’s short text
and I’m going to make it lookup as well.
Now this time when I say Lookup Wizard, I’m going to say I will type in the values that
I want, click on Next.
I’m still only going to only have one column but let me put as my genre options Comedy,
Mystery and, say, Thriller.
Click on Next.
I’m going to say Limit to list again but this time I’m going to say “Do you want
to store multiple values for this lookup?”
And I’m going to say yes I do and then click on Finish.
So save that and then go into Datasheet View to look at my data.
Now let’s choose the first movie, Identity Thief, click in Genre, click on the drop down,
and this time in this drop down I have my three entered options but I get checkboxes
next to them and I can choose more than one option.
And if I choose Comedy and Thriller and click on OK, look what happens; I get Comedy and
Thriller.
Now one thing I should just point out, be looking at this a little bit more later on,
the Datasheet View works like an Excel spreadsheet.
So if a column is not wide enough, you can just drag it a bit wider; there we are.
Now we can see that the genre for Identity Thief is Comedy, comma, Thriller.
So I’ve got two values.
Now if you know much about database design you’ll know that this is pretty bad design
for a database and I’m going to change this a little bit later on.
So if you’re looking at that and perhaps feeling a little bit horrified, don’t worry
too much about it because I am going to fix that later.
But it does demonstrate something that can be useful, which is the ability to get multiple
values in a field like the Genre field.
And it’s a very straightforward way of doing it as well, as you can see.
Now the number of possible values for the genre of a movie, it’s quite a long list
but it’s a fine art list, maybe 10, 15, 20 different options, something like that.
But supposing that I also wanted to put into my movie table a list of the actors in the
movie.
Now let’s go back to the design of movie and let’s suppose I was going to put in
here a list, Actors.
How would I put the list of actors in there?
Would I do it like Genre?
Would I have first actor, comma, second actor, comma, third actor, comma, fourth actor?
The answer to that is definitely No, although many years ago some people would have designed
databases like that.
If you have a long list and particularly if you have a very variable list such as the
actors in a movie or the crew in a movie, or what about the special effects technicians?
There are sometimes hundreds of them.
You wouldn’t make a long list of them with commas in between their names.
We need a different way of putting long lists of things together.
The other alternative is to say have an Actor 1 field and an Actor 2 field and maybe an
Actor 3 field, and with all of those you could put in the actor, the main actor, the headline,
the second actor, so on.
How many of those would you allow for?
How many actors is the most that you might need?
You can see that it’s not that easy to design a table where you’re not quite sure how
many of something you need.
Now what we’ve got so far in the movie table is very straightforward.
We’ve only got one title and we’ve only got one year of release and the genre although
there could be one or two or three or four of them there won’t be that many.
But when it comes to actors or crew, then you’re in a different situation altogether
and as I say that’s what we’re going to look at in the next section.
Now in readiness for looking at relationships between tables in the next section I want
to do one more thing in this section and that is to create a new table, and the new table
is going to be a genre table.
Now we just setup a lookup using three possible genre values that I typed in.
In reality it would be more efficient and more straightforward to have a separate table
with all of the available genre values in it.
And bear in mind that the genre values may change over time as well.
If we have a separate table, we can add new genre, remove ones that are no longer used,
and so on.
So it’s a pretty straightforward case as before with one interesting question.
So I go to Create.
I go to Create table.
I can say create a new blank table or I can say create a new table design.
Let’s go straight to table design this time and this time instead of getting the default
ID, I’m going to say well maybe I don’t actually need an ID; maybe I don’t need
to number these.
Maybe the genre values themselves could be the primary keys.
I said earlier on that you’ll almost always use an ID but it’s only almost used.
There are occasions when you don’t really need one.
So I’m going to actually put in here Genre as the name of the field, the data type is
going to be short text, and that’s all I’m going to have in that particular table.
This will be the primary key.
So click on that, click on there, it becomes the primary key.
As such it must be unique.
Now I’m going to put in the genre values that I want.
I know that when I switch to Datasheet View it will ask me to save what I’ve done.
So click on Datasheet View.
You must save the table.
Click on Yes.
Now I’m tempted to call the table Genre, but I’ve called the field Genre as well
and what I’m going to put for the table name is going to look a little bit strange.
Not everybody does this.
I do but a lot of people that do quite a bit of database design do this.
They prefix the names of the objects in their databases with a system of codes that remind
you what something is.
So the genre table I would call T-B-L-Genre, tblGenre.
That reminds me whenever I see it that it is a table.
In fact, I would normally use that same approach for those other two tables as well.
Okay, so let me start putting in the genre values.
First one is Action, the next one is Adult, the next one; I’ll carry on typing these.
Join me again in just a moment.
So that’s my Genre table setup.
I’m now going to close that table.
I’m going to close the movie table.
Save changes of course.
And then I’m going to right click on Movie and click on Rename and that is going to begin
with T-B-L as well, and then I’m going to right click on Actor and do the same to that.
From now on I’m going to stick to this naming convention.
When we come to things like forms and reports they’ll have similar prefixes to help me
to identify things later on.
It’s a very good thing to bear in mind if you are going to try and follow this naming
convention that you don’t want to wait until much later on to set these names to these
prefixed values.
Use those names from the outset.
So that’s it for this section.
I’ll see you in the next one.