what is true about adding a new field to the right side of an existing data table

When you create an Access database, y'all store your data in tables—field of study-based lists that contain rows and columns. For instance, you can create a Contacts table to store a listing of names, addresses, and telephone numbers, or a Products table to store information about products. This article explains how to create a table, add fields to a table, set a table's master key, and how to set up field and tabular array properties.

Before you lot create tables and add fields, brand sure y'all understand the background concepts. For more information, see Introduction to tables.

In this article

Creating a table

A uncomplicated database, such as a contact listing, might apply only a unmarried table. Many databases, however, use several tables. When you create a new database, you create a new file on your computer that acts as a container for all of the objects in your database, including your tables.

You can create a tabular array by creating a new database, by inserting a tabular array into an existing database, or past importing or linking to a table from another data source — such every bit a Microsoft Excel workbook, a Microsoft Word document, a text file, or another database. When you create a new, blank database, a new, empty table is automatically inserted for you lot. You lot can then enter data in the table to start defining your fields.

Top of Page

Create a new table in a new database

1. Click File > New, and so select Bare desktop database.

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

3. To browse to a different location and salvage the database, click the folder icon.

4. Click Create.

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

Superlative of Folio

Create a new table in an existing database

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

2. In the Open dialog box, select the database that you lot want to open, and so click Open.

3. On the Create tab, in the Tables group, click Tabular array.

   A new tabular array is inserted in the database and the table opens in Datasheet view.

Top of Page

Importing or linking to create a table

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

When you import data, you create a copy of the data in a new table in the electric current database. Subsequent changes to the source information will accept no effect on the imported data, and changes to the imported data practise non affect the source data. After y'all connect to a information source and import its data, you can then use the imported data without connecting to the source. Y'all can alter the design of an imported table.

When you link to data, you create a linked tabular array in the current database that represents a live link to the existing information that is stored elsewhere. When you alter data in a linked table, you are irresolute it in the source. Whenever information changes in the source, that change is shown in the linked table. You must exist able to connect to the data source whenever you use a linked table. Yous cannot change the design of a linked tabular array.

Note:You cannot edit data in an Excel worksheet by using a linked table. As a workaround, import the source data into an Access database, and and then link to the database from Excel.

Top of Page

Create a new table past importing or linking to external data

1. Click File > Open.

2. In the Open dialog box, select and open up the database in which you lot wish to create a new tabular array.

3. On the External Data tab, in the Import & Link group, click ane of the bachelor data sources.

   

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

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

Height of Page

Use a SharePoint site to create a table

Yous tin can create a table in your database that imports from or links to a SharePoint list. You tin also create a new SharePoint list by using a predefined template. The predefined templates in Access include Contacts, Tasks, Issues, and Events.

1. Click File > Open.

2. In the Open dialog box, select the database in which yous want to create the new table, and so click Open.

3. On the Create tab, in the Tables grouping, click SharePoint Lists.

4. Do one of the post-obit:

   Create a SharePoint list that is based on a template

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

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

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

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

   Create a custom list

   1. Click Custom.

   2. In the Create New Listing dialog box, blazon the URL for the SharePoint site where y'all desire to create the list.

   3. Enter a name for the new listing and its clarification in the Specify a name for the new list and Clarification boxes.

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

   Import the data from an existing list

   1. Click Existing SharePoint List.

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

   3. Click Import the source data into a new table in the electric current database, and and then click Side by side.

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

   Link to a list

   1. Click Existing SharePoint List.

   2. In the Get External Data - SharePoint Site dialog box, type the URL for the SharePoint site that contains the listing to which yous desire to link.

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

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

Top of Page

Utilise a Web service to create a table

You can create a table in your database that connects to data at a Web site that provides a Web service interface. Web service tables are read-only.

1. On the External Data tab, in the Import & Link grouping, click More than and and then click Information Servicedue south.

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

3. Click Install new connection.

4. Select the connectedness file that yous want to use, and so click OK.

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

6. Select the table that y'all want to link to. Admission displays the fields on the right side of the dialog box.

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

8. Click OK. Admission creates the linked table.

Top of Page

Set a table's properties

In addition to setting backdrop fields, you tin can also set backdrop that apply to an entire tabular array or to entire records.

1. Select the table whose backdrop you want to ready.

2. On the Home tab, in the Views group, click View, and so click Pattern View.

3. On the Design tab, in the Bear witness/Hide grouping, click Property Sheet.

   

   The table property sheet is shown.

4. On the property sheet, click the Full general tab.

