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.
Download this lesson’s related exercise files.3.02 indexing-primary-keys-adding-records - Exercise.docx
43.5 KB 3.02 indexing-primary-keys-adding-records- Exercise Solution.docx
65.3 KB TechGurusTicketing03.accdb
532 KB TechGurusTicketing03 - Complete.accdb
544 KB Trip_Example.txt
361 B Ticket_Example.txt
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.
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.
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.
Lesson notes are only available for subscribers.