Focus video player for keyboard shortcuts
Auto
  • HD
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Microsoft Access for Beginners course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

About this lesson

In this lesson, we will learn about tables in Access, and then setup our first table and add fields. 

Quick reference

Creating a Table and Adding Fields

Tables are the backbone of Access and understanding how they work is extremely important.

A table is where we store all our tabular information in columns and rows. We can have multiple tables in one database and create relationships between tables.

The column headings in a table are referred to as fields in Access. We can add new fields, delete fields and modify existing fields. 

When to use

We use tables whenever we are creating a database. Tables are where all our information is stored. 

Creating a Table

The first table we are going to create is a table that holds all of the information related to tickets that the IT Helpdesk 'TechGurus' logs. We have the ticket information for the first ticket stored in a text file. Let's take a look at it. 

  • Download the file 'Ticket_Example.txt'. 

The text file shows information related to the customer 'Claire Smith'. 

  • Review the ticket information and the fields. 
  • Some fields such as priority, have multiple options. 

We will use these fields as the column headings in our table. 

  • From the Create tab, click on Table. 

The new table is given a default name of 'Table1'. Let's rename the table. 

  • Right-click on the table name. 
  • Select Rename from the list. 

If we choose to do anything else at this stage, such as switch to Design view, we will be asked to give the table a name. 

  • Name the table 'tbTicket'. 
  • Click OK. 

It's recommended that you use a naming convention to name different elements in the database to make them easier to identify. For example, if it's a table use the prefix 'tbl'. If it's a form, use the prefix 'frm', etc. Or you can use your own naming conventions. 

It is also recommended that you do not include any spaces in the table name to prevent issues further down the line. 

  • From the Home tab, click Views and select Design View. 

Working with table fields

Currently, the only field in the table is 'ID' which has a data type of 'Autonumber'. Data types are determined by the type of data contained in the column. 

  • Click in the Description field and type 'Unique code for the ticket'. 

The ID field in this table is a very important field. This field will automatically generate a number that is unique to this trip every time we add a record. The ID field purely exists in the database and isn't visible to users. 

Field Properties
  • Click on the ID field. 

The Field Properties pane is displayed underneath. This pane shows the different settings for the field and can be customized. 

Adding additional fields

Let's add another field for the Ticket number. 

  • Click in the box underneath ID in the Field Name column. 
  • Type 'TicketNumber'. 
  • In the Data Type column, click the drop-down and select Number. 
  • Add the description 'Ticket number'. 
Modifying Field Properties
  • Click on the TicketNumber field.
  • Modify the field properties:

Required

This property defines if this field is required in order to save the record. In our example, every ticket must have a ticket number. 

  • Click in Required.
  • Click the drop-down arrow or double-click to change the property to Yes. 

Allow Zero Length

This property defines if we are going to allow ticket numbers of zero length. 

  • Click in Allow Zero Length
  • Change this to No. 

Indexed

This property defines if the field is going to be indexed. Indexing improves the efficiency of the database when searching through large numbers of records. 

  • Click in Indexed. 
  • Change this to Yes (Duplicates OK).
Login to download
  • 00:04 So now we've got a handle on the basics,
  • 00:06 it's time to move into the core content of this course.
  • 00:09 And we're going to start out talking about tables,
  • 00:13 because tables are the backbone of Access.
  • 00:17 All of our information is stored in one or numerous different tables.
  • 00:22 And the data is organized into columns and rows, or
  • 00:26 records as they're referred to in Access.
  • 00:29 So we basically create all of the tables that we need.
  • 00:33 And then what we can do is link those tables together and start creating things
  • 00:38 like queries, reports, and forms to interrogate the data.
  • 00:42 Now once again, we're not going to get too far ahead of ourselves,
  • 00:45 let's start out by taking a look at a basic table.
  • 00:48 Now notice on the screen here that I've reopened the blank database file that we
  • 00:52 created called TechSalesTicketing.
  • 00:54 I wanted to call this tech gurus ticketing.
  • 00:58 So not to worry, let's go into File, down to Options, and into Current Database.
  • 01:04 Remember, this is where we added our short title.
  • 01:08 So I'm just going to remove the word at sales and replace it with the word gurus.
  • 01:13 There we go, that looks a little bit better.
  • 01:17 So just to set the scene here, we're going to create a database for
  • 01:22 an IT support team.
  • 01:23 And our IT support team is called Tech Gurus.
  • 01:27 And basically what they do is they support their customers from all around the world
  • 01:31 with any issues that they're having with their software or hardware.
  • 01:35 So what we're basically going to create is a ticketing system database.
  • 01:39 So a customer might call up, maybe their printer is broken,
  • 01:43 the technician will log a ticket and then assign that to a different team.
  • 01:48 And this database is going to hold all of the information for those tickets.
  • 01:53 Now before you start to build your database, and
  • 01:55 specifically when you start to build tables, you need to really think about
  • 01:59 the information that you want those tables to hold.
  • 02:02 So this is a sample record that I'm going to be adding to my database.
  • 02:07 And you can find this little notepad file in the Course Files folder available to
  • 02:12 download.
  • 02:12 And this basically shows me the information that I want to include in my
  • 02:17 first table.
  • 02:18 So I can see here that for every ticket, we have a ticket number,
  • 02:22 we have a customer name, the company they work for,
  • 02:25 the date that that ticket was logged, the charge, the ticket type.
  • 02:29 The technician that this ticket is assigned to.
  • 02:32 The priority of this ticket, the status, the SLA, so the time,
  • 02:37 which this has to be fixed by, and then we have a brief description.
  • 02:41 So this unfortunate person, they have a printer that is constantly jamming,
  • 02:46 and we're going to base our first table off of this data.
  • 02:50 So to create a new table, we go up to the Create tab and
  • 02:54 we have a tables group just here.
  • 02:56 We have two different options, table and table design, and
  • 02:59 these are very slightly different.
  • 03:01 So let's use a table, first of all.
  • 03:05 Notice that in the Navigation pane underneath the Tables group it now says
  • 03:10 Table1.
  • 03:10 Access will always name your table for you with a rather generic name.
  • 03:15 Now we're going to rename this in a couple of seconds.
  • 03:18 And notice what we have in the main window, not a great deal at the moment.
  • 03:22 One thing that we do have is this ID column header.
  • 03:27 And that's been put there automatically by Access.
  • 03:31 I'll explain a bit more as to exactly what this ID field is in a moment.
  • 03:36 And that's just another thing worth noting,
  • 03:39 when I refer to columns in Access I talk about fields.
  • 03:43 Now we're going to add some more columns or more fields into this table.
  • 03:47 And to do that, we need to switch to a different view.
  • 03:49 So I'm going to right click and notice that I have Datasheet View
  • 03:54 currently selected, but I want to go into design view.
  • 03:57 Now as soon as I tried to change to a different view,
  • 04:00 it's going to ask me to save my table.
  • 04:02 Now when I'm saving things in Access,
  • 04:04 I like to stick with a standard naming convention.
  • 04:07 And naming convention that automatically helps me identify what this table or
  • 04:13 this form is.
  • 04:14 So what I tend to do is if I'm creating a table,
  • 04:17 I will always prefix the name with TBL.
  • 04:20 And this is going to be a table related to the tickets.
  • 04:23 So I'm going to call it Table ticket and click on OK.
  • 04:28 Notice that it's been renamed in that navigation pane as well.
  • 04:32 So now it's switched me into design view and
  • 04:35 this is where I can come to add additional columns to my table.
  • 04:40 Now let's talk a little bit more about this ID field.
  • 04:43 Notice that it also has a little key icon next to it and
  • 04:47 this means that this is the primary key field.
  • 04:50 And we're going to be talking a lot more about primary keys in the next lesson.
  • 04:54 But basically this ID field has a data type of auto number.
  • 04:59 And what this is is a way of uniquely identifying each record in a table.
  • 05:05 Each time we add a new record or a new row, this field is going to generate
  • 05:11 a unique value, which allows Access to uniquely identify that row in the table.
  • 05:17 So let's click in the description field.
  • 05:19 And I'm just going to say that this is the unique ID for this ticket.
  • 05:27 Let's click in the row underneath and add in our next field.
  • 05:31 So if we refer back to our notepad file,
  • 05:33 what's the first piece of information that I want in here?
  • 05:36 Well, I want the next column to contain the ticket number.
  • 05:40 Let's click underneath, I'm going to say ticket number.
  • 05:44 Now notice when I type in these field names,
  • 05:47 I don't have any spaces in the name.
  • 05:49 And that is a very standard Access database way of working.
  • 05:54 Sometimes you'll find if you have spaces in between words, it can cause problems.
  • 05:59 So I just prefer to either separate words with an underscore or
  • 06:03 just not have any space in there at all.
  • 06:05 Now the data type basically defines the type of data that's going to go into
  • 06:09 this field.
  • 06:10 So the ticket number is effectively a number, so
  • 06:14 I can click the drop down and say number.
  • 06:17 And now I need to give this column a description.
  • 06:21 Now for each new field that you add, notice if I select the ticket number
  • 06:26 field, at the bottom we have some field properties.
  • 06:29 And this is where we can effectively adjust the settings for
  • 06:33 this particular field.
  • 06:34 Now I'm not going to go through all of these, but
  • 06:36 a couple that are quite important.
  • 06:38 Notice towards the bottom it says required and indexed.
  • 06:42 Now currently both of these settings are set to no.
  • 06:46 Now, required is basically saying,
  • 06:49 is this field required in order to save this table?
  • 06:53 So do I require agents to enter in a ticket number in order to be
  • 06:58 able to save the ticket?
  • 07:00 Well, yes I do, every ticket must have a ticket number.
  • 07:04 And then when it comes to this indexed option again,
  • 07:07 we're going to talk a lot more about this in another lesson.
  • 07:10 I'm going to double click to change this to yes duplicates, okay?
  • 07:15 Now if you're not sure what I'm doing here with regards to this indexing option,
  • 07:19 don't worry because that's exactly what we're going to cover in the next lesson.

Lesson notes are only available for subscribers.

Gift this course