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

When you produce an accessibility database, you save your data in tables—subject-based lists the contain rows and also columns. For instance, friend can create a contact table to save a list of names, addresses, and telephone numbers, or a commodities table to keep information about products. This short article explains exactly how to produce a table, include fields to a table, set a table"s major key, and how to collection field and also table properties.

You are watching: How is creating a table in design view different from creating a table in datasheet view?

Before you create tables and add fields, make certain you understand the elevator concepts. For an ext information, see arrival to tables.

In this article

producing a table — create in a new database— produce in an currently database— Importing or linking— — Use exterior data— — usage a SharePoint site— — usage a web service— set a table"s properties— conserve a table

setup a primary an essential — Determine fields to use— collection or change a main key— eliminate a primary key

move a ar

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

Creating a table

A basic database, such together a call list, can use only a single table. Many databases, however, use numerous tables. Once you develop a brand-new database, you develop a new file on your computer system that acts as a container for all of the objects in her database, including your tables.

You can create a table by developing a brand-new database, through inserting a table into an existing database, or through importing or linking come a table from another data source — such together a Excel workbook, a indigenous document, a message file, or one more database. As soon as you produce a new, empty database, a new, empty table is automatically inserted for you. You can then get in data in the table come start specifying your fields.

height of web page

Create a brand-new table in a new database

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

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

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

Click Create.

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

optimal of page

Create a brand-new table in an present database

Click File > Open, and also click the database if it is provided under Recent. If not, choose one the the browse options to locate the database.

In the Open dialog box, select the database the you desire to open, and also then click Open.

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

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

peak of web page

Importing or linking to develop a table

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

When you import data, you develop a copy of the data in a brand-new table in the current database. Subsequent changes to the source data will have actually no impact on the imported data, and also changes come the imported data do not influence the resource data. After you attach to a data source and import its data, you deserve to then usage the imported data there is no connecting to the source. Girlfriend can change the style of an imported table.

When you link to data, you create a linked table in the existing database that represents a live attach to the existing details that is save elsewhere. Once you adjust data in a linked table, you are changing it in the source. Whenever data alters in the source, that change is presented in the linked table. You need to be able to affix to the data resource whenever you use a linked table. Friend cannot change the style of a linked table.

Note: You cannot edit data in one Excel worksheet by utilizing a connected table. Together a workaround, import the resource data into an access database, and then attach to the database native Excel.

top of web page

Create a brand-new table through importing or linking to outside data

Click File > Open.

In the Open dialog box, select and open the database in i beg your pardon you wish to develop a brand-new table.

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


Follow the indict in the dialog box that show up at every step.

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

top of web page

Use a SharePoint site to create a table

You can develop a table in your database the imports from or links to a SharePoint list. Girlfriend can likewise create a new SharePoint list by utilizing a predefined template. The predefined templates in access include Contacts, Tasks, Issues, and Events.

Click File > Open.

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

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

Do one of the following:

Create a SharePoint list the is based upon a template

Click either Contacts, Tasks, Issues, or Events.

In the Create new List dialog box, form the URL because that the SharePoint site 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 attached table after the is created, pick the Open the list when finished check box (selected through default).

Create a practice list

Click Custom.

In the Create new List dialog box, kind the URL for the SharePoint website where you want to develop the list.

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

To open the attached table after that is created, pick the Open the list once finished check box (selected through default).

Import the data native an existing list

Click Existing SharePoint List.

In the Get outside Data dialog box, form the URL because that the SharePoint website that has the data the you desire to import.

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

Select the inspect box beside each SharePoint list that you desire to import.

Link to a list

Click Existing SharePoint List.

In the Get outside Data - SharePoint Site dialog box, form the URL for the SharePoint site that includes the perform to i m sorry you desire to link.

Click Link to the data resource by producing a attached table, and then click Next.

Select the check box beside each SharePoint perform to which you want to link.

height of page

Use a Web company to produce a table

You can create a table in your database the connects come data at a web site that gives a Web service interface. Web company tables room read-only.

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

If the link you desire to usage has already been installed, skip to step 5. Otherwise, proceed with the following step.

Click Install brand-new connection.

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

In the Create link to Web company Data dialog box, expand the connection that you want to use.

Select the table the you want to connect to. Access displays the areas on the best side that the dialog box.

Optionally, type a name for the linked table in the Specify link name box. Access will usage this surname for the attached table in the navigating Pane.

Click OK. Accessibility creates the connected table.

peak of web page

Set a table"s properties

In addition to setting properties fields, you have the right to also collection properties that use to an entire table or to entire records.

Select the table whose properties you desire to set.

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

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


The table building sheet is shown.

On the property sheet, click the General tab.

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

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

Use this table property


Display views On SharePoint Site

Specify whether views that are based upon the table can be presented on a SharePoint site.

Note: The impacts of this setup depend top top the setup of the Display every Views on SharePoint Site database property.

Subdatasheet Expanded

Expand every subdatasheets when you open up the table.

Subdatasheet Height

Do one of the following:

If you want the subdatasheet window to increase to screen all rows, leaving this property set at 0".

If you want to control the elevation of the subdatasheet, go into the preferred height in inches.


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


Provide a summary of the table. This summary will appear in tooltips for the table.

Default View

Set Datasheet, PivotTable, or PivotChart as the default view as soon as you open the table. PivotTable and also PivotChart choices were removed from Access beginning in accessibility 2013.

Validation Rule

Enter an expression that should be true whenever you add or change a record.

Validation Text

Enter a blog post that is presented when a record violates the expression in the Validation Rule property.


Define criteria to screen only matching rows in Datasheet view.

Order By

Select one or much more fields come specify the default sort order that rows in Datasheet view.

Subdatasheet Name

Specify even if it is a subdatasheet should appear in Datasheet view, and if so, i m sorry table or query should supply the rows in the subdatasheet.

Link child Fields

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

Link master Fields

List the fields in the table that match the Link kid Fields residential property that is specified for the table.

Filter on Load

Automatically apply the filter criteria in the Filter building (by setting to Yes) when the table is opened up in Datasheet view.

Order through On Load

Automatically apply the kind criteria in the Order By home (by setting to Yes) once the table is opened in Datasheet view.

Tip come provide an ext space to enter or modify a setting in the building box, push SHIFT+F2 to screen the Zoom box. If friend are setting the Validation Rule residential or commercial property to one expression and also would like aid in building it, click

beside the ValidationRule property box to screen the Expression Builder.

height of page

Save a table

After you develop or modify a table, you must save that design. Once you conserve a table because that the very first time, give it a surname that explains the data that it contains. You have the right to use as much as 64 alphanumeric characters, including spaces. Because that example, you might name a table Customers, components Inventory, or Products.

Access offers you too many of flexibility when it pertains to 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 combination of letters, numbers, spaces, and special personalities except a duration (.), exclamation allude (!), square brackets (<>), leading space, top equal authorize (=), or nonprintable character such as a carriage return. The name also cannot contain any kind of of the complying with characters:` / \ : ; * ? " " | # % ~ &.

Tip: You should decide top top a specify name convention for the objects in her database, and use that consistently.

Select File > Save, or press CTRL+S.

If girlfriend are saving the table because that the very first time, kind a surname for the table, and also then click OK.

top of web page

Setting a primary key

Unless you have actually a particular reason no to, you need to specify a primary vital for a table. Accessibility automatically create an index because that the major key, which can help improve database performance. Access also provides sure that every record has a worth in the primary key field, and also that the value is constantly unique. Unique values are crucial, since otherwise over there is no method to reliably differentiate a details row from other rows.

When you create a new table in Datasheet view, accessibility automatically create a primary crucial for you and also assigns that a ar name that ID and the AutoNumber data type.

In style view, you can readjust or eliminate the major key, or set the primary an essential for a table the doesn"t currently have one.

height of web page

Determine which areas to usage as a primary key

Sometimes, you might currently have data that you want to use as a main key. Because that example, you may have existing ID number for your employees. If you develop a table to monitor employee information, you might decide to usage the currently employee ID as the primary vital for the table. Or, perhaps employee id is only distinctive in combination with room ID, requiring that you use both areas together together the main key. A an excellent candidate for the primary key has the complying with characteristics:

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

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

The values execute not change.

If no perfect data exists to use as a main key, friend can develop a brand-new field to use as a primary key. As soon as you develop a brand-new field to use as a major key, collection the field"s data type to AutoNumber to assist make sure that it meets the three features in the coming before list.

top of web page

Set or change the primary key

Select the table whose primary an essential you desire to set or change.

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

In the table architecture grid, pick the field or areas that you want to usage as the main key.

To select one field, click the row selector because that the field that you want.

To select an ext than one field, host down CTRL, and also then click the row selector for each field.

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

A crucial indicator shows up to the left of the ar or fields that girlfriend specify together the major key.

height of web page

Remove the major key

Select the table who primary key you desire to remove.

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

Click the row selector for the current primary key. If the primary key consists of lot of fields, host down CTRL, and also then click the heat selector for each field.

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

The vital indicator is gotten rid of from the field or fields that you previously specified together the major key.

When you save a brand-new table without setup a major key, accessibility prompts girlfriend to develop a brand-new field because that the main key. If friend click Yes, accessibility creates an ID field that uses the AutoNumber data type to carry out a distinct value because that each record. If your table already includes an AutoNumber field, access uses it together the primary key. If you click No, access does not include a field, and also no primary an essential is set.

top of page

Adding fields

To store a new piece the data around something for which you already have an access table, consider adding a field to the table. For example, mean you have actually a table the stores the critical name, very first name, email address, telephone number, and mailing deal with of each of her customers. If you desire to start tracking each customer’s preferred way of communication, you include a ar to keep that data.

You save each item of data the you desire to track in a field. For example, in a contact table you develop fields for Last Name, very first Name, phone call Number, and also Address. In a assets table you produce fields because that Product Name, Product ID, and also Price.

Every ar has particular essential characteristics, such together a name that uniquely identifies the ar within a table, a data kind that defines the nature of the data, the operations that deserve to be perform on the data, and also how much storage an are to collection aside for each value.

Before you create fields, try to different data into its smallest advantageous parts. It is much less complicated to integrate data later on than that is to traction it apart. Because that example, rather of a complete Name field, take into consideration creating separate areas for Last name and first Name. Then, you can quickly search or type by an initial Name, last Name, or both. If you arrangement to report, sort, search, or calculate on an item of data, placed that items in a field by itself.

After you produce a field, you can also collection field nature to regulate its appearance and also behavior. Because that example, the Format property defines how the data appears in a datasheet or kind that contains that field.

top of web page

Add a ar by start data

When you create a brand-new table or open an currently table in Datasheet view, girlfriend can include a ar to the table by start data in the Add brand-new Field shaft of the datasheet (1). Access automatically assigns a data type for the field, based on the worth that friend enter. If no various other data kind is implied by your input, accessibility sets the data type to Text however you can readjust the data type.


To go into data in the Add brand-new Field column:

Create or open a table in Datasheet check out by right-clicking the table the you want in the navigation Pane and then clicking Datasheet view native the faster way menu.

In the Add new Field column, get in the surname of the field that you want to create.

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

Enter data in the new field.

height of page

Add a ar by making use of a field template

Sometimes that is less complicated to select from a predefined list of areas that to the right your needs than come manually develop a field. You can use the More Fields perform to select from a perform of ar templates. A ar template is a predefined collection of characteristics and also properties that defines a field. The ar template an interpretation includes a field name, a data type, a setting for the field"s Format property, and other ar properties.

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

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


Select a ar in the More Fields perform to insert the new column. Accessibility places the ar to the ideal of the obelisk where your cursor is currently located. If you pick one that the field options under the fast Start heading, such as Address, accessibility creates multiple fields in her table come contain the assorted parts of one address.

top of web page

Setting field properties

After you create a field, you can collection field properties to regulate its appearance and behavior.

For example, by setup field properties, girlfriend 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 also sorting on a field

You can set some the the obtainable field nature while you job-related in Datasheet view. To have access to and set the complete list of field properties; however, you need to use style view.

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

top of web page

Set field properties in Datasheet view

You have the right to rename a field, adjust its data type, readjust its Format property, and change some the a field"s other properties if you occupational in Datasheet view.

In the navigating Pane, right-click the table that you desire to open.

On the faster way menu, click Datasheet view.

peak of web page

Rename a field

When you include a ar by beginning data in Datasheet view, accessibility automatically assigns a generic surname to the field. Accessibility assigns the name Field1 come the an initial new field, Field2 come the second brand-new field, and also so on. By default, a field"s name is used as its label wherever the ar is displayed, such as a shaft heading top top a datasheet. Renaming fields so the they have more descriptive name helps do them less complicated to use as soon as you see or edit records.

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

On the shortcut menu, click Rename Field.

Enter the new name in the field heading.

Field names can consist of approximately 64 personalities (letters or numbers), including spaces.

peak of web page

Change a field"s data type

The data form of the field determines which other field properties you have the right to set. For example, you can collection only the Append Only residential property for a ar that has the Hyperlink data kind or the Memo data form (Long text if you"re using access 2016).

There might be cases where you desire to manually readjust a field"s data type. For example, suppose you have actually 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 attribute selects the Date/Time data kind for the field. Since room numbers room labels, and also not dates, they must use the text 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, pick the data kind that you want.


What data varieties are available?

See Data species for Access desktop computer databases for a complete list of obtainable data types in access databases.

Tips top top data types

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

See more: Can Oxygen Have An Expanded Octet Rule, Illustrated Glossary Of Organic Chemistry

For phone numbers, component numbers, and other numbers that you don"t intend to use for math calculations, girlfriend should pick the message data kind instead of the Number data type. A numeric worth that is stored as text have the right to be sorted and filtered more logically.

peak of web page

Change a field"s format

In enhancement to determining the data kind of a brand-new field, accessibility may also set the Format building for the field, depending on what friend enter. For example, if you go into 10:50 a.m., access sets the data kind to Date/Time and the Format residential or commercial property to tool Time. To manually change a field"s Format property, perform the following: