Access provides several ways to add or remove the columns in a datasheet. You can use Datasheet view to add or remove columns and set the data types for those columns. You can also add fields from a task pane, or you can open the table that underlies the datasheet and add a field in Design view. This topic explains how to use each method.
For more information about creating and using datasheets, see the articles Create a form using the Datasheet tool and Working with datasheets.
What do you want to do?
Understand columns in datasheets
A datasheet is the visual representation of the information contained in a database table, or of the results returned by a query. A column in a datasheet represents the same thing as a field in a database table. When you add or remove a column from a datasheet, you add or remove a field from the table that underlies the datasheet. If that field contains data, you also eliminate that information.
Working with columns
Access makes it faster and easier to add or remove a table field because you can perform those tasks in Datasheet view. By default, all tables in Datasheet view now contain a blank column labeled Click to Add. To add a column, you enter data into the first blank cell beneath that column header. You can also paste one or more pieces of data into the blank column. You no longer need to use Design view to add or remove columns, although you can if you want.
In addition, you no longer have to set the more common data types for the new column. By default, the fields in a database table must contain specific types of data, such as text, dates and times, numbers, and so on. Typically, you set data types when you design the tables for a database. However, Access now infers most data types when you first enter data in a new column. For example, if you enter a name in the first field in your new table, Access sets that field to the Text data type. If you paste a date, Access sets the field to the Date/Time data type, and so on. If you paste a mix of data, such as postal codes from several countries/regions, Access selects the data type that does the best job of preserving the information — usually, the Text data type. For more information, see the section Understand how Access assigns data types as you enter information, later in this article.
For more information about setting data types, see the article Data types for Access desktop databases. If you are not familiar with design rules for relational databases, see the article Database design basics.
The steps in the following sections explain how to add and remove columns in datasheets by using Datasheet view and Design view.
Add a column by using Datasheet view
The steps in this section explain how to use Datasheet view to add a column to a datasheet, give the column a name, and enter data. As you proceed, remember that following these steps adds and changes a field in the table that underlies your datasheet.
-
In the Navigation Pane, locate and double-click the table to which you want to add the field.
Access opens the table in Datasheet view.
-
Scroll to the right or left side of the datasheet (depending on your Windows Regional and Language settings), and locate the blank column.
By default, the words Click to Add appear in the column header of the blank column.
-
Enter some data in the first blank row beneath the header.
-or-
Paste one or more pieces of data into the field, starting with the first blank cell.
Save your changes.
Based on the type of data you enter, Access sets a data type for the field. For example, if you enter a name, Access sets the data type to Text.
For more information about how Access sets data types, see the section Understand how Access assigns data types as you enter information. For information about setting data types manually, see Set the data types that Datasheet view does not infer, also later in this article.
-
Double-click the column header and enter a name for the new field.
-or-
Right-click the column header and click Rename Field on the shortcut menu. Then type a name for the field.
-
Save your changes.
Remove a column by using Datasheet view
Before you remove a column from a datasheet, remember several important facts:
-
When you remove a column, you delete all the data in the column, and you cannot undo the deletion. For that reason, you should back up the table before you delete the column.
-
You cannot delete certain types of columns without some additional work. For example, you cannot use Datasheet view to delete a primary key field. You must use Design view to perform that task. Also, you cannot delete a primary key or a lookup field without first deleting the relationship between the field and the tables from which the field takes its data. Explaining how to delete related fields is beyond the scope of this article.
For more information, see the articles Database design basics, Add or change a table’s primary key in Access, Create, edit or delete a relationship and Create or delete a multivalued field.
Remove the column in Datasheet view
-
Right-click the header row of the column that you want to remove.
-
Click Delete Field on the shortcut menu.
-
Click Yes to confirm the deletion.
-
Save your changes.
Add a column by using Design view
If you are familiar with earlier versions of Access, you probably have some experience using Design view. Design view provides greater flexibility than Datasheet view because you can set all available data types, and you can create a lookup field, all without having to leave Design view.
Add the column in Design view
-
In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
-or-
On the Access status bar, click Design View.
-
In the Field Name column, select a blank row and type a name for the new field.
-
In the Data Type column, next to your new field name, select a data type for the new column.
-
Save your changes.
Remove a column by using Design view
The rules for deleting a column in Datasheet view also apply to working in Design view: If you delete a column, you lose all the data in that column. In addition, before you can delete a primary key or a lookup field, you must first delete the relationships for those fields.
For more information, see the articles Database design basics, Add or change a table’s primary key in Access, and Create or delete a multivalued field.
Remove the column in Design view
-
In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
-or-
On the Access status bar, click Design View.
-
Select the field (the row) that you want to delete.
-
On the Design tab, in the Tools group, click Delete Rows.
-or-
Press DELETE.
-
Save your changes.
Understand how Access assigns data types as you enter information
When you create a blank datasheet, Access assigns a data type to each field when you first enter data in that field. The following table lists the various types of data that you can enter and the data type that Access applies to each one.
Note: You cannot set the Attachment or OLE Object data types by entering data in a field, and you cannot enable support for rich-text editing by entering data in a field.
The following table shows how Access sets the data type for a field when you enter data in Datasheet view.
|
If you enter: |
Access creates a field with a data type of: |
|
John |
Short Text (Text) |
|
A block of text or text and numbers longer than 256 characters. |
Long Text (Memo) Note: You cannot use Datasheet view to enable rich-text formatting. Also, if you enable a property called Append Only for the Memo field, then by default, Access hides any text when you place your cursor in that field. For information about enabling rich-text formatting, see the article Create or delete a Rich Text field. |
|
http://www.contoso.com Access recognizes the following Internet protocols: http, ftp, gopher, wais, file, https, mhtml, mailto, msn, news, nntp, midi, cid, prospero, telnet, rlogin, tn3270, pnm, mms, outlook. Note: For Access to recognize the protocol and set the Hyperlink data type, you must follow the protocol with a non-whitespace character. |
Hyperlink |
|
50000 |
Number, Long Integer |
|
50,000 |
Number, Long Integer |
|
50,000.99 |
Number, Double |
|
50000.389 |
Number, Double |
|
12/31/2019 The date and time formats specified in your Windows Regional and Language settings control how Access formats Date/Time data. |
Date/Time |
|
December 31, 2019 Note: You must enter or paste more than the name of day for Access to infer the Date/Time data type. For example, if you enter "Tuesday," Access selects the Text data type. For Access to infer the Date/Time data type, you must enter a month in addition to the day. |
Date/Time |
|
10:50:23 |
Date/Time |
|
10:50 am |
Date/Time |
|
17:50 |
Date/Time |
|
$12.50 Access recognizes the currency symbol specified in your Windows Regional and Language settings. |
Currency |
|
21.75 |
Number, Double |
|
123.00% |
Number, Double |
|
3.46E+03 |
Number, Double |
Set the data types that Datasheet view does not infer
By default, you cannot perform some tasks by using Datasheet view:
-
You cannot set the Attachment or OLE Object data types by pasting data into a blank cell.
-
You cannot enable rich-text editing for a Long Text (Memo) field by pasting data. To enable rich-text support, you must set an option for the field in Design view.
The steps in this section explain how to perform those tasks.
Set an Attachment data types
-
If it isn't already selected, select the blank column (the one labeled Click to Add) in your datasheet.
-
In the drop-down list select Attachment.
-
Save your changes.
Add a Long Text (Memo) field to a table with rich-text editing
-
If it isn't already selected, select the blank column (the one labeled Click to Add) in your datasheet.
-
In the drop-down list select Rich Text.
-
Save your changes.
Convert a column into a lookup field
By default, you cannot use Datasheet view to convert a new column into a lookup field. If you are new to Access, a lookup field displays data from another source — either a table or a list of items — in a list. By default, Access uses a drop-down list to display lookup data, but can also use a list box (a list that doesn't open or close) in a form.
You can create two kinds of lookup fields — table-based lists and value lists. A table-based list uses a query to retrieve data from another table, and a value list displays a set of hard-coded values. The following procedure explains how to create both types of lists.
Add a lookup field
-
In the Navigation Pane, right-click the table that underlies your new datasheet and click Design View.
-or-
On the Access status bar, click Design View.
The table opens in Design view.
-
Select the field that you want to convert.
-or-
Select a blank row in the Field Name column and enter a name for the new field.
-
On the Design tab, in the Tools group, click Lookup Column.
-or-
In Design view, in the Data Type column, click the down arrow and select Lookup Wizard.
The Lookup Wizard starts.
-
Do one of the following:
-
Create a table-based list
-
Select I want the lookup column to look up the values in a table or query and click Next.
-
Under View, select an option, select a table or query from the list, and then click Next. For example, If you want to use values from a table in your lookup field, click Tables. If you want to use a query, click Queries. To see a list of all the tables and queries in the database, click Both.
-
Move the fields that you want to appear in your lookup list from the Available Fields pane to the Selected Fields pane, and then click Next.
-
Optionally, choose one or more sorting choices for the fields that you selected in the previous step, and then click Next.
-
Optionally, adjust the width of each column in your lookup list, and then click Next.
-
Optionally, under What label would you like for your lookup column, enter a name in the text box.
-
Optionally, select the Allow Multiple Values check box. Selecting this option allows you to select and store more than one item from the list.
-
Click Finish. If Access prompts you to save the table, click Yes. Access adds the lookup query to your new table. By default, the query retrieves the fields that you specify, plus the primary key values for the source table. Access then sets the data type for the lookup field to match the data type set for the primary key field in the source table. For example, if the primary key field in the source table uses the AutoNumber data type, Access sets the data type for your lookup field to the Number data type.
-
Return to Datasheet view, go to your lookup field, and select an item from the list.
Or
-
-
-
Create a value-based list
-
Click I will type in the values that I want, and then click Next.
-
In the Number of columns box, enter the number of columns that you want to appear in your list, then go to the first blank cell and enter a value.
When you enter your first value, another blank cell appears below the current cell.
-
When you finish entering your first value, use the TAB or down-arrow keys to move focus to the next cell, and enter a second value.
-
Repeat steps 2 and 3 until you finish creating your list, and then click Next.
-
Optionally, enter a name for the new field, and then click Finish.
-