5. Click the box to the left of the property that you lot want to set, then enter a setting for the property.

   Don't forget to Press CTRL+S to salvage your changes.

   Apply this table property	To
   Display Views On SharePoint Site	Specify whether views that are based on the table can be displayed on a SharePoint site. Annotation:The effects of this setting depend on the setting of the Display All Views On SharePoint Site database property.
   Subdatasheet Expanded	Aggrandize all subdatasheets when yous open the tabular array.
   Subdatasheet Height	Do one of the following: If you want the subdatasheet window to expand to display all rows, exit this property set at 0". If yous want to control the height of the subdatasheet, enter the desired height in inches.
   Orientation	Fix the view orientation, according to whether your language is read left-to-right, or right-to-left.
   Clarification	Provide a clarification of the table. This description will appear in tooltips for the tabular array.
   Default View	Set up Datasheet, PivotTable, or PivotChart as the default view when y'all open up the table. PivotTable and PivotChart options were removed from Access starting in Admission 2013.
   Validation Rule	Enter an expression that must be true whenever you add or change a record.
   Validation Text	Enter a message that is displayed when a record violates the expression in the Validation Rule property.
   Filter	Define criteria to brandish only matching rows in Datasheet view.
   Society Past	Select one or more fields to specify the default sort order of rows in Datasheet view.
   Subdatasheet Name	Specify whether a subdatasheet should appear in Datasheet view, and if so, which table or query should supply the rows in the subdatasheet.
   Link Child Fields	List the fields in the table or query that are used for the subdatasheet that match the Link Master Fields property that is specified for the tabular array.
   Link Master Fields	List the fields in the table that lucifer the Link Child Fields property that is specified for the tabular array.
   Filter On Load	Automatically use the filter criteria in the Filter belongings (past setting to Yep) when the tabular array is opened in Datasheet view.
   Guild Past On Load	Automatically apply the sort criteria in the Order By property (by setting to Yes) when the tabular array is opened in Datasheet view.

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

Top of Folio

Relieve a table

After y'all create or modify a table, you should salve its design. When yous save a table for the first time, requite information technology a name that describes the data that it contains. Yous tin employ up to 64 alphanumeric characters, including spaces. For case, you might name a tabular array Customers, Parts Inventory, or Products.

