Add or delete a column in a datasheet
Microsoft Office Access 2007 provides several ways to add or remove the columns in a datasheet. You can now 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.
Note: The information in this topic explains how to do some, but not all, of the tasks that you can perform in a datasheet.
For more information about creating and using datasheets, see the article Open a blank datasheet.
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.
New features for working with columns
Office Access 2007 makes it faster and easier to add or remove a table field because you can now perform those tasks in Datasheet view. By default, all tables in Datasheet view now contain a blank column labeled Add New Field. 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.
Note: You cannot use Datasheet view to set all of the available data types. Also, remember that relational databases adhere to a set of design rules.
For more information about the using Datasheet view and setting data types, see the article Open a blank datasheet. If you are not familiar with design rules for relational databases, see the article Database design basics.
Tip: In Access 2010, you can add a field by dragging the field name from the new Field List pane.
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 Add New Field appear in the column header of the blank column. This figure illustrates a typical blank column.
-
Double-click the column header and enter a name for the new field.
-or-
Right-click the column header and click Rename Column on the shortcut menu. Then type a name for the field.
-
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.
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, set, change or remove the primary key, Create, edit or delete a relationship and Add or change a lookup field that lets you store multiple values.
Remove the column in Datasheet view
-
Right-click the header row of the column that you want to remove.
-
Click Delete Column 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. The tool has not changed in Office Access 2007. 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, set, change or remove the primary key, and Add or change a lookup field that lets you store multiple values
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.
For information about setting those data types and enabling rich-text editing, see the article Open a blank datasheet.
The following table shows how Access sets the data type for a field when you enter data in Datasheet view.
| If you enter: | Office Access 2007 creates a field with a data type of: |
| John | Text |
| A block of text or text and numbers longer than 256 characters. | 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 Enter or edit data in a control or column that supports rich text. |
| 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/2006 The date and time formats specified in your Windows Regional and Language settings control how Access formats Date/Time data. Show me how to set or change those settings In Windows Vista
In Microsoft Windows XP (Classic view)
In Microsoft Windows XP (Category view)
. | Date/Time |
| December 31, 2006 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. Show me how to set or change those settings In Windows Vista
In Microsoft Windows XP (Classic view)
In Microsoft Windows XP (Category view)
| Currency |
| 21.75 | Number, Double |
| 123.00% | Number, Double |
| 3.46E+03 | Number, Double |
, and then click Control Panel.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 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
For more complete information about each task, see the article Open a blank datasheet.
Set the OLE Object or Attachment data types
-
If it isn't already selected, select the blank column (the one labeled Add New Field) in your datasheet.
-
Click the first blank data row below the header.
-
On the Datasheet tab, in the Data Type & Formatting group, click the arrow in the drop-down list next to Data Type, and then select a data type.
-
Save your changes.
Enable rich-text editing for a Memo field
You can use Datasheet view to add a Memo field to a table, but you must use Design view to enable rich-text editing for that Memo field. The following steps explain how to perform both tasks.
Add a Memo field to a table
-
If it isn't already selected, select the blank column (the one labeled Add New Field) in your datasheet.
-
Click the first blank data row below the header.
-
On the Datasheet tab, in the Data Type & Formatting group, click the arrow in the drop-down list next to Data Type, and then select a data type
-
Save your changes and continue to the next procedure, in which you will enable rich-text editing.
Enable rich-text editing for a Memo field
-
Switch your table from Datasheet view to Design view. Access provides several ways to do this:
If you use object tabs, right-click the tab for the open datasheet and click Design View.
-or-
On the Access status bar, click Design View.
-or-
In the Navigation Pane, right-click the table that underlies the datasheet, and then click Design View.
-
In Design view, select your Memo field.
-
In the Field Properties pane, on the General tab, click the arrow in the cell next to Text Format, and then select Rich Text from the list.
A confirmation message appears, asking if you want to convert the column to Rich Text. Click Yes to convert the column.
-
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.
-
No comments:
Post a Comment