Add tables

Build tables and set data types

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Because they store your data, tables are the backbone of your database. Each table contains information about a specific subject. For example, a supplier table might store supplier names, e-mail addresses, and telephone numbers.

Note: Before you build tables, it’s helpful to understand Access database objects.

When you open a new, blank database, Access automatically creates an empty table. To customize that table, start defining your fields and add data.

Rename a table in a desktop database

Table1 is the default name of the first table in a new desktop database. It’s a good idea to name the table something more meaningful.

  1. On the Quick Access Toolbar, select Save Save

  2. In the Table name box, enter a descriptive name.

Add a table to a desktop database

Add more tables to a database, if you need them, even if you started with a template.

  1. On the Create tab, select Table.
    Access adds a new table with the name Table<#>, where <#> is the next sequential, unused number.

  2. Rename the table using the procedure in Rename a table in a desktop database, earlier in this module.

Save a table

Before you close your database, to avoid losing the work you’ve done and the data you’ve entered, be sure to save your table. When you try to close the database, if you haven’t saved your work on a table, Access prompts you to save it. Or, at any time, select Save .

Add a field by entering data

  1. In Datasheet view, enter data in the Click to Add column of the datasheet.
    Access creates a new field.

    Screen snippet of ID in Supplier table

  2. In the column heading, type a new name for the field.

    Screen snippet of Supplier table showing two rows with ID

Change the data type of a field

When you add a field by typing data into it, Access sets the field’s data type based on its contents. View the data type on the Fields tab, under Data Type.

Screen snippet showing data type field

To change the data type:

  1. Select the field.

  2. On the Fields tab, open the Data Type list and select a data type.

Add a field for a specific data type

Access validates data as it’s entered to ensure that it matches the field’s data type. If you need a specific data format in a field, specify its data type when you create it.

  1. With the table open in Datasheet view, select Click to Add and then select a data type

    screen snippet of Click to add data type drop down

  2. Type a descriptive name for the field, such as Last Name.

    Screen snippet of field to add a descriptive name for a column

Because you specified a data type, Access validates the data you enter in the new field. For example, text isn’t accepted in a Date field. Specifying the data type also helps to minimize your database size.

Want more?

Data types for Access apps

Data types for Access desktop databases

Excel training

Outlook training

Tables are usually the first objects you add to a database, because simply put, they store your data. As we create this table, we’ll identify its Primary Key. We’ll also learn about the various data types available and see how we can set each field’s data type.

I’m going to put our design up here for reference. When we double-click the ID field here, we see that it becomes editable. Let’s rename the field Customer ID. This way we always know where the values in the field come from—the Customers table. All your tables must have a Primary Key, and Customer ID is the Primary Key for this table.

When we press Enter HERE, Access selects the next blank field and it displays this menu. Most of these items are DATA TYPES. Every field in your database must be assigned a data type.

Data types make storage more efficient because they control the size of your fields. For example, if you only need to capture names, you don't have to create a huge storage space for those names. That might not seem important in this simple example, but it is important when you have a lot of data.

Data types also help you prevent mistakes. For example, you can’t enter a name in a field set to contain dates and times.

Let's take a quick look at the data types you can use. Short Text is a text field which holds up to 256 characters. There’s Number, Currency, Date and Time, and Yes/No.

A Yes/No field can contain Yes or No, True or False, or one and zero.

Lookup and Relationship isn't actually a data type. But you can use this to create a list of choices in that field.

Rich text and long text both let you store a gigabyte of text. Desktop databases use rich text and Access web apps use long text. The main difference between them is you can apply fonts and other formatting to rich text, such as bold and italic.

Use the Attachment data type to attach pictures and other files to a record, just like you can in email. Use the Hyperlink data type to store web and email addresses. And use the Calculated Field data type to do things like combine first and last names or calculate a discount.

There’s another data type in this table: AutoNumber. This data type assigns a sequential number whenever you add a new record. You can’t change or delete a value in one of these fields. Many primary keys use this data type because as a rule, you never want to change Primary Key values.

When you build a table in an Access web app, you use the Image data type instead of the Attachment data type to store and display images.

For this field, let’s select Short Text because it’s going store names, and 256 characters is plenty of space. Notice that after we select the data type, the heading becomes editable. Let’s enter first name and press the Tab key to select the next blank field. We select Short Text again, ... and then type last name. Having the first and last names as two separate fields reflects the database design principle that each field should contain the smallest possible unit of data.

Press Control S to save the table. Let’s name it Customers.

Now we have a primary key and short text fields for first names and last names. Each field holds the smallest possible unit of data and has a data type. We now have what we need for this table.

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.