Access gives you lots of flexibility when it comes to naming your tables; however, at that place are some restrictions to exist aware of. A tabular array proper noun can be upwards to 64 characters long, can include any combination of letters, numbers, spaces, and special characters except a flow (.), exclamation point (!), square brackets ([]), leading space, leading equal sign (=), or nonprintable character such as a carriage return. The name also cannot comprise whatsoever of the post-obit characters:` / \ : ; * ? " ' < > | # <TAB> { } % ~ &.

Tip:Y'all should make up one's mind on a naming convention for the objects in your database, and use it consistently.

1. Select File > Relieve, or printing CTRL+Due south.

2. If y'all are saving the table for the first time, blazon a name for the tabular array, so click OK.

Summit of Page

Setting a primary key

Unless you lot have a specific reason non to, yous should specify a primary key for a table. Access automatically creates an index for the primary key, which can assist improve database performance. Admission also makes sure that every record has a value in the primary key field, and that the value is e'er unique. Unique values are crucial, because otherwise there is no mode to reliably distinguish a particular row from other rows.

When yous create a new table in Datasheet view, Access automatically creates a primary fundamental for you and assigns it a field name of ID and the AutoNumber data type.

In Design view, you lot can alter or remove the chief central, or set the chief fundamental for a table that doesn't already have one.

Top of Page

Make up one's mind which fields to use as a primary key

Sometimes, you might already accept data that y'all want to use every bit a primary cardinal. For example, you may have existing ID numbers for your employees. If you create a table to track employee information, y'all might decide to employ the existing employee ID as the primary key for the tabular array. Or, perhaps employee ID is only unique in combination with department ID, requiring that y'all use both fields together as the primary key. A good candidate for the main central has the post-obit characteristics:

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

• The field or combination of fields is never empty or null — at that place is always a value.

• The values exercise not modify.

If no suitable information exists to apply as a main key, you can create a new field to use equally a principal key. When you create a new field to use as a principal primal, set up the field's data type to AutoNumber to assist make sure that it meets the three characteristics in the preceding list.

Pinnacle of Page

Set or alter the primary key

1. Select the tabular array whose master key you lot desire to set or change.

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

3. In the table design grid, select the field or fields that you want to use as the primary key.

   To select 1 field, click the row selector for the field that you want.

   To select more than than one field, hold down CTRL, and then click the row selector for each field.

4. On the Design tab, in the Tools group, click Primary Fundamental.

   

   A fundamental indicator appears to the left of the field or fields that you specify as the primary central.

Superlative of Page

Remove the chief key

1. Select the table whose primary key you want to remove.

2. On the Abode tab, in the Views group, click View, and so click Blueprint View.

3. Click the row selector for the current primary fundamental. If the master key consists of multiple fields, concur down CTRL, and so click the row selector for each field.

4. On the Pattern tab, in the Tools grouping, click Principal Primal.

   

   The cardinal indicator is removed from the field or fields that yous previously specified as the primary key.

When you relieve a new table without setting a primary key, Access prompts y'all to create a new field for the master key. If you click Yep, Access creates an ID field that uses the AutoNumber data blazon to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses it as the master key. If you click No, Access does not add a field, and no primary cardinal is set.

Elevation of Page

Adding fields

To store a new piece of data about something for which you already have an Admission table, consider adding a field to the table. For example, suppose you have a tabular array that stores the last name, start name, email accost, telephone number, and mailing address of each of your customers. If yous desire to commencement tracking each client'southward preferred means of communication, y'all add a field to store that data.

You store each piece of data that you want to track in a field. For instance, in a contacts table you create fields for Last Proper name, Beginning Proper noun, Telephone Number, and Address. In a products table you create fields for Production Name, Product ID, and Toll.

Every field has certain essential characteristics, such as a name that uniquely identifies the field within a tabular array, a data type that defines the nature of the information, the operations that tin be performed on the data, and how much storage space to prepare bated for each value.

Before you create fields, attempt to separate data into its smallest useful parts. It is much easier to combine data afterward than it is to pull it apart. For example, instead of a Full Name field, consider creating separate fields for Last Name and Offset Name. Then, you can easily search or sort past Get-go Name, Last Name, or both. If you lot plan to report, sort, search, or calculate on an detail of information, put that item in a field by itself.

Later on yous create a field, yous can also set field properties to control its appearance and behavior. For case, the Format property defines how the data appears in a datasheet or form that contains that field.

Top of Folio

Add a field past entering data

When you create a new table or open an existing table in Datasheet view, y'all tin add a field to the table by entering information in the Add New Field cavalcade of the datasheet (1). Access automatically assigns a information type for the field, based on the value that y'all enter. If no other data type is implied by your input, Admission sets the data blazon to Text but you can alter the information type.

To enter data in the Add New Field cavalcade:

1. Create or open up a table in Datasheet view past right-clicking the table that you want in the Navigation Pane and and then clicking Datasheet view from the shortcut menu.

2. In the Add New Field column, enter the name of the field that y'all want to create.

   Utilise a descriptive proper noun and then that the field will be easier to identify.

3. Enter data in the new field.

Acme of Page

Add a field by using a field template

Sometimes it is easier to choose from a predefined list of fields that fit your needs than to manually create a field. You can utilise the More Fields list to cull from a listing of field templates. A field template is a predefined prepare of characteristics and backdrop that describes a field. The field template definition includes a field name, a data type, a setting for the field'southward Format property, and other field properties.

1. On the Home tab, in the Views grouping, click View, so click Datasheet View.

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

   

3. Select a field in the More than Fields listing to insert the new column. Admission places the field to the right of the cavalcade where your cursor is currently located. If you choose 1 of the field options under the Quick Start heading, such equally Address, Access creates multiple fields in your tabular array to contain the various parts of an accost.

Peak of Page

Setting field backdrop

After y'all create a field, you can ready field properties to control its appearance and behavior.

For example, past setting field properties, you can:

• Control the appearance of data in a field

• Help prevent incorrect information entry in a field

• Specify default values for a field

• Help speed up searching and sorting on a field

You can set some of the available field properties while you piece of work in Datasheet view. To have admission to and fix the complete list of field properties; even so, you must utilize Design view.

The properties that you lot can ready, depend on the field'southward data type.

Top of Page

Set field properties in Datasheet view

You can rename a field, change its data blazon, alter its Format property, and modify some of a field's other properties while you piece of work in Datasheet view.

1. In the Navigation Pane, correct-click the table that you want to open.

2. On the shortcut carte, click Datasheet view.

Superlative of Folio

Rename a field

When you add a field by inbound information in Datasheet view, Access automatically assigns a generic name to the field. Access assigns the proper noun Field1 to the commencement new field, Field2 to the 2nd new field, and so on. Past default, a field'southward name is used as its label wherever the field is displayed, such equally a cavalcade heading on a datasheet. Renaming fields so that they have more descriptive names helps make them easier to use when you view or edit records.

1. Right-click the heading of the field that y'all want to rename (for instance, Field1).

2. On the shortcut carte, click Rename Field.

3. Enter the new name in the field heading.

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

Top of Page

Modify a field's data blazon

When you create a field by entering data in Datasheet view, Admission examines that information to decide the appropriate data blazon for the field. For example, if you lot enter 1/ane/2017, Access recognizes that data as a appointment and sets the information type for the field to Date/Time. If Access tin't definitively determine the information type, the data blazon is prepare to Text by default (Brusk Text if yous're using Access 2016).

The information type of the field determines which other field properties you can fix. For example, yous can set up simply the Append Simply holding for a field that has the Hyperlink data type or the Memo data type (Long Text if you're using Admission 2016).

There may be cases where you desire to manually change a field's data type. For example, suppose you lot take room numbers that resemble dates, such as x/2017. If you enter 10/2017 into a new field in Datasheet view, the automatic data type detection characteristic selects the Engagement/Fourth dimension information blazon for the field. Because room numbers are labels, and non dates, they should use the Text data type. Use the following procedure to modify a field's data blazon.

1. On the Ribbon, click the Fields tab.

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

   

   What data types are available?

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

   Tips on data types

   • The maximum size of an Access database file is 2 gigabytes.

   • To optimize functioning, yous should use the most appropriate Field Size when you create a Text or Number field. For example, if you expect to store postal codes of a predictable length, specify that length as the field size. Y'all tin can specify the field size past setting a value in the Field Size belongings box. For more than information, see the department, Set up other field backdrop.

   • For phone numbers, part numbers, and other numbers that you don't intend to use for mathematical calculations, you should select the Text data type instead of the Number data blazon. A numeric value that is stored as text can be sorted and filtered more than logically.

Elevation of Page

Change a field'due south format

In addition to determining the data type of a new field, Access may as well set the Format property for the field, depending on what y'all enter. For instance, if you enter 10:50 a.m., Access sets the information type to Date/Fourth dimension and the Format property to Medium Time. To manually change a field's Format belongings, practise the following:

1. On the Ribbon, click the Fields tab.

2. In the Format list, in the Formatting group, enter the format yous want.

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

Top of Page

Set other field properties

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

2. On the Fields tab, in the Properties, Formatting, or Field Validation groups, select the properties that you want.

Summit of Page

Set field backdrop in Design view

You tin can fix any field belongings while you work with a table in Design view. In Blueprint view, you set a field'due south data type in the table design grid, and you set other properties in the Field Backdrop pane.

1. In the Navigation Pane, right-click the tabular array.

2. On the shortcut menu, click Design view.

Top of Page

Change a field's data type

You tin can change various aspects of a field after you create it.

Warning:You can also change these aspects of a field that you did non but create. However, if data already exists in the field some actions will not be bachelor, or may cause data loss.

1. In the table blueprint grid, locate the field for which you want to set the data type.

2. In the Data Type cavalcade, choose a data type from the list.

   What information types are bachelor?

   Run across Information types for Admission desktop databases for a complete list of available information types in Access databases.

   Tips on data types

   • The maximum size of an Access database file is ii gigabytes.

   • For phone numbers, office numbers, and other numbers that you don't intend to utilize for mathematical calculations, you should select the Text data blazon instead of the Number data type. A numeric value that is stored as text can be sorted and filtered more than logically, just cannot be easily used in calculations.

   • For the Text and Number data types, yous can specify the field size or data type more precisely by setting a value in the Field Size holding box.

Top of Page

Set other field properties

Note:Non all formats are available for all information types. Prepare the data blazon first, and then, if needed, fix the format.

1. In the tabular array design grid, select the field for which you want to set properties. Access displays the properties for this field in the Field Properties pane.

   The data blazon of the field determines the backdrop that you tin set up.

2. In the Field Properties pane, enter the settings that you want for each property, or printing F6 and so use the arrow keys to select a property.

   What field backdrop are available?

   See Introduction to information types and field properties for a complete list of field properties available for each data type in Admission databases.

   Notation:Non all properties are available for every field. A field's data type determines which backdrop information technology has.

3. To provide more infinite for entering or editing a holding setting in the belongings box, printing SHIFT+F2 to display the Zoom box.

   Tip:If you are entering an input mask or validation expression and would like aid in building it, click next to the belongings box to display the appropriate builder.

4. To save your changes, printing CTRL+South.

Top of Page

Motility a field

To motion a field, elevate it to the position that you want. To select multiple face-to-face fields to move, click the first field, concur downward the SHIFT fundamental, and so click the last field. Yous can then drag the selected group of fields to a new position.

Dragging a field changes its position on the Datasheet, only does not change the order of the fields in the table design. If you programmatically access the fields, the original order applies. For example, if you lot drag a field to a new position on the Datasheet, and so create a form from the table by using the Grade button, the field will exist in its original position.

Top of Page

See Besides

Add a calculated field to a tabular array

Add a field to a form or report

coulterfropeon.blogspot.com

Source: https://support.microsoft.com/en-us/office/create-a-table-and-add-fields-8fdc65f9-8d40-4ff5-9212-80e6545e8d87