Introduction to tables

Tables are essential objects in a database because they hold all the information or data. For example, a database for a business can have a Contacts table that stores the names of their suppliers, e-mail addresses, and telephone numbers. This article provides an overview of tables in Access. Before you create tables, consider your requirements and determine all the tables that you might need. For an introduction to planning and designing a database, see Database design basics.

In this article

Overview

Add a table to a desktop database

Tables in Access web apps

Save a table

Overview

A relational database like Access usually has several related tables. In a well-designed database, each table stores data about a particular subject, such as employees or products. A table has records (rows) and fields (columns). Fields have different types of data, such as text, numbers, dates, and hyperlinks.

1. A record: Contains specific data, like information about a particular employee or a product.

2. A field: Contains data about one aspect of the table subject, such as first name or e-mail address.

3. A field value: Each record has a field value. For example, Contoso, Ltd. or someone@example.com.

Table and field properties

The characteristics and behavior of tables and fields are defined and controlled by the properties. A table's properties are set in the table's property sheet, For example, you can set a table's Default View property to specify how the table is displayed by default. A field's property defines an aspect of the field's behavior. You can also set field properties in Design view by using the Field Properties pane. Every field has a data type which defines the kind of information stored in the field. For example, multiple lines of text or currency are data types.

Table relationships

Although each table in a database stores data about a specific subject, tables in a relational database such as Access, store data about related subjects. For example, a database might contain:

• A customers table that lists your company's customers and their addresses.

• A products table that lists the products that you sell, including prices and pictures for each item.

• An orders table that tracks customer orders.

To connect the data stored in different tables, you would create relationships. A relationship is a logical connection between two tables that have a common field.

Keys

Fields that are part of a table relationship are called keys. A key usually consists of one field, but may consist of more than one field. There are two kinds of keys:

• Primary key: A table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Access automatically provides a unique identification number, called an ID number that serves as a primary key. For more information see adding or changing a table's primary key.

• Foreign key: A table can have one or more foreign keys. A foreign key contains values that correspond to values in the primary key of another table. For example, you might have an Orders table in which each order has a customer ID number that corresponds to a record in a Customers table. The customer ID field is a foreign key of the Orders table.

The correspondence of values between key fields forms the basis of a table relationship. You use a table relationship to combine data from related tables. For example, suppose that you have a Customers table and an Orders table. In your Customers table, each record is identified by the primary key field, ID.

To associate each order with a customer, you add a foreign key field to the Orders table that corresponds to the ID field of the Customers table, and then create a relationship between the two keys. When you add a record to the Orders table, you use a value for customer ID that comes from the Customers table. Whenever you want to view any information about an order's customer, you use the relationship to identify which data from the Customers table corresponds to which records in the Orders table.

1. A primary key is identified by the key icon next to the field name.

2. A foreign key — note the absence of the key icon.

Benefits of using relationships

Keeping data separated in related tables produces the following benefits:

• Consistency    Because each item of data is recorded only once, in one table, there is less opportunity for ambiguity or inconsistency. For example, you store a customer's name only once, in a table about customers, rather than storing it repeatedly (and potentially inconsistently) in a table that contains order data.

• Efficiency    Recording data in only one place means you use less disk space. Moreover, smaller tables tend to provide data more quickly than larger tables. Finally, if you don't use separate tables for separate subjects, you will introduce null values (the absence of data) and redundancy into your tables, both of which can waste space and impede performance.

• Comprehensibility    The design of a database is easier to understand if the subjects are properly separated into tables.

Top of Page

Add a table to an Access desktop database

Create a new table if you have a new source of data that doesn't belong in any of your existing tables. There are several options for adding a table to your Access database such as, 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, a Web service, 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.

For more information on adding a field to a desktop database, see the article Add a field to a table.

Create a new table in a new desktop database

1. Under File, click New > Blank desktop database.

2. In the File Name box, type a file name for the new database.

3. To save the database in a different location, click the folder icon.

4. Click Create.

The new database opens, with a new table named Table1, rename the table based on the type of data it stores.

Create a new table in an existing database

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

2. Under Create, click Table.

A new table is added and opens in Datasheet view.

Top of Page

External data

You can link to a variety of external data sources, such as other databases, text files, and Excel workbooks. When you link to external data, Access can use the link as if it were a table. Depending on the external data source and the way that you create the link, you can edit the data in the linked table, and can create relationships that involve the linked table. However, you cannot change the design of the external data by using the link.

Import or link to create a table

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

When you import data, you create a copy of the data in a new table in the current database. Subsequent changes to the source data will have no effect on the imported data, and changes to the imported data do not affect the source data. After you connect to a data source and import its data, you can then use the imported data without connecting to the source. You can change the design of an imported table.

When you link to data, you create a linked table in the current database that represents a live link to the existing information that is stored elsewhere. When you change data in a linked table, you are changing it in the source. Whenever data changes in the source, that change is shown in the linked table. You must be able to connect to the data source whenever you use 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. For more information about linking to Access from Excel, search Excel Help

Create a new table by importing or linking to external data

1. On the External Data tab, in the Import & Link group, click one of the available data sources.

2. Follow the instructions in the dialog boxes that appear at each step.

   Access creates the new table and displays it in the Navigation Pane.

Tip:  You can also import or link to a SharePoint list, for more information, see Import from or link data to a SharePoint list.

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.

1. Under Create, click SharePoint Lists and do one of the following:

2. Create a SharePoint list that is based on a template

   1. Click Contacts, Tasks, Issues, or Events.

   2. In the Create New List dialog box, type the URL for the SharePoint site where you want to create the list.

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

   4. To open the linked table after it is created, select the Open the list when finished check box (selected by default).

3. Create a new custom list

   1. Click Custom. In the Create New List dialog box, type the URL for the SharePoint site where you want to create the list.

   2. Enter a name for the new list and its description in the Specifya name for the new list and Description boxes.

   3. To open the linked table after it is created, select the Open the list when finished check box (selected by default).

4. Import the data from an existing list

   1. Click Existing SharePoint List.

   2. In the Get External Data dialog box, type the URL for the SharePoint site that contains the data that you want to import.

   3. Click Import the source data into a new table in the current database, and then click Next.

   4. Select the check box next to each SharePoint list that you want to import.

5. Link to an existing list

   1. Click Existing SharePoint List.

   2. In the Get External Data - SharePoint Site dialog box, type the URL for the SharePoint site that contains the list to which you want to link.

   3. Click Link to the data source by creating a linked table, and then click Next.

   4. Select the check box next to each SharePoint list to which you want to link.

Top of Page

Use a Web service to create a table

You can create a table in your database that connects to data at a Web site that provides a Web service interface.

Note:  Web service tables are read-only.

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

2. If the connection you want to use has already been installed, skip to step 5. Otherwise, continue with the next step.

3. Click Install new connection.

4. Select the connection file that you want to use, and then click OK.

5. In the Create Link to Web Service Data dialog box, expand the connection that you want to use.

6. Select the table that you want to link to. Access displays the fields on the right side of the dialog box.

7. Optionally, type a name for the linked table in the Specify link name box. Access will use this name for the linked table in the Navigation Pane.

8. Click OK. Access creates the linked table.

Top of Page

Set table properties in a desktop database

You can set properties that apply to an entire table or to entire records.

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

2. On the Home tab, in the Views group, click View, and then click Design View.

3. On the Design tab, in the Show/Hide group, click Property Sheet.

   .

4. On the property sheet, click the General tab.

5. Click the box to the left of the property that you want to set, and then enters a setting for the property. To see a list of table properties, click Available Table Properties.

6. Press CTRL+S.to save your changes.

   Use this table property	To
   Display Views On SharePoint Site	Specify whether views that are based on the table can be displayed on a SharePoint site. Note: The effects of this setting depend on the setting of the Display All Views On SharePoint Site database property. For more information, see the See Also section.
   Subdatasheet Expanded	Expand all subdatasheets when you open the table.
   Subdatasheet Height	Do 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.
   Orientation	Set the view orientation, according to whether your language is read left-to-right, or right-to-left.
   Description	Provide a description of the table. This description will appear in tooltips for the table.
   Default View	Set Datasheet, PivotTable, or PivotChart as the default view when you open the table.
   Validation Rule	Enter an expression that must be true whenever you add or change a record.
   Validation Text	Enter a message that is displayed when a record violates the expression in the Validation Rule property.
   Filter	Define criteria to display only matching rows in Datasheet view.
   Order By	Select one or more fields to specify the default sort order of rows in Datasheet view.
   Subdatasheet Name	Specify whether a subdatasheet should appear in Datasheet view, and if so, which table or query should supply the rows in the subdatasheet.
   Link Child Fields	List 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 Fields	List the fields in the table that match the Link Child Fields property that is specified for the table.
   Filter On Load	Automatically apply the filter criteria in the Filter property (by setting to Yes) when the table is opened in Datasheet view.
   Order By On Load	Automatically apply the sort criteria in the Order By property (by setting to Yes) when the table is opened in Datasheet view.

   Tip: To provide more space to enter or edit a setting in the property box, press SHIFT+F2 to display the Zoom box. If you are setting the Validation Rule property to an expression and would like help in building it, click next to the ValidationRule property box to display the Expression Builder.

Top of Page

Add a field to a table in an Access desktop database

You store each piece 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.

Before you create fields, try to separate data into its smallest useful parts. It is much easier to combine data later than it is to pull it apart. 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. For more information about designing a database and creating fields, see the links in the See Also section.

After you create a field, you can also set field properties to control its appearance and behavior.

You can create a new field in Access by entering data in a new column in Datasheet view. When you create a field by entering data in Datasheet view, 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

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. Enter data in the blank Add New Field column.

To add a new field:

1. Create or open a table in Datasheet view.

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

3. Enter data in the new field.

Top of Page

Set a field's properties

After you create a field, you can set field properties to control its appearance and behavior.

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 set some of the available field properties while you work in Datasheet view. To have access to and set the complete list of field properties; however, you must use Design view.

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.

• To open a table in Datasheet view:    In the Navigation Pane, right-click the table and on the shortcut menu, click Datasheet view.

• To 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.

• Right-click the heading of the field that you want to rename (for example, Field1).

• On the shortcut menu, click Rename Column.

• Enter the new name in the field heading.

Field names can consist of up to 64 characters (letters or numbers), including spaces.

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/2006, 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.

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.

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/2001. If you enter 10/2001 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. Click the Datasheet tab.

2. In the Data Type list, in the Data Type & Formatting group, select the data type that you want.

   

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 enter. 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, click the Datasheet tab.

2. In the Format list, in the Data Type & Formatting group, enter the format that you want.

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

Set other field properties

1. In Datasheet view, click the field for which you want to set the property.

2. On the Datasheet tab, in the Data Type & Formatting group, select the properties that you want.

   

Top of Page

Set field properties

You can set a field property by opening the table in Design view.

• To open a table in Design view   : In the Navigation Pane, right-click the table, and on the shortcut menu, click Design View.

• To change a field's data type   : Locate the field name for which you want to set the data type, click the Data Type field, and then choose a data type from the list.

• To set other field properties   : In the table design grid, select the field for which you want to set properties. In the Field Properties pane, enter the settings that you want for each property. Press CTRL+S.to save your changes.

  Note:  The properties that you can set, depends on the field's data type.

Top of Page

Tables in Access web apps

The Access client provides designers for creating and modifying tables that will be stored on SQL Server. The information that Access requires in order to create and maintain a table and properties such as lookup information and formatting are stored in a system table (Access.ColumnProperties) in the user's database. For more information, see creating an Access app.

Top of Page

Save a table

After you create or modify a table, in a desktop database you should save its design. When you save a table for the first time, give it a name that describes the data that it contains. You can use up to 64 alphanumeric characters, including spaces. For example, you might name a table Customers, Parts Inventory, or Products.

Access gives you lots of flexibility when it comes to naming your tables in web apps; however, there are some restrictions to be aware of. A table name can be up to 64 characters long, can include any combination of letters, numbers, spaces, and special characters except a period (.), exclamation point (!), square brackets ([]), leading space, leading equal sign (=), or nonprintable character such as a carriage return. The name also cannot contain 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. Click Save on the Quick Access Toolbar or press CTRL + S.

2. If you are saving the table for the first time, type a name for the table, and then click OK.

Top of Page