Indexing, Primary Keys and Adding Records

Locked lesson.

Upgrade

About this lesson

Understand indexing and the concept of primary key fields to identify records. We will then start to build our database by adding our first records to the Trip table. 

Quick reference

Indexing, Primary Keys, and Adding Records

In this lesson, we will learn about indexing and primary keys and discover why they are so important when building an efficient Access database. We will also add our first records to the table. 

When to use

It is essential to your understanding of Access databases to know how indexing and primary keys work. This knowledge will be required whenever you are working with tables in Access. 

Instructions

Indexing

What is Indexing and Why is it Useful?

As our content grows, it will take longer to search for the information we need as Access has to churn through large amounts of records. We can improve the efficiency of our database by using indexing. When a field is indexed, Access can essentially jump straight to it without having to search through thousands or even millions of records. 

How do we determine which fields should be indexed? We need to think about the type of information we might search for in our database. We may search for a ticket by the customer name. We may search for a ticket based on its priority. We may search by the date the ticket was logged. These fields should be indexed. In general, we would not search for a ticket based on its long description so that would not be an indexed field. 

Primary Keys

Notice in the Ticket table, the ID field has a key icon next to it. This indicates it is the Primary Key field for this table. The Primary Key is a unique way of identifying records in a table.

Our Primary Key is set to Autonumber so when a new record is added to the table, it will be assigned a unique number in the database.

The Primary Key field is how we identify records in a table. 

Adding another field

Let's add another field to the table for the Customer Name. 

  • From the Home tab, click Views and select Datasheet View. 
  • Click on Click to Add in the last column. 
  • Select the data type Short Text from the list. 
  • Change the column heading to 'CustomerName'. 
Define the Field Properties
  • From the Home tab, click Views and switch to Design View. 

New fields can be added in Design view or Datasheet view. 

  • Add the Description text 'Customer name'. 
  • Change the Required property to Yes. 
  • Change the Allow Zero Length property to No. 
  • Change the Indexed field to Yes. 

Adding records to the table

So far, we've focused on adding fields to our table but as yet, we have not added any records. Records need to be added in Datasheet view. 

  • From the Home tab, click Views and switch to Datasheet View.
  • If prompted, select Yes to save the table. 
  • In the TicketNumber field, type '123456'. 
  • Press Tab to move to the next field. 
  • In the CustomerName field, type 'Claire Smith'. 
  • Adjust the column widths if required.

Hints & tips

  • If a record that is using the Primary Key field is deleted at some point, its unique number will not be reused for another record. This ensures that the records always have a unique primary key. 
  • The fields you choose to index is not a hard and fast rule. There may be specific business or tech considerations at your workplace that will determine which fields you choose to index. In general, long text fields are not indexed. 
Login to download
  • 00:04 In the previous lesson we created a table and we added a brand new field.
  • 00:09 And what I want to talk about in this lesson is indexing and primary keys.
  • 00:14 So, let's start out with primary keys.
  • 00:17 If we take a look at the two fields that we currently have in our table,
  • 00:21 which one do you think is the primary key?
  • 00:24 Well, if you guess the ID field,
  • 00:26 because we have a little key symbol next to it then you would be correct.
  • 00:31 And that is how we can tell which fields in our table is being used
  • 00:34 as the primary key.
  • 00:36 So, what exactly does that mean?
  • 00:38 So in general, the primary key field is the field that we would use to
  • 00:43 uniquely identify a record in a database.
  • 00:46 And in this table, because the datatype is AutoNumber, it means that for every
  • 00:52 new record that's added a brand new unique number will be assigned to that record.
  • 00:58 And it's just a way for Access to identify those records.
  • 01:01 Now it's a bit easier to visualize this if we switch into datasheet view.
  • 01:06 So, currently we're working in design view is in general where we come to
  • 01:11 add fields to a table, but if we right-click on the tab,
  • 01:15 we have another option here which is Datasheet View.
  • 01:19 It's worth noting that we can also make minor design changes in Datasheet View
  • 01:24 as well.
  • 01:25 And we can also add columns, which we are going to do in a moment.
  • 01:29 But take a look at what we have so far.
  • 01:31 You can see that I now have a new column called TicketNumber.
  • 01:36 So, every time you add a field in design view,
  • 01:39 that is represented by a new column in datasheet view.
  • 01:43 And because the data type for this column is a number currently, it just says zero.
  • 01:49 So, we are going to add a third column to this table, and
  • 01:52 I'm going to do it this time from datasheet view.
  • 01:55 Because what you'll notice is at the end here, we can click to add a column.
  • 02:00 So, let's click, we can then choose the data type for this column.
  • 02:05 Now I want the customer's name to populate in this field, a name is text.
  • 02:10 So, I'm going to choose short text.
  • 02:13 I can now give my column a name.
  • 02:15 So, I'm going to call this CustomerName.
  • 02:18 Again, I'm not going to use any spaces.
  • 02:20 Now remember with these columns, you can drag them out if they need a little bit
  • 02:25 more breathing room, just so that you can see all of those labels.
  • 02:29 Now for each field that we add, we need to check the properties of that field.
  • 02:34 So, for this we need to switch back into design view.
  • 02:38 And I'm going to add a description over here as well.
  • 02:41 So, we're just going to say Customer name.
  • 02:46 Now if we select this record and take a look down below at our field properties,
  • 02:52 the first field we have here is Field Size, and
  • 02:55 by default that is set to 255 characters.
  • 02:59 Now I'm going to guess that it's probably very unlikely that anybody has a name that
  • 03:04 is longer than 255 characters.
  • 03:07 So, I might want to change this field size just to limit the width or
  • 03:11 the amount of characters that people can add to something like, let's go for
  • 03:15 40, most people don't have a name longer than 40 characters.
  • 03:19 A couple of other properties I'm going to change here,
  • 03:22 we're going to go down to Required.
  • 03:24 Is this a required field?
  • 03:26 Do we need the customer name in the record?
  • 03:28 Yes, we do.
  • 03:30 Now it's worth noting when changing these options you can use the drop down all
  • 03:34 the way over on the right-hand side.
  • 03:36 If I'm just toggling this from no to yes, I can double-click,
  • 03:39 which is sometimes much easier.
  • 03:41 Now do I want to allow zero length?
  • 03:44 That means, can I have a customer name that doesn't contain any characters?
  • 03:49 Well, no, I can't.
  • 03:50 So, I don't want to allow zero length names.
  • 03:55 And then finally we get on to this Index option.
  • 03:58 Do I want to index this field?
  • 04:00 So, let's pause for a moment and
  • 04:02 just talk a little bit about what exactly indexing is.
  • 04:06 Now at the moment we have a very tiny database, but it might be over time your
  • 04:11 database grows and grows you add more information, more tables, more forms,
  • 04:16 more reports, so that the database ends up being extremely large.
  • 04:20 And once you're dealing with very large datasets they can become a bit sluggish,
  • 04:25 a bit slow particularly when you're searching for different records.
  • 04:29 So, in order to effectively optimize our database, we can use indexing.
  • 04:35 So, if a field is indexed, so for example the customer name Name field, if somebody
  • 04:40 is searching for Sally Jones, because I've applied indexing to the field.
  • 04:45 Access is able to go directly to the record it needs as opposed to churning
  • 04:49 through hundreds, or even thousands, or millions of different rows of data.
  • 04:55 So, makes your database more efficient.
  • 04:57 Now I will say we don't need to apply indexing to all fields, but for
  • 05:01 something like the customer name I'm going to say Yes, let's index, and
  • 05:06 it's okay if it finds duplicate, so in this case duplicate names.
  • 05:10 So, now we have that third field set up,
  • 05:13 let's switch back into datasheet view and see how our table is looking.
  • 05:17 Now notice here it will always prompt you to save your table, so
  • 05:21 let's say Yes, there we have the third column.
  • 05:24 So, now that I have a couple of columns I'm going to start adding in some
  • 05:28 information for this first record.
  • 05:31 And datasheet view in general is where you start adding these things in.
  • 05:35 So, for my first record, the TicketNumber is going to be 12345.
  • 05:40 And I can press the Tab key to move to the next record.
  • 05:44 What is my CustomerName?
  • 05:45 Well, let's refer back to our Notepad file.
  • 05:49 I can see that I need to add a 6 to this ticket number name.
  • 05:52 The customer name is Claire Smith.
  • 05:55 So, if I go back to ticket number, if I just click in the cell,
  • 05:59 it's very simple to edit just add a 6 and the customer name is Claire Smith,
  • 06:04 there is our first record.
  • 06:06 Now we are going to add more columns information, but I really wanted to show
  • 06:10 you how this autonumbering for that primary key field works.
  • 06:14 I've added a new record, and
  • 06:16 it's automatically been assigned a unique ID of 1.
  • 06:20 So, this is going to be unique to this specific record.
  • 06:25 When I add a new record, it's going to give it an ID of 2, so on and so forth.
  • 06:31 So, that is a very quick explanation of how primary keys and
  • 06:36 indexing works in Access.

Lesson notes are only available for subscribers.

Gift this course