When you produce an accessibility database, you save your data in tables—subject-based lists that contain rows and also columns. Because that instance, you can produce a contacts table to store a list of names, addresses, and also telephone numbers, or a commodities table to store information about products. This post explains just how to produce a table, include fields to a table, set a table"s main key, and how to collection field and table properties.

You are watching: When you create a ____ report, the records must have been sorted in order by a key field.

Before you create tables and add fields, make sure you recognize the background concepts. For an ext information, see introduction to tables.

In this article

creating a table — create in a new database— develop in an present database— Importing or linking— — Use external data— — use a SharePoint site— — usage a web service— set a table"s properties— save a table

setting a primary crucial — Determine fields to use— set or readjust a primary key— remove a primary key

relocate a ar

adding fields — add by entering data— include by making use of a ar template— setup field properties— set in Datasheet view— — Rename a field— — adjust a data type— — change a format— — set other properties— collection properties in architecture view— — change a data type— — set other properties

Creating a table

A an easy database, such as a call list, could use just a solitary table. Many databases, however, use numerous tables. Once you produce a new database, you create a new file on your computer system that acts together a container for all of the objects in your database, consisting of your tables.

You can create a table by producing a new database, by inserting a table right into an present database, or by importing or linking come a table from one more data source — such together a urbanbreathnyc.com Excel workbook, a urbanbreathnyc.com word document, a text file, or another database. As soon as you produce a new, empty database, a new, north table is automatically inserted for you. You can then enter data in the table come start defining 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, form a paper name for the new database.

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

Click Create.

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

optimal of web page

Create a brand-new table in an currently database

Click File > Open, and click the database if it is listed under Recent. If not, choose one the the browse alternatives 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 placed in the database and also the table opens up in Datasheet view.

top of page

Importing or linking to produce a table

You can create a table by importing or linking come data that is stored elsewhere. You can import or link to data in one Excel worksheet, a SharePoint list, one XML file, another access database, a urbanbreathnyc.com Outlook folder, and more.

When you import data, you create a copy of the data in a brand-new table in the present database. Subsequent transforms to the source data will have no effect on the imported data, and changes come the imported data execute not influence the source data. After you affix to a data resource and import its data, you have the right to then usage the imported data without connecting to the source. Friend can adjust the style of one imported table.

When you connect to data, you produce a linked table in the current database the represents a live attach to the existing information that is stored elsewhere. Once you adjust data in a attached table, girlfriend are an altering it in the source. At any time data alters in the source, that adjust is displayed in the attached table. You must be able to affix to the data resource whenever you use a attached table. You cannot readjust the architecture of a attached table.


Note: You cannot edit data in one Excel worksheet by utilizing a linked table. As a workaround, income the source data into an access database, and then connect to the database indigenous Excel.


optimal of web page

Create a new table by 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 produce a new table.

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

*

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

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

peak of page

Use a SharePoint website to produce a table

You can produce a table in your database the imports native or web links to a SharePoint list. Girlfriend can additionally create a new SharePoint list by using a predefined template. The predefined templates in access include Contacts, Tasks, Issues, and also Events.

Click File > Open.

In the Open dialog box, pick the database in i beg your pardon you desire to develop the new table, and also 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 brand-new List dialog box, kind the URL for the SharePoint site where you desire to develop the list.

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

To open the linked table after it is created, pick the Open the list as soon as finished check box (selected through default).

Create a custom list

Click Custom.

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

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

To open up the linked table after the is created, choose the Open the list when finished examine box (selected through default).

Import the data from an present list

Click Existing SharePoint List.

In the Get outside Data dialog box, form the URL for the SharePoint site that consists of the data that you desire to import.

Click Import the resource data right into a brand-new table in the present database, and also then click Next.

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

Link come a list

Click Existing SharePoint List.

In the Get external Data - SharePoint Site dialog box, kind the URL because that the SharePoint site that includes the list to which you want to link.

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

Select the examine box next to each SharePoint perform to i m sorry you want to link.

peak of web page

Use a Web organization to produce a table

You can produce a table in your database the connects come data in ~ a internet site that offers a Web organization interface. Web business tables space read-only.

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

If the link you want to usage has currently been installed, skip to action 5. Otherwise, continue with the following step.

