Access 2016 for Beginners Part 11: Adding Fields and Using Indexed Fields in MS Access

Get a FREE Microsoft Access 2019 Introductory Course here https://theskillstream.com/access-2019-intro-course
Get the complete 9-hour Access 2016 training course here https://www.simonsezit.com/courses/microsoft/learn-microsoft-access-2016-training-tutorials/
Get the complete 8-hour Access 2016 advanced course here https://www.simonsezit.com/courses/microsoft/learn-microsoft-access-2016-advanced-training-tutorials/
This Microsoft Access 2016 training tutorial video is the second part of the Tables chapter of this course. Here we will continue adding more fields in our tour database such as trip name. We will also talk about the indexed field.
Check out some of our other training on YouTube:
Access 2016 training tutorials: PLAYLIST LINK HERE
PowerPoint 2016 training tutorials:https://www.youtube.com/playlist?listPLzj7TwUeMQ3jj_QkuckJNn8RddhwlQKOM
VBA for Excel tutorials: https://www.youtube.com/playlist?listPLzj7TwUeMQ3hWRi0mgxdyWkT0QaYKuBGZ
Excel 2016 training for beginners: https://www.youtube.com/playlist?listPLzj7TwUeMQ3jUeMoLReqNzzuKj7rdLhZ2
Project 2016 training: https://www.youtube.com/playlist?listPLzj7TwUeMQ3gPqakrFQ8fSNPu00rsOuzu
Stay in touch:
SimonSezIT.com: https://www.SimonSezIT.com/
StreamSkill.com: https://StreamSkill.com/
YouTube Channel: https://www.youtube.com/user/simonsezittraining
Facebook: https://www.facebook.com/SimonSezIT/
If you enjoyed the video, please give a "thumbs up" and subscribe to the channel ;-)
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.
Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.