Welcome back to our course on Access 2016.
In the preceding section we got to work on creating the trip table for the Esprit de
Tour travel company.
And we’ve only got a couple of fields so far.
We’re going to add a few more fields in this section.
But I’ve got a few other things to point out about this table to you first.
And the first of them relates to indexes and keys.
As the content of a database grows and as the volume of data in the database grows,
as people are using the database it can potentially take longer and longer to find what you need
in the database.
Now one of the ways that you can improve the performance say searching a database is by
indexing some of the fields.
Now the last field that we added to this table was a code field.
This is the three character code identifying a trip.
And if somebody wants to find out information about that trip and the code is not indexed
what Access has to do is to read through all of the trips in the database, all of the trips
in the trip table, to find the one with that code.
So it looks at each trip and says, “Has this one got that code?
No.
Has this one got that code?” and so on.
However, if you index the code then what Access does is to maintain an index.
It’s a little bit like say a telephone directory.
And when you say to Access, “I want information about the trip with this code” it goes straight
to the trip with that code.
So it makes it much quicker for Access to find the information that you want.
However, there is a cost to this and the cost is that in order to maintain these indexes
as a user maintains, modifies data in, for example the trip table, the process of keeping
all of these indexes up to date tends to slow down the maintenance process.
Not the searching process but the process of maintaining the data.
Now if you look at the bottom right hand corner of the screen at the moment you can see a
regular little statement there about indexes.
An index speeds up searches and sorting on a field but may slow updates.
Selecting Yes/No duplicates prohibits duplicate values in the field.
Now in order to make a decision about whether a field needs to be indexed or not, there
are certain technical considerations but there are also very important business considerations.
And if you take something like Code here, the code, the three character code for trip
is a very important identifier for a trip.
And therefore this is the sort of field that you would index on.
But more about indexing as we go along.
Now the other important thing here that I didn’t point out is that if I go back to
that first field, the ID field you’ll see a little key symbol to the left of the word
ID.
And when I clicked there you may have noticed that on the Ribbon on the Design tab the Primary
Key control became highlighted.
And that’s because the ID is the primary key for this particular table.
That means that whatever the ID of a trip is is the main way that we identify it, the
main unique way that we identify a record in this table.
Now this goes back to the unique identification property that I talked about in the preceding
section.
Every table will normally have a primary key.
Often we will use ID such as the AutoNumber ID for the trip table.
Sometimes we will use something else, as you will see.
Let me also talk a little bit more about these views.
We’re currently in Design View.
And in Design View note that we have a contextual tab, a Design tab.
And it’s part of the Table Tools contextual tabs.
Now some of the controls on the Design tab we’ll be looking at later on.
Note that one of them is Property Sheet that we closed earlier on.
We still don’t need the property sheet, but that’s how you can open it again.
Note also to the left of Property Sheet that there are options there to insert rows.
This is insert rows in the design of this table.
And also to delete rows, which basically means to delete fields from the table.
I showed you earlier how to switch to Datasheet View.
Apart from using the buttons at the right-hand end of the Status Bar you do have a View button
on the left.
And if I click on Datasheet View, as expected I’m asked, You must first save the table.
I have made changes to this table design since I last saved it.
So I’m going to say Yes to saving the table and I’m now in Datasheet View.
In Datasheet View I get the Table Tools contextual tabs.
There is a Fields tab and a Table tab.
And really the main reason that I get different tabs for those different ways of working on
a table is that in essence Datasheet View is a view in which we enter and modify data
in a table.
And Design View is how we work on the design of the table.
Now strictly speaking it’s not a very clear distinction because in Datasheet View you
can do some simple design.
So for instance given that I’ve currently got two fields in the trip table, that’s
the ID field and the Code field.
In Datasheet View the fields correspond to the columns and the rows will be the individual
records in the trip table.
If I want to add a third field here and the third field I’m going to add is the name
of the trip.
All I need to do is click in here.
If I right click it lets me choose a data type.
I’m going to say Short Text.
And then I’m going to rename that to TripName.
I can’t actually only call it Name because Name is what’s called a reserved word in
Access.
Name has another meaning.
So if I call it TripName I’ve now defined another field.
Now I’m quite limited in what I can do in that definition.
So to go into more detail about TripName I really need to go back into Design View.
But it can be a simple way of if you like roughly setting up the fields at the start
of designing a table.
Let’s switch back into Design View now.
There’s my new field.
Trip Field.
It’s a Short Text field.
Let’s click on that row in Design View.
It currently allows 255 characters.
Well that should be plenty for a name for a trip.
Do we require a name?
Yes we do.
Do we allow a zero length name?
No we don’t.
Is name indexed?
No.
Text fields, fields with a lot of text in them are not often indexed.
Normally if you wanted to find a trip with a particular name you would expect Access
to have to actually go through the trips and find that one.
Whereas if you had the three character code you’d expect it to go straight to it.
You may also, for example, want to search for part of the name of a trip.
But again more on that later.
So they’re the basic settings for TripName.
What I’m going to do now is put in a description.
And that’s my third field set up.
Now let me switch back into Datasheet View.
As usual save the changes and now we have three fields in the trip table.
So I’m sure you can see by now the pattern of setting up the fields in this table.
We’ve still got a few more to do.
But one thing we haven’t done yet is to add any data to the table.
And I’m going to add a sort of dummy trip to the table now to demonstrate a couple of
other things that you need to be aware of.
In terms of loading a reasonable amount of data into the table, I’m going to cover
that in two or three sections from now, and also noting that we are in Datasheet View
I’m going to spend some time talking about Datasheet View in more detail also in two
or three sections from now.
But for the moment let me give you a very brief explanation of what you can see in Datasheet
View.
Each row is a record in the table.
So in our case that would be a trip.
And all the time we haven’t got any trip information there at all we have one row with
a star at the left-hand end.
And note in ID the word New in brackets.
That indicates that if I start typing in this particular row I’ll be creating a new trip.
Let’s make up a trip.
Let’s put in a code.
Let’s call the code for this trip LEX.
So that’s my three character code.
Now let me type in my trip name.
It’s going to be Louisiana Explorer.
Now as I start typing into this field if I find that there isn’t enough room to see
what I’m typing I can make the column wider by just hovering over the header in the normal
way, move out to the right until I get the double headed arrow and I can drag that right-hand
column edge over to the right to give myself a bit more space to type in.
And I have my first record in the database.
Now note the ID is three.
That is a number that is automatically assigned by Access.
It’s AutoNumber field so I don’t have to do anything about that.
If I deleted that record and started another new one it would be ID4.
So the numbers don’t get reused.
And you’ll notice that to the left-hand end of that row with Louisiana Explorer in
it there’s a little pen icon.
That indicates that this is the record that I’m currently working on.
And if I click away, say I click down onto the New row again which is ready for the next
row in my table, the pen disappears and I have one record stored in my table.
Now of course that one record doesn’t have all of the fields of information that ultimately
trips need but it’s a start.
So not only have we now got three fields defined in our table but we’ve added the first row
of data.
In the next section we’re going to add some more fields and they’re going to be fields
of different types again.
So please join me for that.