Click Install brand-new connection.

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

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

Select the table the you desire to connect to. Access displays the areas on the ideal side of the dialog box.

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

Click OK. Accessibility creates the connected table.

peak of page

Set a table"s properties

In addition to setup properties fields, you deserve to also collection properties that apply to whole table or to whole records.

Select the table who 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 building sheet is shown.

On the property sheet, click the General tab.

Click the box to the left of the residential or commercial property that you desire to set, and also then go into a setup for the property.

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

Use this table property

To

Display see On SharePoint Site

Specify even if it is views that are based on the table deserve to be presented on a SharePoint site.


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


Subdatasheet Expanded

Expand every subdatasheets when you open up the table.

Subdatasheet Height

Do among the following:

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

If you desire to control the height of the subdatasheet, get in the wanted height in inches.

Orientation

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

Description

Provide a summary of the table. This description will show up 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 choices were eliminated from Access beginning in accessibility 2013.

Validation Rule

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

Validation Text

Enter a blog post that is displayed when a document violates the expression in the Validation Rule property.

Filter

Define criteria to display only equivalent rows in Datasheet view.

Order By

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

Subdatasheet Name

Specify whether a subdatasheet should appear in Datasheet view, and if so, i beg your pardon table or query must supply the rows in the subdatasheet.

Link child Fields

List the fields in the table or query the are offered for the subdatasheet that enhance the Link master Fields building that is specified for the table.

Link master Fields

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

Filter on Load

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

Order through On Load

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

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

*
next to the ValidationRule home box to screen the Expression Builder.

height of web page

Save a table

After you produce or change a table, you have to save that is design. Once you save a table for the first time, give it a surname that explains the data the it contains. You can use up to 64 alphanumeric characters, consisting of spaces. For example, you can name a table Customers, parts Inventory, or Products.

Access offers you lots of versatility when it comes to naming your tables; however, there room some restrictions to be conscious of. A table name deserve to be up to 64 characters long, can incorporate any mix of letters, numbers, spaces, and also special personalities except a period (.), exclamation allude (!), square base (<>), leading space, leading 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 top top a specify name convention because that the objects in her database, and use the consistently.


Select File > Save, or push CTRL+S.

If you are saving the table for the first time, form a surname for the table, and then click OK.

optimal of web page

Setting a main key

Unless you have a particular reason not to, you need to specify a primary key for a table. Accessibility automatically create an index for the major key, i beg your pardon can aid improve database performance. Access also provides sure that every record has a worth in the primary crucial field, and also that the worth is always unique. Distinctive values room crucial, since otherwise there is no way to reliably distinguish a particular row from various other rows.

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

In architecture view, girlfriend can adjust or remove the primary key, or collection the primary key for a table the doesn"t already have one.

height of web page

Determine which areas to usage as a major key

Sometimes, friend might already have data the you want to use as a major key. For example, you may have actually existing ID number for her employees. If you develop a table to monitor employee information, you might decide to usage the present employee ID as the primary vital for the table. Or, probably employee i would is only unique in combination with room ID, requiring the you usage both areas together as the main key. A an excellent candidate for the primary an essential has the following characteristics:

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

The ar or combination of fields is never ever empty or null — over there is constantly a value.

The values do not change.

If no an ideal data exists to use as a primary key, friend can develop a brand-new field to usage as a main key. When you develop a brand-new field to use as a major key, collection the field"s data type to AutoNumber to aid make sure that the meets the three characteristics in the coming before list.

peak of web page

Set or readjust the primary key

Select the table whose primary crucial you want to collection or change.

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

In the table style grid, select the ar or fields that you want to usage as the primary key.

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

To select more than one field, host down CTRL, and then click the heat selector because that each field.

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

A vital indicator appears to the left that the field or fields that friend specify together the major key.

peak of page

Remove the primary key

Select the table who primary key you desire to remove.

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

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

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

The key indicator is gotten rid of from the ar or fields that you formerly specified together the main key.

When you save a brand-new table without setting a major key, access prompts you to create a new field for the primary key. If friend click Yes, access creates an ID ar that provides the AutoNumber data form to carry out a unique value for each record. If her table currently includes an AutoNumber field, accessibility uses it together the major key. If girlfriend click No, accessibility does not include a field, and no primary key is set.

top of page

Adding fields

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

You keep each item of data the you want to track in a field. For example, in a contact table you produce fields because that Last Name, very first Name, telephone Number, and Address. In a commodities table you produce fields for Product Name, Product ID, and Price.

Every field has specific essential characteristics, such together a name that uniquely identify the ar within a table, a data kind that defines the nature the the data, the operations that have the right to be perform on the data, and how lot storage room to set aside for each value.

Before you develop fields, try to separate data right into its smallest beneficial parts. It is much easier to incorporate data later on than it is to traction it apart. For example, instead of a complete Name field, think about creating separate areas for Last surname and an initial Name. Then, you can easily search or kind by an initial Name, critical Name, or both. If you plan to report, sort, search, or calculation on an item of data, placed that item in a ar by itself.

After you create a field, you have the right to also collection field properties to control its appearance and behavior. For example, the Format property defines how the data appears in a datasheet or kind that contains that field.

top of page

Add a field by entering data

When you create a brand-new table or open up an existing table in Datasheet view, you can include a ar to the table by beginning data in the Add brand-new Field column of the datasheet (1). Access automatically assigns a data type for the field, based upon the worth that friend enter. If no other data type is implied by your input, accessibility sets the data form to Text however 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 view by right-clicking the table the you desire in the navigating Pane and then clicking Datasheet view native the faster way menu.

In the Add brand-new Field column, go into the name of the field that you want to create.

Use a descriptive surname so the the ar will be less complicated to identify.

Enter data in the new field.

height of web page

Add a field by making use of a field template

Sometimes it is easier to select from a predefined list of fields that to the right your requirements than come manually produce a field. You can use the More Fields perform to choose from a list of ar templates. A field template is a predefined set of characteristics and properties that explains a field. The field template definition includes a ar name, a data type, a setting for the field"s Format property, and also other ar 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 brand-new column. Accessibility places the ar to the best of the obelisk where her cursor is at this time located. If you pick one that the field options under the quick Start heading, such as Address, accessibility creates multiple areas in her table to contain the assorted parts of one address.

peak of web page

Setting field properties

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

For example, by setup field properties, you can:

Control the illustration of data in a field

Help stop 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 of the available field nature while you work in Datasheet view. To have accessibility to and set the complete list of ar properties; however, you have to use style view.

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

height of web page

Set field properties in Datasheet view

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

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

On the shortcut menu, click Datasheet view.

peak of web page

Rename a field

When you include a field by entering data in Datasheet view, accessibility automatically assigns a generic surname to the field. Access assigns the surname Field1 to the first new field, Field2 come the second brand-new field, and so on. Through default, a field"s name is provided as its label wherever the ar is displayed, such together a obelisk heading on a datasheet. Renaming areas so the they have much more descriptive name helps make them easier to use as soon as you view or edit records.

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

On the faster way menu, click Rename Field.

Enter the new name in the ar heading.

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

top of web page

Change a field"s data type

The data kind of the field determines i m sorry other field properties you deserve to set. Because that example, girlfriend can collection only the Append Only property for a ar that has the Hyperlink data type or the Memo data type (Long text if you"re using accessibility 2016).

There may be instances where you desire to manually change a field"s data type. Because that example, expect you have actually room numbers the resemble dates, such as 10/2017. If you enter 10/2017 into a new field in Datasheet view, the automatic data type detection feature selects the Date/Time data kind 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 adhering to procedure to adjust 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 types are available?

See Data types for Access desktop computer databases because that a finish list of obtainable data varieties in accessibility databases.

Tips ~ above data types

The maximum dimension of an access database file is 2 gigabytes.

See more: Where To Watch Big Brother Over The Top Full Episodes, Big Brother: Over The Top

For call numbers, part numbers, and other numbers the you don"t intended to usage for math calculations, you should select the text data kind instead that the Number data type. A numeric value that is stored as text deserve to be sorted and filtered much more logically.

peak of web page

Change a field"s format

In enhancement to identify the data form of a brand-new field, access may also collection the Format residential or commercial property for the field, depending on what you enter. Because that example, if you get in 10:50 a.m., access sets the data kind to Date/Time and the Format building to tool Time. To manually adjust a field"s Format property, carry out the following: