Create a table and add fields

Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

Once you design an Access database, you store your data in tables—subject-based lists that embody rows and columns. For example, you can make a Contacts table to store a list of names, addresses, and telephone numbers, or a Products table to store information about products. This article details how to create a table, add fields to a table, set a table’s primary key, and how to set field and table properties.

Before you create tables and add fields, make sure you understand the background concepts. For more information, see Introduction to tables.

In this article

Creating a table
— Create in a new database
— Create in an existing database
— Importing or linking
— — Use external data
— — Use a SharePoint site
— — Use a Web service
— Set a table’s properties
— Save a table
Setting a primary key
— Determine fields to use
— Set or change a primary key
— Remove a primary key
Move a field
Adding fields
— Add by entering data
— Add by using a field template
— Setting field properties
— Set in Datasheet view
— — Rename a field
— — Change a data type
— — Change a format
— — Set other properties
— Set properties in Design view
— — Change a data type
— — Set other properties

Creating a table

A simple database, such as a contact list, might use only a single table. Many databases, however, use several tables. When you create a new database, you create a new file on your computer that acts as a container for all of the objects in your database, including your tables.

You can create a table by creating a new database, by inserting a table into an existing database, or by importing or linking to a table from another data source — such as a Microsoft Excel workbook, a Microsoft Word document, a text file, or another database. When you create a new, blank database, a new, empty table is automatically inserted for you. You can then enter data in the table to start defining your fields.

Top of Page

Create a new table in a new database

  1. Press File > New, and then click Blank desktop database.

  1. In the File Name box, enter a file name for the new database.

  1. To browse to a different location and save the database, select the folder icon.

  1. Tap Create. The new database launches, and a new table named Table1 is formed and appears in Datasheet view.

Top of Page

Create a new table in an existing database

  1. Press File Open, and select the database if it is listed under Recent. If not, pick one of the browse options to locate the database.

  1. In the Open dialogue box, click the database that you want to open, and then press Open.

  1. On the Create tab, in the Tables group, choose Table. A new table is added in the database and the table opens in Datasheet view.

Top of Page

Importing or linking to create a table

You can produce a table by importing or linking to data that is stored elsewhere. You can import or link to data in an Excel worksheet, a SharePoint list, an XML file, another Access database, a Microsoft Outlook folder, and more.

After you import data, you make a copy of the data in a new table in the existing database. Further changes to the source data will leave no trace on the imported data, and changes to the imported data avoid altering the source data. After you connect to a data source and import its data, you can then apply the imported data without any source connection. You can edit the design of an imported table.

Once you link to data, you make a linked table in the current database that symbolises a live link to the existing information that is stored elsewhere. After you change data in a linked table, you are modifying it in the source. Anytime data changes in the source, that update is reflected in the linked table. You must be able to connect to the data source whenever you employ a linked table. You cannot change the design of a linked table.

Note: You cannot edit data in an Excel worksheet by using a linked table. As a workaround, import the source data into an Access database, and then link to the database from Excel.

Top of Page

Create a new table by importing or linking to external data

  1. Pick File > Open.

  1. In the Open dialogue box, choose and access the database in which you wish to create a new table.

  1. On the External Data tab, in the Import & Link group, select one of the available data sources. 
Access Ribbon Image
  1. Follow the instructions in the dialogue boxes that display at every step. Access produces the new table and presents it in the Navigation Pane.

Top of Page

Use a SharePoint site to create a table

You can create a table in your database that imports from or links to a SharePoint list. You can also create a new SharePoint list by using a predefined template. The predefined templates in Access include Contacts, Tasks, Issues, and Events.

  1. Select File > Open.

  1. In the Open dialogue box, choose the database in which you want to create the new table, and then press Open.

  1. On the Create tab, in the Tables group, choose SharePoint Lists.

  1. Do one of the following:

Create a SharePoint list that is based on a template

a. Pick either ContactsTasksIssues, or Events.

b. In the Create New List dialogue box, state the URL for the SharePoint site where you want to create the list.

c. Type a name for the new list and its description in the Specify a name for the new list and Description boxes.

d. To open the linked table after it is made, click the Open the list when finished checkbox (selected by default).

Create a custom list

a. Press Custom.

b. In the Create New List dialogue box, enter the URL for the SharePoint site where you want to create the list.

c. Specify a name for the new list and its description in the Specify a name for the new list and Description boxes.

d. To access the linked table after it is formed, choose the Open the list when finished checkbox (selected by default).

Import the data from an existing list

a. Choose Existing SharePoint List.

b. In the Get External Data dialogue box, write the URL for the SharePoint site that includes the data that you want to import.

c. Select Import the source data into a new table in the current database, and then pick Next.

d. Pick the checkbox beside each SharePoint list that you want to import.

Link to a list

a. Pick Existing SharePoint List.

b. In the Get External Data – SharePoint Site dialogue box, input the URL for the SharePoint site that has the list to which you want to link.

c. Select Link to the data source by creating a linked table, and then press Next.

Click the checkbox beside each SharePoint list to which you want to link.

Top of Page

Use a Web service to create a table

You can design a table in your database that links to data at a Web site that offers a Web service interface. Web service tables are read-only.

  1. On the External Data tab, in the Import & Link group, pick More and then choose Data Services.

  1. If your sought connection is already pre-installed, skip to step 5. Otherwise, proceed with the next step.

  1. Pick Install new connection.

  1. Choose the connection file that you want to use, and then confirm OK.

  1. In the Create Link to Web Service Data dialogue box, increase the connection that you want to use.

  1. Specify the table that you want to link to. Access displays the fields on the right side of the dialogue box.

  1. Optionally, enter a name for the linked table in the Specify link name box. Access will relay this name for the linked table in the Navigation Pane.

  1. Press OK. Access forms the linked table.

Top of Page

Set a table’s properties

In addition to setting properties fields, you can also set properties that apply to an entire table or to entire records.

  1. State the table whose properties you want to set.

  1. On the Home tab, in the Views group, select View, and then pick Design View.

  1. On the Design tab, in the Show/Hide group, choose Property Sheet.

Show/Hide group on the Design tab in Access

The table property sheet is displayed.

  1. On the property sheet, tap the General tab.

  1. Select the box by the left of your desired property to set, and then type a setting for the property. Remember to Press CTRL+S to save your changes.
Use this table propertyTo
Display Views On SharePoint SiteElaborate whether views that are based on the table can be presented on a SharePoint site.

Note: The effects of this setting depend on the setting of the Display All Views On SharePoint Site database property.
Subdatasheet ExpandedExpand all subdatasheets after you open the table.
Subdatasheet HeightDo one of the following:

If you want the subdatasheet window to expand to display all rows, leave this property set at 0″.

If you want to control the height of the subdatasheet, enter the desired height in inches.
OrientationSet the view orientation, based on whether your language is read left-to-right, or right-to-left.
DescriptionOffer a description of the table. This description will appear in tooltips for the table.
Default ViewSet DatasheetPivotTable, or PivotChart as the default view when you open the table. PivotTable and PivotChart options were removed from Access starting in Access 2013.
Validation RuleEnter an expression that must be true whenever you add or change a record.
Validation TextEnter a message that is displayed when a record violates the expression in the Validation Rule property.
FilterDefine criteria to display only matching rows in Datasheet view.
Order BySelect one or more fields to specify the default sort order of rows in Datasheet view.
Subdatasheet NameSpecify whether a subdatasheet should appear in Datasheet view, and if so, which table or query should supply the rows in the subdatasheet.
Link Child FieldsList the fields in the table or query that are used for the subdatasheet that match the Link Master Fields property that is specified for the table.
Link Master FieldsList the fields in the table that match the Link Child Fields property that is specified for the table.
Filter On LoadAutomatically apply the filter criteria in the Filter property (by setting to Yes) when the table is opened in Datasheet view.
Order By On LoadAutomatically apply the sort criteria in the Order By property (by setting to Yes) when the table is opened in Datasheet view.

Tip – To give extra space to enter or edit a setting in the property box, press SHIFT+F2 to present the Zoom box. If you are setting the Validation Rule property to an expression and would prefer assistance in building it, select Builder button adjacent to the ValidationRule property box to display the Expression Builder.

Top of Page

Save a table

It’s best practice to save a table’s design straight after you make or adjust a table. Once you save a table initially, entitle it that summarises the data within. You can employ a maximum of 64 alphanumeric characters, even spaces. For example, you might name a table Customers, Parts Inventory, or Products.

Access offers you abundant free rein concerning labelling your tables; however, there are some boundaries to remain mindful of. A table name can be up to 64 characters long, can contain any mixture of letters, numbers, spaces, and special characters except a period (.), exclamation point (!), square brackets ([]), leading space, leading equal sign (=), or nonprintable character, like a carriage return. The name also cannot have any of the following characters:` / \ : ; * ? ” ‘ < > | # <TAB> { } % ~ &.

Tip: You should decide on a naming convention for the objects in your database, and use it consistently.

  1. Choose File > Save, or press CTRL+S.
  1. If you are saving the table for the first time, enter a name for the table, and then select OK.

Top of Page

Setting a primary key

Unless you have a specific reason not to, you’re recommended to clarify a primary key for a table. Access instantly composes an index for the primary key, which can facilitate enhanced database performance. Access even guarantees that each record owns a unique value in the primary key field. Unique values are pivotal, because otherwise there is no way to credibly dfferentiate a given row from other rows.

After you create a new table in Datasheet view, Access promptly produces a primary key for you and provides it a field name of ID and the AutoNumber data type.

In Design view, you can edit or erase the primary key, or set the primary key for a table without one.

Top of Page

Determine which fields to use as a primary key

Often, you may already have data that you want to use as a primary key. For example, you may have existing ID numbers for your employees. If you design a table to track employee information, you could decide to use the existing employee ID as the primary key for the table. Or, maybe employee ID is only unique alongside department ID, mandating that you use both fields together as the primary key. A good candidate for the primary key has the following characteristics:

  • Every record has a unique value for the field or combination of fields.
  • The field or combination of fields is never empty or null — there is always a value.
  • The values are fixed.

If no suitable data exists to use as a primary key, you can formulate a new field to apply a primary key. After you make a new field to use as a primary key, set the field’s data type to AutoNumber to aid with certainty that it qualifies the three characteristics in the preceding list.

Top of Page

Set or change the primary key

  1. Choose the table whose primary key you want to set or change.

  1. On the Home tab, in the Views group, pick View, and then press Design View.

  1. In the table design grid, confirm the field or fields that you want to use as the primary key. To choose one field, click the row selector for the field that you want. To pick numerous fields, hold down CTRL, and then select the row selector for each field.

  1. On the Design tab, in the Tools group, tap Primary Key.

Tools group on the Design tab

A key indicator appears to the left of the field or fields that you specify as the primary key.

Top of Page

Remove the primary key

  1. Pick the table whose primary key you want to remove.

  1. On the Home tab, in the Views group, pick View, and then select Design View.

  1. Select the row selector for the current primary key. If the primary key includes several fields, hold down CTRL, and then press the row selector for each field.

  1. On the Design tab, in the Tools group, choose Primary Key.

Tools group on the Design tab

The key indicator is removed from the field or fields that you previously specified as the primary key.

When you save a new table without setting a primary key, Access prompts you to create a new field for the primary key. If you click Yes, Access creates an ID field that uses the AutoNumber data type to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses it as the primary key. If you click No, Access does not add a field, and no primary key is set.

Top of Page

Adding fields

To reserve a new piece of data about something for which you already have an Access table, be open to the possibility of inserting a field to the table. For example, suppose you have a table that stores the last name, first name, email address, telephone number, and mailing address of each of your customers. If you want to start monitoring each customer’s preferred means of communication, you add a field to store that data.

You store every aspect of data that you want to track in a field. For example, in a contacts table you create fields for Last Name, First Name, Telephone Number, and Address. In a products table you create fields for Product Name, Product ID, and Price.

Each field contains specific essential characteristics, such as a name that uniquely identifies the field within a table, a data type that defines the nature of the data, the operations that can be performed on the data, and how much storage space to set aside for each value.

Prior to you making fields, aim to divide data into its tiniest, resourceful segments. It is simpler to integrate data later than it is to separate it. For example, instead of a Full Name field, consider creating separate fields for Last Name and First Name. Then, you can easily search or sort by First Name, Last Name, or both. If you plan to report, sort, search, or calculate on an item of data, put that item in a field by itself.

After you create a field, you can even define field properties to control its appearance and behaviour. For example, the Format property defines how the data appears in a datasheet or form that contains that field.

Top of Page

Add a field by entering data

When you create a new table or open an existing table in Datasheet view, you can add a field to the table by entering data in the Add New Field column of the datasheet (1). Access automatically assigns a data type for the field, based on the value that you enter. If no other data type is implied by your input, Access sets the data type to Text but you can change the data type.

Datasheet in Access with Add New Field column

To enter data in the Add New Field column:

  1. Create or open a table in Datasheet view by right-clicking the table that you want in the Navigation Pane and then pressing Datasheet view from the shortcut menu.

  1. In the Add New Field column, specify the name of the field that you want to create.Use a descriptive name so that the field will be easier to identify.

  1. State data in the new field.

Top of Page

Add a field by using a field template

Sometimes it is easier to choose from a predefined list of fields that fit your needs than to manually create a field. You can use the More Fields list to choose from a list of field templates. A field template is a predefined set of characteristics and properties that describes a field. The field template definition includes a field name, a data type, a setting for the field’s Format property, and other field properties.

  1. On the Home tab, in the Views group, choose View, and then select Datasheet View.

  1. On the Fields tab, in the Add & Delete group, pick More Fields.

Screesnhot of Add & Delete group on the Fields ribbbon tab.

  1. Choose a field in the More Fields list to insert the new column. Access places the field to the right of the column where your cursor is currently located. If you choose one of the field options under the Quick Start heading, such as Address, Access creates multiple fields in your table to contain the various parts of an address.

Top of Page

Setting field properties

Following you creating a field, you can configure field properties to manage its appearance and behaviour.

For example, by setting field properties, you can:

  • Control the appearance of data in a field
  • Help prevent incorrect data entry in a field
  • Specify default values for a field
  • Help speed up searching and sorting on a field

You can programme some of the available field properties during your work in Datasheet view. To have access to and set the complete list of field properties; however, you need to use Design view.

The properties that you can set, depend on the field’s data type.

Top of Page

Set field properties in Datasheet view

You can rename a field, change its data type, change its Format property, and change some of a field’s other properties while you work in Datasheet view.

  1. In the Navigation Pane, right-click your desired table to access.
  1. On the shortcut menu, press Datasheet view.

Top of Page

Rename a field

When you add a field by entering data in Datasheet view, Access automatically assigns a generic name to the field. Access assigns the name Field1 to the first new field, Field2 to the second new field, and so on. By default, a field’s name is used as its label wherever the field is displayed, such as a column heading on a datasheet. Renaming fields so that they have more descriptive names helps make them easier to use when you view or edit records.

  1. Right-click the heading of your sought field to rename (for example, Field1).
  1. On the shortcut menu, pick Rename Field.
  1. Enter the new name in the field heading. Field names can extend to a maximum of 64 characters (letters or numbers), including spaces.

Top of Page

Change a field’s data type

When you create a field by entering data in Datasheet view, Access examines that data to determine the appropriate data type for the field. For example, if you enter 1/1/2017, Access recognizes that data as a date and sets the data type for the field to Date/Time. If Access can’t definitively determine the data type, the data type is set to Text by default (Short Text if you’re using Access 2016).

The data type of the field determines which other field properties you can set. For example, you can set only the Append Only property for a field that has the Hyperlink data type or the Memo data type (Long Text if you’re using Access 2016).

There may be cases where you want to manually change a field’s data type. For example, suppose you have room numbers that resemble dates, such as 10/2017. If you enter 10/2017 into a new field in Datasheet view, the automatic data type detection feature selects the Date/Time data type for the field. Because room numbers are labels, and not dates, they should use the Text data type. Use the following procedure to change a field’s data type.

  1. On the Ribbon, choose the Fields tab.

  • In the Data Type list, in the Formatting group, click the data type that you want.
Access Ribbon Image of Data Type and Formatting group

What data types are available?See Data types for Access desktop databases for a complete list of available data types in Access databases.

Tips on data types

  • The maximum size of an Access database file is 2 gigabytes.

  • For phone numbers, part numbers, and other numbers that you plan to exclude from mathematical calculations, you should choose the Text data type instead of the Number data type. A numeric value that is stored as text can be sorted and filtered more logically, but cannot be easily used in calculations.

  • For the Text and Number data types, you can specify the field size or data type more precisely by setting a value in the Field Size property box.

Top of Page

Change a field’s format

In addition to determining the data type of a new field, Access may also set the Format property for the field, depending on what you input. For example, if you enter 10:50 a.m., Access sets the data type to Date/Time and the Format property to Medium Time. To manually change a field’s Format property, do the following:

  1. On the Ribbon, choose the Fields tab. 
  1. In the Format list, in the Formatting group, specify your desired format. 

Note: The Format list may be unavailable for some fields (for example, Text), depending on the data type of the field.

Top of Page

Set other field properties

  1. In Datasheet view, pick the field for which you want to set the property.
  1. On the Fields tab, in the PropertiesFormatting, or Field Validation groups, choose your preferred properties.

Top of Page

Set field properties in Design view

You can customise any field property while you work with a table in Design view. In Design view, you set a field’s data type in the table design grid, and you set other properties in the Field Properties pane.

  1. In the Navigation Pane, right-click the table.

  1. On the shortcut menu, press Design view.

Top of Page

Change a field’s data type

You can modify various aspects of a field after you create it.

Warning: You can also change these aspects of a field that you did not just create. However, if data already exists in the field some actions will not be available, or may cause data loss.

  1. In the table design grid, find your target field to set the data type.

  1. In the Data Type column, select a data type from the list. What data types are available? See Data types for Access desktop databases for a complete list of available data types in Access databases.

Tips on data types

  • The maximum size of an Access database file is 2 gigabytes.

  • For phone numbers, part numbers, and other numbers that you plan to exclude from mathematical calculations, you should choose the Text data type instead of the Number data type. A numeric value that is stored as text can be sorted and filtered more logically, but cannot be easily used in calculations.

  • For the Text and Number data types, you can specify the field size or data type more precisely by setting a value in the Field Size property box.

Top of Page

Set other field properties

Note: Not all formats are available for all data types. Set the data type first, and then, if needed, set the format.

  1. In the table design grid, click your desired field to set properties. Access displays the properties for this field in the Field Properties pane. The data type of the field determines the properties that you can set.

  1. In the Field Properties pane, type your preferred settings for each property, or press F6 and then use the arrow keys to confirm a property.What field properties are available?

See Introduction to data types and field properties for a complete list of field properties available for each data type in Access databases.

Note: Not all properties are available for every field. A field’s data type determines which properties it has.

  1. To offer additional space for entering or editing a property setting in the property box, press SHIFT+F2 to present the Zoom box.

Tip: If you are entering an input mask or validation expression and would like help in building it, click Builder button next to the property box to display the appropriate builder.

  1. To save your changes, press CTRL+S.

Top of Page

Move a field

To move a field, drag it to your sought position. To choose multiple contiguous fields to move, pick the first field, hold down the SHIFT key, and then select the last field. You can then drag the selected group of fields to a new position.

Dragging a field changes its position on the Datasheet, but leaves the order of the fields in the table design. If you programmatically access the fields, the original order applies. For example, if you drag a field to a new position on the Datasheet, and then create a form from the table by using the Form button, the field will be in its original position.

Top of Page

See Also

Add a calculated field to a table

Add a field to a form or report

Leave a Reply

%d bloggers like this: