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

When you develop an accessibility database, you keep your data in tables—subject-based lists that contain rows and also columns. For instance, friend can develop a contacts table to keep a perform of names, addresses, and also telephone numbers, or a assets table to store information about products. This write-up explains exactly how to produce a table, add fields come a table, collection a table"s main key, and how to set field and also table properties.

You are watching: Every ____ consists of an object that contains a button for each unique value in that field.

Before you produce tables and add fields, make certain you understand the background concepts. For much more information, see development to tables.

In this article

creating a table — produce in a brand-new database— develop in an existing database— Importing or linking— — Use exterior data— — use a SharePoint site— — use a internet service— set a table"s properties— save a table

setup a primary key — Determine areas to use— collection or readjust a major key— eliminate a primary key

relocate a field

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

Creating a table

A straightforward database, such as a call list, can use only a single table. Numerous databases, however, use several tables. Once you produce a brand-new database, you produce a new file on your computer system that acts as a container for every one of the objects in her database, including your tables.

You can develop a table by creating a brand-new database, through inserting a table right into an currently database, or by importing or linking to a table from another data source — such together a Excel workbook, a indigenous document, a text file, or another database. Once you develop a new, blank database, a new, empty table is immediately 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 pick Blank desktop database.

In the File Name box, form a file name because that the new database.

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

Click Create.

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

height of web page

Create a new table in an currently database

Click File > Open, and also click the database if the is listed under Recent. If not, select one that the browse alternatives to situate 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.

top of web page

Importing or linking to produce a table

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

When you income data, you develop a copy of the data in a brand-new table in the current database. Subsequent transforms to the source data will have actually no impact on the imported data, and also changes to the imported data do not influence the source data. After ~ you attach to a data source and income its data, you have the right to then usage the imported data there is no connecting to the source. Friend can change the design of one imported table.

When you connect to data, you develop a attached table in the existing database that represents a live connect to the existing information that is save on computer elsewhere. Once you change data in a attached table, you are an altering it in the source. Whenever data changes in the source, that readjust is shown in the linked table. You need to be maybe to affix to the data resource whenever you use a linked table. Friend cannot readjust the style of a connected table.

Note: You cannot modify data in one Excel worksheet by using a attached table. Together a workaround, import the resource data into an accessibility database, and also then connect to the database indigenous Excel.

height of web page

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

Click File > Open.

In the Open dialog box, select and also open the database in i m sorry you wish to produce a brand-new table.

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


Follow the instructions in the dialog box that show up at each step.

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

optimal of web page

Use a SharePoint site to create a table

You can produce a table in her database that imports native or links to a SharePoint list. Girlfriend can also create a new SharePoint list by making use of 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 desire to create 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 that is based upon a template

Click either Contacts, Tasks, Issues, or Events.

In the Create brand-new List dialog box, type the URL for the SharePoint site where you want to create the list.

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

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

Create a tradition list

Click Custom.

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

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

To open up the linked table after the is created, select the Open the list once finished inspect box (selected by default).

Import the data from an present list

Click Existing SharePoint List.

In the Get exterior Data dialog box, type the URL because that the SharePoint website that includes the data the you want to import.

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

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

Link to a list

Click Existing SharePoint List.

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

Click Link to the data resource by developing a connected table, and then click Next.

Select the examine box next to each SharePoint list to i beg your pardon you want to link.

height of page

Use a Web business to create a table

You can produce a table in your database that connects to data in ~ a net site that gives a Web service interface. Web organization 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 next step.

Click Install new connection.

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

In the Create connect to Web service Data dialog box, increase the link that you desire to use.

Select the table that you desire to link to. Accessibility displays the areas on the right side the the dialog box.

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

Click OK. Accessibility creates the linked table.

optimal of page

Set a table"s properties

In enhancement to setup properties fields, you have the right to also set properties that apply to whole table or to entire records.

Select the table whose properties you want 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 residential property sheet is shown.

On the home sheet, click the General tab.

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

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

Use this table property


Display views On SharePoint Site

Specify whether views that are based upon the table deserve to be shown on a SharePoint site.

Note: The results of this setup depend on the setup of the Display all Views top top 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 home window to increase to screen all rows, leaving this property collection at 0".

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


Set the see 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 for the table.

Default View

Set Datasheet, PivotTable, or PivotChart as the default view when you open up the table. PivotTable and also PivotChart options were eliminated from Access starting in access 2013.

Validation Rule

Enter an expression that must be true at any time you add or adjust a record.

Validation Text

Enter a blog post 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 much more fields to specify the default type order of rows in Datasheet view.

Subdatasheet Name

Specify even if it is a subdatasheet should appear in Datasheet view, and also if so, i beg your pardon table or query need to supply the rows in the subdatasheet.

Link boy Fields

List the areas in the table or query that are provided for the subdatasheet that match the Link understand Fields building that is specified for the table.

Link master Fields

List the areas in the table that complement the Link boy Fields residential or commercial property that is mentioned for the table.

Filter top top Load

Automatically apply the filter criteria in the Filter residential or commercial property (by setup to Yes) once the table is opened up in Datasheet view.

Order by On Load

Automatically use the type criteria in the Order By residential or commercial property (by setup to Yes) as soon as the table is opened in Datasheet view.

Tip come provide more space to get in or edit a setting in the residential property box, push SHIFT+F2 to screen the Zoom box. If girlfriend are setting the Validation Rule home to an expression and also would like aid in building it, click

next to the ValidationRule property box to display the Expression Builder.

optimal of page

Save a table

After you produce or modify a table, you need to save its design. Once you conserve a table for the first time, give it a surname that defines the data that it contains. You deserve to use up to 64 alphanumeric characters, including spaces. Because that example, you can name a table Customers, components Inventory, or Products.

Access gives you too many of flexibility when it comes to naming your tables; however, there room some limitations to be aware of. A table name can be up to 64 characters long, can incorporate any combination of letters, numbers, spaces, and special personalities except a period (.), exclamation allude (!), square brackets (<>), top space, top equal authorize (=), or nonprintable character such as a carriage return. The name also cannot contain any kind of of the adhering to characters:` / : ; * ? " " | # % ~ &.

Tip: You need to decide on a naming convention because that the objects in your database, and use the consistently.

Select File > Save, or press CTRL+S.

If girlfriend are conserving the table for the very first time, kind a name for the table, and then click OK.

peak of web page

Setting a main key

Unless you have actually a details reason not to, you should specify a primary key for a table. Accessibility automatically creates an index because that the major key, i beg your pardon can assist improve database performance. Access also makes sure that every record has a worth in the primary crucial field, and that the value is constantly unique. Distinctive values space crucial, since otherwise there is no way to reliably distinguish a specific row from various other rows.

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

In design view, girlfriend can readjust or eliminate the primary key, or set the primary an essential for a table the doesn"t already have one.

top of web page

Determine which fields to usage as a primary key

Sometimes, girlfriend might already have data the you want to usage as a major key. Because that example, girlfriend may have existing ID numbers for your employees. If you create a table to monitor employee information, you might decide to use the present employee ID together the primary key for the table. Or, maybe employee identifier is only distinctive in mix with room ID, requiring that you use both fields together as the primary key. A good candidate for the primary key has the adhering to characteristics:

Each record has a unique value for the field or combination of fields.

The ar or combination of areas is never empty or null — over there is always a value.

The values execute not change.

If no perfect data exist to use as a primary key, girlfriend can create a brand-new field to usage as a primary key. Once you produce a new field to usage as a main key, collection the field"s data form to AutoNumber to assist make sure that the meets the three qualities in the coming before list.

peak of web page

Set or readjust the major key

Select the table who primary key you desire to collection or change.

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

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

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

To select much more than one field, organize down CTRL, and also then click the row selector because that each field.

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

A an essential indicator shows up to the left of the ar or areas that you specify as the major key.

height of page

Remove the major key

Select the table whose primary an essential you want to remove.

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

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

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

The vital indicator is eliminated from the ar or areas that you previously specified together the main key.

When you conserve a new table without setting a major key, accessibility prompts friend to create a brand-new field because that the major key. If friend click Yes, access creates an ID field that uses the AutoNumber data type to administer a distinct value for each record. If your table currently includes an AutoNumber field, accessibility uses it together the primary key. If girlfriend click No, accessibility does not include a field, and no primary crucial is set.

peak of web page

Adding fields

To store a brand-new piece that data about something for which you already have an accessibility table, consider including a ar to the table. For example, expect you have actually a table the stores the last name, very first name, email address, telephone number, and mailing deal with of each of your customers. If you desire to begin tracking every customer’s preferred method of communication, you include a ar to save that data.

You store each item of data the you desire to monitor in a field. Because that example, in a contacts table you develop fields because that Last Name, very first Name, telephone Number, and also Address. In a assets table you produce fields because that Product Name, Product ID, and Price.

Every field has certain essential characteristics, such together a name that uniquely identify the ar within a table, a data form that specifies the nature that the data, the to work that deserve to be carry out on the data, and also how much storage space to collection aside for each value.

Before you develop fields, shot to separate data right into its smallest valuable parts. That is much simpler to incorporate data later on than the is to pull it apart. For example, rather of a complete Name field, consider creating separate fields for Last surname and an initial Name. Then, you can conveniently search or type by an initial Name, last Name, or both. If you arrangement to report, sort, search, or calculate on an object of data, placed that item in a ar by itself.

After you produce a field, you can also collection field properties to control its appearance and also behavior. Because that example, the Format property defines how the data shows up in a datasheet or form that includes that field.

top of page

Add a ar by beginning data

When you produce a new table or open an present table in Datasheet view, girlfriend can add a ar to the table by beginning data in the Add brand-new Field tower of the datasheet (1). Access automatically assigns a data form for the field, based on the worth that friend enter. If no various other data type is comprise by her input, accessibility sets the data kind to Text but you can readjust the data type.


To go into data in the Add new Field column:

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

In the Add brand-new Field column, enter the name of the ar that you want to create.

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

Enter data in the new field.

peak of web page

Add a ar by using a field template

Sometimes that is less complicated to pick from a predefined perform of fields that right your needs than to manually create a field. You deserve to use the More Fields perform to choose from a list of field templates. A field template is a predefined set of characteristics and also properties that describes 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 ar in the More Fields perform to insert the new column. Accessibility places the field to the best of the shaft where your cursor is at this time located. If you select one of the field alternatives under the fast Start heading, such as Address, access creates multiple areas in your table to contain the miscellaneous parts of an address.

peak of web page

Setting field properties

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

For example, by setting field properties, you can:

Control the illustration of data in a field

Help avoid incorrect data entrance in a field

Specify default worths for a field

Help rate up searching and also sorting ~ above a field

You can set some the the accessible field properties while you work-related in Datasheet view. To have accessibility to and collection the finish list of field properties; however, you need to use style view.

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

peak of web page

Set field properties in Datasheet view

You deserve to rename a field, adjust its data type, change its Format property, and adjust some that a field"s various other properties while you work in Datasheet view.

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

On the shortcut menu, click Datasheet view.

height of page

Rename a field

When you add a ar by start data in Datasheet view, accessibility automatically assigns a generic name to the field. Accessibility assigns the surname Field1 come the first new field, Field2 to the second new field, and also so on. By default, a field"s surname is supplied as its label wherever the field is displayed, such together a shaft heading top top a datasheet. Renaming areas so the they have an ext descriptive names helps make them simpler to use as soon as you see or edit records.

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

On the shortcut menu, click Rename Field.

Enter the new name in the field heading.

Field names have the right to consist of as much as 64 characters (letters or numbers), consisting of spaces.

optimal of page

Change a field"s data type

The data form of the ar determines i beg your pardon other ar properties you have the right to set. Because that example, friend can set only the Append Only building for a ar that has the Hyperlink data kind or the Memo data type (Long text if you"re using access 2016).

There might be situations where you want to manually readjust a field"s data type. Because that example, intend you have room numbers the resemble dates, such as 10/2017. If you go into 10/2017 right into a new field in Datasheet view, the automatic data form detection function selects the Date/Time data form for the field. Due to the fact that room numbers room labels, and not dates, they must use the text data type. Use the complying with procedure to readjust 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 girlfriend want.


What data types are available?

See Data types for Access desktop databases for a complete list of available data types in accessibility databases.

Tips top top data types

The maximum size of an access database paper is 2 gigabytes.

See more: One Example Of A Primary Market Transaction Would Be The, Fin 3320 Chapter 2 Flashcards

For call numbers, part numbers, and also other numbers the you don"t intend to usage for math calculations, friend should pick the text data kind instead that the Number data type. A numeric value that is stored as text have the right to be sorted and also filtered an ext logically.

top of page

Change a field"s format

In enhancement to identify the data form of a new field, accessibility may also set the Format home for the field, relying on what friend enter. Because that example, if you enter 10:50 a.m., access sets the data type to Date/Time and the Format home to tool Time. To manually adjust a field"s Format property, carry out the following: