Access for 365 accessibility 2021 accessibility 2019 access 2016 access 2013 access 2010 accessibility 2007 More...Less

When you create an accessibility database, you keep your data in tables—subject-based lists that contain rows and columns. Because that instance, friend can develop a contact table to store a perform of names, addresses, and also telephone numbers, or a commodities table to keep information around products. This short article explains just how to produce a table, include fields come a table, collection a table"s main key, and how to set field and also table properties.

You are watching: Another term used for the column selector feature is ____.

Before you produce tables and add fields, make certain you know the lift concepts. For an ext information, see arrival to tables.

In this article

developing a table — produce in a brand-new database— produce in an currently database— Importing or linking— — Use external data— — usage a SharePoint site— — use a net service— collection a table"s properties— conserve a table

setting a primary crucial — Determine areas to use— collection or change a primary key— remove a main key

move a ar

including fields — add by start data— include by using a ar template— setting field properties— collection in Datasheet view— — Rename a field— — change a data type— — change a format— — collection other properties— set properties in design view— — adjust a data type— — collection other properties

Creating a table

A an easy database, such as a call list, can use just a single table. Numerous databases, however, use number of tables. When you develop a brand-new database, you create a new file on your computer system that acts together a container for every one of the objects in your database, including your tables.

You can create a table by developing a brand-new database, by inserting a table into an present database, or by importing or linking to a table from an additional data source — such as a Excel workbook, a native document, a message file, or an additional database. Once you create a new, empty database, a new, north table is instantly inserted because that you. You deserve to then enter data in the table come start defining your fields.

peak of web page

Create a new table in a brand-new database

Click File > New, and then pick Blank desktop database.

In the File Name box, kind a record name for the brand-new database.

To browser to a various location and also save the database, click the folder icon.

Click Create.

The brand-new database opens, and a brand-new table called Table1 is created and opens in Datasheet view.

top of page

Create a new table in an currently database

Click File > Open, and click the database if that is detailed under Recent. If not, choose one that the browse choices to locate the database.

In the Open dialog box, pick the database that you want to open, and then click Open.

On the Create tab, in the Tables group, click Table.

A brand-new table is placed in the database and the table opens up in Datasheet view.

optimal of page

Importing or linking to create a table

You can produce a table by importing or linking to data the is stored elsewhere. You have the right to import or connect to data in one Excel worksheet, a SharePoint list, an XML file, another access database, a Outlook folder, and also more.

When you income data, you produce a copy that the data in a brand-new table in the existing database. Subsequent alters to the resource data will have no effect on the imported data, and changes come the imported data execute not impact the source data. ~ you attach to a data resource and import its data, you can then use the imported data without connecting to the source. Girlfriend can change the architecture of one imported table.

When you connect to data, you create a connected table in the existing database that represents a live attach to the existing details that is stored elsewhere. Once you change data in a attached table, you are changing it in the source. Anytime data changes in the source, that readjust is shown in the linked table. You need to be able to affix to the data source whenever you usage a linked table. Friend cannot change the style of a attached table.

Note: You cannot modify data in an Excel worksheet by utilizing a attached table. Together a workaround, import the resource data into an accessibility database, and then attach to the database native Excel.

peak of page

Create a new table through importing or linking to exterior data

Click File > Open.

In the Open dialog box, select and also open the database in i beg your pardon you great to create a new table.

On the External Data tab, in the Import & Link group, click among the obtainable data sources. 


Follow the accuse in the dialog boxes that show up at every step.

Access creates the new table and also displays that in the navigating Pane.

height of web page

Use a SharePoint website to produce a table

You can develop a table in her database that imports indigenous or web links to a SharePoint list. Friend can likewise create a brand-new SharePoint perform by utilizing a predefined template. The predefined templates in access include Contacts, Tasks, Issues, and Events.

Click File > Open.

In the Open dialog box, select the database in i beg your pardon you want to develop the new table, and then click Open.

On the Create tab, in the Tables group, click SharePoint Lists.

Do among the following:

Create a SharePoint list the is based on a template

Click either Contacts, Tasks, Issues, or Events.

In the Create brand-new List dialog box, kind the URL because that the SharePoint website where you desire to produce the list.

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

To open the linked table after that is created, pick the Open the list as soon as finished examine box (selected by default).

Create a practice list

Click Custom.

In the Create new List dialog box, type the URL because that the SharePoint website where you want to produce the list.

Enter a surname for the new list and its summary in the Specify a surname for the new list and Description boxes.

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

Import the data indigenous an currently list

Click Existing SharePoint List.

In the Get exterior Data dialog box, kind the URL because that the SharePoint site that consists of the data the you want to import.

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

Select the check box alongside each SharePoint list the you desire to import.

Link to a list

Click Existing SharePoint List.

In the Get external Data - SharePoint Site dialog box, form the URL for the SharePoint website that includes the list to i m sorry you desire to link.

Click Link come the data source by producing a linked table, and also then click Next.

Select the examine box beside each SharePoint list to which you want to link.

peak of page

Use a Web business to produce a table

You can develop a table in her database the connects come data in ~ a internet site that gives a Web service interface. Web business tables room read-only.

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

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

Click Install brand-new connection.

Select the connection document that you want to use, and also then click OK.

In the Create connect to Web company Data dialog box, broaden the connection that you desire to use.

Select the table the you desire to attach to. Accessibility displays the areas on the ideal side the the dialog box.

Optionally, type a name for the attached table in the Specify attach name box. Accessibility will usage this surname for the connected table in the navigating Pane.

Click OK. Accessibility creates the connected table.

top of page

Set a table"s properties

In enhancement to setup properties fields, you deserve to also collection properties that use to whole table or to entire records.

Select the table who properties you desire to set.

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

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


The table building sheet is shown.

On the home sheet, click the General tab.

Click the box to the left that the building that you desire to set, and then enter a setting for the property.

Don"t forget to press CTRL+S to conserve your changes.

Use this table property


Display see On SharePoint Site

Specify even if it is views that are based upon the table have the right to be shown on a SharePoint site.

Note: The effects of this setting depend top top the setting of the Display all Views ~ above SharePoint Site database property.

Subdatasheet Expanded

Expand every subdatasheets as soon as you open up the table.

Subdatasheet Height

Do one of the following:

If you desire the subdatasheet window to broaden to screen all rows, leave this property set at 0".

If you desire to control the elevation of the subdatasheet, go into the desired height in inches.


Set the check out orientation, follow to whether her language is read left-to-right, or right-to-left.


Provide a description of the table. This description will appear in tooltips because that the table.

Default View

Set Datasheet, PivotTable, or PivotChart together the default view when you open up the table. PivotTable and also PivotChart alternatives were gotten rid of from Access beginning in access 2013.

Validation Rule

Enter an expression that have to be true at any time you include or adjust a record.

Validation Text

Enter a message that is presented when a document violates the expression in the Validation Rule property.


Define criteria to display screen only matching rows in Datasheet view.

Order By

Select one or an ext fields come specify the default type order the rows in Datasheet view.

Subdatasheet Name

Specify whether a subdatasheet should show up in Datasheet view, and also if so, i beg your pardon table or query have to supply the rows in the subdatasheet.

Link son Fields

List the fields in the table or query the are supplied for the subdatasheet that complement the Link master Fields property that is mentioned for the table.

Link master Fields

List the areas in the table that match the Link child Fields residential property that is specified for the table.

Filter top top Load

Automatically use the filter criteria in the Filter home (by setup to Yes) as soon as the table is opened up in Datasheet view.

Order through On Load

Automatically use the sort criteria in the Order By residential property (by setup to Yes) when the table is opened in Datasheet view.

Tip come provide much more space to enter or modify a setting in the building box, push SHIFT+F2 to display the Zoom box. If friend are setting the Validation Rule residential or commercial property to an expression and would like help in structure it, click

next to the ValidationRule residential or commercial property box to screen the Expression Builder.

height of web page

Save a table

After you develop or modify a table, you must save that is design. As soon as you conserve a table because that the first time, offer it a surname that defines the data that it contains. You deserve to use as much as 64 alphanumeric characters, consisting of spaces. For example, you can name a table Customers, parts Inventory, or Products.

Access gives you lots of versatility when it concerns naming her tables; however, there space some constraints to be aware of. A table name can be as much as 64 personalities long, can encompass any mix of letters, numbers, spaces, and also special personalities except a duration (.), exclamation allude (!), square brackets (<>), top space, leading equal sign (=), or nonprintable character such together a carriage return. The name also cannot contain any kind of of the adhering to characters:` / \ : ; * ? " " | # % ~ &.

Tip: You need to decide ~ above a specify name convention because that the objects in her database, and use that consistently.

Select File > Save, or press CTRL+S.

If you are saving the table because that the first time, kind a name for the table, and then click OK.

height of page

Setting a primary key

Unless you have a particular reason no to, you need to specify a primary an essential for a table. Access automatically creates an index because that the main key, which can assist improve database performance. Accessibility also renders sure the every record has a worth in the primary an essential field, and that the value is constantly unique. Distinctive values space crucial, because otherwise there is no means to reliably distinguish a details row from various other rows.

When you produce a new table in Datasheet view, access automatically create a primary an essential for you and assigns the a field name of ID and also the AutoNumber data type.

In style view, girlfriend can change or eliminate the primary key, or collection the primary an essential for a table the doesn"t already have one.

height of page

Determine which fields to use as a major key

Sometimes, you might already have data the you desire to usage as a primary key. Because that example, friend may have existing ID number for her employees. If you produce a table to monitor employee information, you might decide to use the existing employee ID as the primary crucial for the table. Or, perhaps employee identifier is only distinctive in combination with department ID, requiring the you usage both areas together as the primary key. A good candidate for the primary vital has the adhering to characteristics:

Each record has actually a distinctive value because that the field or mix of fields.

The field or mix of fields is never empty or null — over there is always a value.

The values do not change.

If no an ideal data exists to use as a primary key, friend can create a new field to usage as a major key. As soon as you produce a brand-new field to use as a main key, collection the field"s data type to AutoNumber to assist make sure that the meets the three qualities in the coming before list.

peak of page

Set or readjust the primary key

Select the table who primary an essential you want to set or change.

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

In the table design grid, pick the ar or fields that you want to use as the main key.

To pick one field, click the heat selector for the ar that girlfriend want.

To select more than one field, organize down CTRL, and also then click the heat selector for each field.

On the Design tab, in the Tools group, click Primary Key.

A an essential indicator appears to the left the the ar or areas that friend specify as the main key.

optimal of page

Remove the major key

Select the table who primary key you want to remove.

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

Click the row selector for the existing primary key. If the primary crucial consists of many fields, host down CTRL, and also then click the heat selector because that each field.

On the Design tab, in the Tools group, click Primary Key.

The an essential indicator is eliminated from the ar or areas that you formerly specified as the main key.

When you save a brand-new table without setting a primary key, access prompts friend to create a brand-new field for the major key. If friend click Yes, access creates an ID field that supplies the AutoNumber data type to provide a distinctive value because that each record. If her table currently includes an AutoNumber field, accessibility uses it as the primary key. If you click No, access does not add a field, and also no primary key is set.

top of page

Adding fields

To store a brand-new piece of data about something for which you currently have an access table, consider including a field to the table. Because that example, mean you have actually a table the stores the critical name, first name, email address, telephone number, and also mailing deal with of every of your customers. If you want to start tracking every customer’s preferred method of communication, you include a ar to keep that data.

You keep each item of data the you want to monitor in a field. Because that example, in a contacts table you create fields because that Last Name, very first Name, call Number, and also Address. In a assets table you produce fields for Product Name, Product ID, and Price.

Every field has certain essential characteristics, such together a name the uniquely identify the field within a table, a data kind that specifies the nature the the data, the work that have the right to be performed on the data, and also how much storage an are to set aside for each value.

Before you create fields, shot to separate data into its smallest valuable parts. The is much much easier to combine data later than it is to pull it apart. For example, instead of a complete Name field, take into consideration creating separate areas for Last name and an initial Name. Then, you can quickly search or type by an initial Name, last Name, or both. If you plan to report, sort, search, or calculation on an item of data, placed that article in a ar by itself.

After you develop a field, you have the right to also collection field properties to manage its appearance and also behavior. For example, the Format property specifies how the data shows up in a datasheet or kind that includes that field.

top of page

Add a ar by entering data

When you develop a brand-new table or open an existing table in Datasheet view, you can include a field to the table by entering data in the Add new Field pillar of the datasheet (1). Accessibility automatically assigns a data form for the field, based upon the worth that friend enter. If no other data form is comprise by her input, accessibility sets the data type to Text yet you can readjust the data type.


To get in data in the Add brand-new Field column:

Create or open up a table in Datasheet see by right-clicking the table that you want in the navigation Pane and then clicking Datasheet view indigenous the shortcut menu.

In the Add new Field column, go into the surname of the ar that you desire to create.

Use a descriptive name so the the ar will be easier to identify.

Enter data in the brand-new field.

top of web page

Add a field by using a field template

Sometimes it is simpler to choose from a predefined list of fields that fit your requirements than to manually develop a field. You have the right to use the More Fields perform to select from a list of ar templates. A ar template is a predefined collection of characteristics and properties that explains a field. The field template meaning includes a ar name, a data type, a setup for the field"s Format property, and also other field properties.

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

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


Select a field in the More Fields list to insert the new column. Access places the ar to the appropriate of the tower where her cursor is currently located. If you select one the the field options under the fast Start heading, such as Address, accessibility creates multiple fields in her table to contain the miscellaneous parts of one address.

optimal of web page

Setting ar properties

After you develop a field, girlfriend can collection field nature to control its appearance and also behavior.

For example, by setting field properties, girlfriend can:

Control the figure of data in a field

Help protect against incorrect data entry in a field

Specify default values for a field

Help speed up searching and sorting ~ above a field

You can collection some that the obtainable field properties while you work in Datasheet view. To have accessibility to and collection the complete list of field properties; however, you have to use style view.

The properties that you have the right to set, count on the field’s data type.

optimal of page

Set ar properties in Datasheet view

You have the right to rename a field, adjust its data type, adjust its Format property, and adjust some that a field"s other properties if you job-related in Datasheet view.

In the navigation Pane, right-click the table the you want to open.

On the faster way menu, click Datasheet view.

top of web page

Rename a field

When you add a ar by start data in Datasheet view, accessibility automatically assigns a generic name to the field. Access assigns the surname Field1 to the very first new field, Field2 to the second brand-new field, and so on. By default, a field"s surname is provided as its label wherever the ar is displayed, such as a tower heading top top a datasheet. Renaming fields so the they have an ext descriptive name helps make them easier to use when you watch or modify records.

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

On the shortcut menu, click Rename Field.

Enter the brand-new name in the field heading.

Field names deserve to consist of as much as 64 personalities (letters or numbers), consisting of spaces.

height of page

Change a field"s data type

The data type of the ar determines i m sorry other ar properties you deserve to set. For example, girlfriend can set only the Append Only residential or commercial property for a field that has actually the Hyperlink data kind or the Memo data form (Long message if you"re using access 2016).

There may be situations where you want to manually change a field"s data type. Because that example, suppose you have room numbers that resemble dates, such as 10/2017. If you get in 10/2017 right into a new field in Datasheet view, the automatic data type detection attribute selects the Date/Time data form for the field. Due to the fact that room numbers room labels, and also not dates, they need to use the message data type. Usage the following procedure to change a field"s data type.

On the Ribbon, click the Fields tab.

In the Data Type list, in the Formatting group, choose the data type that girlfriend want.


What data varieties are available?

See Data varieties for Access desktop databases because that a finish list of accessible data species in accessibility databases.

Tips on data types

The maximum size of an accessibility database file is 2 gigabytes.

See more: Video: Penn State Rose Bowl Tickets Open To Students Friday, Saturday

For phone numbers, part numbers, and also other numbers that you don"t intend to usage for mathematics calculations, girlfriend should choose the text data form instead that the Number data type. A numeric value that is stored together text have the right to be sorted and filtered an ext logically.

height of web page

Change a field"s format

In enhancement to identify the data type of a new field, access may also set the Format residential or commercial property for the field, depending on what girlfriend enter. Because that example, if you get in 10:50 a.m., access sets the data kind to Date/Time and also the Format property to medium Time. To manually change a field"s Format property, do the following: