Add one or more records to a database

This article explains how you can enter data into a Microsoft Office Access 2007 database, and provides the background information needed to understand the various data entry processes. The article also explains how to use several data entry techniques in Access.

In this article


The basics of adding records

To enter data accurately and quickly in an Access database, it helps to understand a few of the basics about how a database works. The information in the following sections explains some of the basic structure and design principles that control how you enter data.

How database design affects data entry

An Access database is not a file in the same sense as a Microsoft Office Word 2007 document or a Microsoft Office PowerPoint 2007 slide deck. Instead, an Access database is a a collection of objects — tables, forms, reports, queries, and so on — that must work together for a database to function properly.

In addition, those objects must adhere to a set of design principles or the database will either work poorly or fail altogether. In turn, those design principles affect how you enter data. Remember these facts about database objects and design as you proceed.

  • Access stores all data in one or more tables. The number of tables you use depends on the design and complexity of the database. Although you may view data in a form, a report, or in the results returned by a query, Access stores the data only in tables, and the other objects in the database are built on top of those tables.
  • Each table should accept only one type of data. For example, a table of business contact information should not contain sales information. If it does, finding the correct information can become difficult, if not impossible.
  • Typically, each of the fields in a table accepts only one type of data. For example, you cannot store notes in a field set to accept numbers. If you try to enter text in such a field, Access displays an error message. However, that is not a hard, fast rule. For example, you can store numbers (such as postal codes) in a field set to the Text data type, but you cannot perform calculations on that data.
  • With some exceptions, the fields in a record should accept only one value. For example, you can not enter more than one address in an address field. This is in contrast to Microsoft Office Excel 2007, which, by default, allows you to enter any number of names or addresses or images in a single cell unless you set that cell to accept limited types of data.

However, you can enter a delimited list of items in fields set to the Text or Memo data type. In addition, Office Access 2007 provides a new feature called the multivalued field. You use multivalued fields to attach multiple pieces of data to a single record, and to create lists that accept multiple values. For example, you can attach a Microsoft Office PowerPoint 2007 slide deck and any number of images to a record in your database. You can also create a list of names and select as many of those names as needed. The use of multivalued fields may seem to break the rules of database design because you can store more than one record per table field, but actually doesn't, because Access enforces the rules "behind the scenes," by storing the data in special, hidden tables.

  • In older versions of Access, you had to design and create at least one table before you could enter data. You had to decide which fields to add to the table, and you had set the data types for each field. In Office Access 2007, you can now open a blank table and begin entering data. Access will infer a data type for the field based on what you enter.

More information

The information in this section touches on database design, table design, and a new feature called multivalued fields.

The following links take you to articles that provide more information about those subjects.

This article explains fundamental concepts, such as planning a database, designing the data, and normalization — the process of splitting your data into related tables and eliminating redundant data.

This article explains how to create tables, add primary keys (fields that uniquely identify each row or record in the table), and how to set data types and table properties.

This article explains how to attach one or more pieces of data to an Attachment field.

This article explains how to use check box lists and check box drop-down lists to store more than one value.

How data types affect the way you enter data

When you design a database table, you select a data type for each of the fields in that table, a process that helps ensure more accurate data entry. For example, suppose that you open a blank table and enter a set of sales figures. Access then infers the Number data type for the field. If someone tries to enter text in that field, Access displays an error message and won't let that user save the changed record — a step that helps protect your data.

ShowShow me how to view data types

In the Navigation Pane, do one of the following:

  • Double-click the table that you want to investigate, and on the Datasheet tab, in the Data Types & Formatting group, look at the value in the Data Type list. The value changes as you place the cursor in the various fields in your table. This figure shows the list:

The Data Type list

  • Right-click the table you want to investigate and click Design View.

Access opens the table in the design grid, and the upper section of the grid shows the name and data type of each table field. This figure shows a typical table in the design grid.

Fields in Design view


The data type that you set for each table field provides the first level of control over what you can and cannot enter into a field. In some cases, such as when working with a Memo field, you can enter any data that you want. In other cases, such as when working with an AutoNumber field, the data type setting for the field prevents you from entering any information at all. The following table lists the data types that Office Access 2007 provides, and describes how they affect data entry.

Data type Effect on data entry
Text Text fields accept either text or numeric characters, including delimited lists of items. A text field accepts a smaller number of characters than does a Memo field — from 0 to 255 characters. In some cases, you can use conversion functions to perform calculations on the data in a Text field.
Memo

You can enter large amounts of text and numeric data in this type of field. Also, if the database designer sets the field to support rich text formatting, you can apply the types of formatting that you normally find in word processing programs, such as Word. For example, you can apply different fonts and font sizes to specific characters in your text, and make them bold or italic, and so on. You can also add Hypertext Markup Language (HTML) tags to the data.

In addition, Memo fields have a new property called Append Only. When you enable that property, you can append new data to a Memo field, but you cannot change existing data. The feature is intended for use in applications such as issue tracking databases, where you may need to keep a permanent record that remains unchangeable. When you place the cursor in a Memo field with the Append Only property enabled, by default, the text in the field disappears. You cannot apply any formatting or other changes to the text.

For more information about using rich-text formatting in a Memo field, see the article Format data in rows and columns.

Like Text fields, you can also run conversion functions against the data in a Memo field.

Number You can enter only numbers in this type of field, and you can perform calculations on the values in a Number field.
Date/Time

You can enter only dates and times in this type of field. Depending on how the database designer sets the field, you may encounter these conditions:

  • If the database designer set an input mask for the field (a series of literal and placeholder characters that appear when you select the field), you must enter data in the spaces and the format that the mask provides. For example, if you see a mask such as MMM_DD_YYYY, you must type Oct 11 2006 in the spaces provided. You cannot enter a full month name, or a two-digit year value.
  • If the designer did not create an input mask to control how you enter a date or time, you can enter the value using any valid date or time format. For example, you can type 11 Oct. 2006, 10/11/06, October 11, 2006, and so on.
  • The database designer might apply a display format to the field. In that case, if not input mask is present, you can enter a value in almost any format, but Access displays the dates in accordance with the display format. For example, you can enter 10/11/2006, but the display format might be set so that it displays the value as 11-Oct-2006.

For more information about input masks, see the article Format data in rows and columns.

Currency You can enter only currency values in this type of field. Also, you do not have to manually enter a currency symbol. By default, Access applies the currency symbol ( ¥, £, $, and so on) specified in your Windows regional settings.
AutoNumber You can not enter or change the data in this type of field at any time. Access increments the values in an AutoNumber field whenever you add a new record to a table.
Yes/No When you click a field that is set to this data type, Access displays either a check box or a drop-down list, depending on how you format the field. If you format the field to show a list, you can select either Yes or No, True or False, or On or Off from the list, again depending on the format applied to the field. You cannot enter values in the list or change the values in the list directly from a form or table.
OLE Object

You use this type of field when you want to display data from a file created with another program. For example, you can display a text file, an Excel chart, or a PowerPoint slide deck in an OLE Object field.

 Note   Attachments provide a faster, easier, and more flexible way to view data from other programs. See the Attachment entry, later in this table, for more information.

Hyperlink You can enter any data in this type of field, and Access wraps it in a Web address. For example, if you type a value in the field, Access surrounds your text with http://www.your_text.com. If you enter a valid Web address, your link will work. Otherwise, your link will result in an error message. Also, editing existing hyperlinks can be difficult because clicking a hyperlink field with your mouse starts your browser and takes you to the site specified in the link. To edit a hyperlink field, you select an adjacent field, use the TAB or arrow keys to move the focus to the hyperlink field, and then press F2 to enable editing.
Attachment

You can attach data from other programs to this type of field, but you cannot type or otherwise enter text or numeric data.

For information about using an Attachment field, see the article Attach files and graphics to the records in your database.

Lookup Wizard

The Lookup Wizard is not a data type. Instead, you use the wizard to create two types of drop-down lists: value lists and lookup fields. A value list uses a delimited list of items that you enter manually when you use the Lookup Wizard. Those values can be independent of any other data or object in your database.

In contrast, a lookup field uses a query to retrieve data from one or more of the other tables in a database, or in another location such as a server running Windows SharePoint Services 3.0. The lookup field then displays the data in a drop-down list. By default, the Lookup Wizard sets the table field to the Number data type.

You can work with lookup fields directly in tables, and also in forms and reports. By default, the values in a lookup field appear in a type of list control called a combo box — a list that has a drop-down arrow: A blank lookup list. Depending on how the database designer has set the lookup field and the combo box, you can edit the items in the list and add items to the list. To do that, the database designer must set a property for the lookup field (the property is called Limit To List, and the designer has to turn it off).

If you cannot directly edit the values in a lookup list, you have to add or change the data in your predefined list of values, or in the table that serves as the source for the lookup field. For information about doing so, see Edit the items in a lookup field, later in this article.

Finally, when you create a lookup field, you can optionally set it to support multiple values. When you do so, the resulting list displays a check box next to each list item, and you can select or clear as many of the items as needed. This figure illustrates a typical multi-valued list:

A check box list.

For information about creating multivalued lookup fields and using the resulting lists, see the articles Use a list that stores multiple values and Guide to multivalued fields.

How table field properties affect the way you enter data

In addition to the design principles that control the structure of a database and the data types that control what you can enter in a given field, several field properties can also affect how you enter data into an Access database.

ShowView properties for a table field

Access now provides two ways to view the properties for a table field. You can use the controls on the Datasheet tab, or you can open the table in Design view. The following sets of steps explain how to use both techniques.

View table properties on the Datasheet tab

  1. In the Navigation Pane, double-click the table that you want to use.
  2. Click the Datasheet tab and use the controls in the Data Type & Formatting group to view the properties for each table field.

View table properties in Design view

  1. In the Navigation Pane, right-click the table you want and click Design View Button image on the shortcut menu.

Access opens the table in the design grid.

  1. In the lower part of the grid, click the General tab, if it isn't already selected.

–or–

To see the properties for a lookup field, click the Lookup tab.

A lookup field is a table field that uses a query to retrieve values from one or more tables in a database. By default, the lookup field presents those values to you in the form of a list. Depending on how a database designer sets the lookup field, you can select one or more items from that list.


The following table lists the properties that have the greatest impact on data entry and explains how they affect data entry.

Property Location in table design grid Possible values Behavior when you try to enter data
Field Size General tab 0-255 The character limit applies only to fields set to the Text data type. If you try to enter more than the specified number of characters, the field cuts them off.
Required General tab Yes/No

When turned on, this property forces you to enter a value in a field, and Access will not let you save any new data until you complete a required field. When turned off, the field will accept null values, meaning the field can remain blank.

 Note   A null value is not the same thing as a zero value. Zero is a digit, and "null" is a missing, undefined, or unknown value.

Allow zero length strings General tab Yes/No When turned on, you can enter zero-length strings — strings that contain no characters. To create a zero-length string, you enter a pair of double quotation marks in the field ("").
Indexed General tab Yes/No When you index a table field, Access prevents you from adding duplicate values. You can also create an index from more than one field. If you do this, you can duplicate the values in one field, but not in both fields.
Input Mask General tab Predefined or custom sets of literal and placeholder characters

An input mask forces you to enter data in a predefined format. The masks appear when you select a field in a table or a control on a form. For example, suppose you click a Date field and see this set of characters: MMM-DD-YYYY. That is an input mask. It forces you to enter month values as three-letter abbreviations, such as OCT, and the year value as four digits — for example, OCT-15-2006.

 Note   Remember that input masks only control how you enter data, not how Access stores or displays that data.

For more information about creating and using input masks, see the article Format data in rows and columns. For more information about how Access stores and displays Date/Time data, see the article Enter a date or time value.

Limit to List Lookup tab Yes/No Enables or disables changes to the items in a lookup field. New Access users sometimes try to change the items in a lookup field manually. When Access prevents you from changing the items in a field, this property is set to Yes. If this property is enabled and you need to change the items in a list, you must open the list (if you wan to edit a value list) or the table that contains the source data for the list (if you want to edit a lookup field) and change the values there. For more information about using lookup fields, see Edit the items in a lookup field, later in this article.
Allow Value List Edits Lookup tab Yes/No

Enables or disables the Edit List Items command for value lists, but not for lookup fields. To enable that command for lookup fields, enter a valid form name in the List Items Edit Form property. The Allow Value List Edits command appears on a shortcut menu that you open by right-clicking a list box or combo box control. When a you run the command, the Edit List Items dialog box appears. Alternately, if you specify the name of a form in the List Items Edit Form property, Access starts that form instead of displaying the dialog box.

 Note   You can run the Edit List Items command from list box and combo box controls located on forms, and from combo box controls located in tables and query result sets. Forms must be open in either Design view or Browse view; tables and query result sets must be open in Datasheet view.

List Items Edit Form Lookup tab Name of a data entry form If you enter the name of a data entry form as the value in this table property, that form opens when a user runs the Edit List Items command. Otherwise, the Edit List Items dialog box appears when users run the command.

Top of Page Top of Page

Add records directly to a table in datasheet view

A table open in Datasheet view resembles an Excel worksheet, and you can type or paste data into one or more fields — the equivalent of a cell in a worksheet.

Remember these facts as you proceed.

  • You do not need to explicitly save your data. Access commits your changes to the table when you move the cursor to a new field in the same row, or when you move the cursor to another row.
  • By default, the fields in an Access database are set to accept a specific type of data, such as text or numbers. You must enter the type of data that the field is set to accept. If you don't, Access displays an error message:

The invalid value error message

  • A field may have an input mask applied. An input mask is a set of literal and placeholder characters that force you to enter data in a specific format.

For more information about input masks, see the article Format data in rows and columns.

  • With the exception of attachments and multivalued lists, you can enter only one record in most fields. If you don't know whether a field accepts attachments, you can check the field's properties. To do so, see the steps in View properties for a table field, earlier in this article. You can always identify a multivalued list because Access displays a check box next to each list item.

Enter data in a table

  1. In the Navigation Pane, double-click the table that you want to use.

By default, Access opens the table in Datasheet view — a grid that resembles an Excel worksheet.

  1. Click or otherwise place the focus on the first field that you want to use, and then enter your data.
  2. To move to the next field in the same row, press TAB, use the Right or Left arrow keys, or click the cell in the next field.

When you press TAB, by default, Access uses your Windows regional settings to determine whether it moves the cursor to the left or to the right. If the computer uses a language that reads from left to right, the cursor moves to the right when you press the TAB key. If the computer uses a language that reads from right to left, the cursor moves to the left.

To move to the next cell in a column, use the Up or Down arrow keys, or click the cell you want.

Apply rich-text formatting to data in a Memo field

  1. With the table open in Datasheet view, select a Memo field.

Typically, memo fields contain comments or notes, so you can look for a field named "Comments" or "Notes." If you still can't find the Memo field, see the steps in View properties for a table field, earlier in this article.

  1. On the Home tab, in the Font group, use the buttons and menus to format the text.

Access Ribbon Image

You can apply different fonts and sizes, make text bold or italic, change colors, and so on.

Top of Page Top of Page

Add records by using a form

Data entry forms can provide an easier, faster, and more accurate way to enter data. You typically create forms when you need to enter data into more than one table at a time. You also create forms when you want to hide some of the fields in a table, when you want to make a database easier to use (a step that can reduce training costs), and when you want to help ensure that users enter data accurately.

How you use a form to edit data depends on the design of the form. Forms can contain any number of controls, such as lists, text boxes, buttons, and datasheets — grids that look like Excel worksheets. In turn, each of the controls on the form either reads data from or writes data to an underlying table field. What you do with a given control depends on the data type you have set for the underlying table field, any properties set for that field, and possibly on several properties that the database designer sets for each control. For more information about how data types and field properties affect data entry, see the sections How data types affect the way you enter data and How table field properties affect the way you enter data, earlier in this article.

The following sections explain how to use the most common data entry controls. If you have questions about your specific database, contact your system administrator or the database designer.

Add or edit text in a text box

When you add or edit text in a text box, you work with data in either a Text or Memo field. As you proceed, remember that you can set a Memo field (or the control bound to the Memo field) to support rich text formatting, and you can then apply different fonts, sizes, styles, and colors to your text.

Edit text in a text box

  • Place the cursor in the text box and change your data. Remember that you cannot perform calculations on numbers in a Text or Memo field.

Apply rich text formatting

 Note   You can follow these steps only when a text box is bound to a Memo field. See the steps in View properties for a table field, earlier in this article.

  1. With the form open in Form or Layout views, or the table open in Datasheet view, select the Memo field.
  2. On the Home tab, in the Font group, use the buttons and menus to format the text.

Access Ribbon Image

You can apply different fonts and sizes, make text bold or italic, change colors, and so on.

Enter data by using a list

You can use lists in forms, and in tables and query result sets open in datasheet view — a grid that resembles an Excel worksheet.

When you use a list on a form, you may see a combo box control or a list box control. These figures depict a combo box and list box control, respectively.

A blank lookup list

A basic list box control on a form

When you use a list in a table or query result set, Access uses a combo box by default.

In addition, lists can support multiple values. If you see a list with a check box next to each list item, you can select as many as 100 items on that list. Multivalued lists that you can create without programming are a new feature in Office Access 2007, and they solve a common business problem. For example, suppose you use an Access database to track customer support issues. If you need to assign multiple people to an issue, you can use a multivalued list. In earlier versions of Access, you would have needed some advanced programming to create a similar tool. These figures show typical multivalued lists.

A check box drop-down list in the open state.  A check box list.

You can also edit the items in a list. If you or your database designer enable the command, you can add, change, and remove list items directly from the list. However, database designers can choose to disable the command, so it may not be available for all databases.

The steps in the following sections explain how to use and edit lists.

Select items from a drop-down list

  1. Open the form, table, or query result set that contains the list.

 Note   You must open forms in Form view or Layout view. You must open tables and query result sets in Datasheet View.

  1. Click the down arrow next to the list, and then select the item you want.
  2. To commit your choice to your database, move the cursor to another field.

Select items from a list box

  1. Open the form that contains the list.
  2. Scroll down the list of items in the list box and select the item you want.
  3. To commit your choice to your database, move the cursor to another field.

Select items from a check-box drop down list

  1. Open the form, table, or query result set that contains the list.

 Note   You can use check box drop-down lists in forms, tables, and query result sets. To do so, you must open forms in Form view or Browse view, and you must open tables and query result sets in Datasheet view.

  1. Click the down arrow next to the list.
  2. Select up to 100 check boxes, and then click OK.

Select items from a check-box list

  1. Open the form that contains the list.

 Note   You must open the form in Form view or Browse view.

  1. Click the down arrow next to the list.
  2. Select as many as 100 check boxes, and then click OK.

Edit the items in a list

 Note   You can edit any type of list in a form, table, or query result set. Remember that you must open forms in Form view or Browse view, and you must open tables and query result sets in Datasheet view. Also, remember that the database designer or IT department can disable this command, so it may not be available at all times.

  1. Open the form, table, or query result set that contains the list.
  2. Right-click the list that you want to edit, and then click Edit List Items.

A dialog box or a data entry form appears. The screen that you see depends on the type of list that you want to edit. Access uses two basic types of lists — value lists and lookup lists. Value lists display a set of items that you enter manually, and lookup lists use a query to retrieve their data from one or more tables. Do one of the following.

  • If you are editing a value list, use the Edit List Items dialog box to edit the list data, and then click OK after you finish.
  • If you are editing a lookup list, a data entry form appears. Use that form to edit the list data.

Top of Page Top of Page

Edit the items in a lookup field

A lookup field presents a list of data that users can use to select one or more items. You can create two types of lookup lists.

  • Value lists    These contain a hard-coded set of values that you enter manually. The values reside in the Row Source property of the lookup field.
  • Lookup lists    These use a query to retrieve values from another table. The Row Source property of the field contains a query instead of a hard-coded list of values.

By default, Access displays lookup data in a drop-down list, though you can specify a list box control. A drop-down list opens to present the list, and then closes once you make a selection. A list box, in contrast, remains open at all times.

In earlier versions of Access, if you needed to edit the items in a lookup field, you had to locate the table that contained the field. If the field used a value list, you had to edit the items in the Row Source property of the field. If the field used a query, you had to locate the table referenced by the query and edit the data directly in that source table. By using Office Access 2007, you can run the Edit List Items command or you can edit the data directly in the Row Source property or the source table. For information about using the Edit List Items command, see Enter data by using a list, earlier in this article. The steps in this section explain how to edit the items directly in either the Row Source property or the source table. The process follows several broad steps.

  • Identify the lookup field. You follow a slightly different process when working with a form than with a table.
  • Identify the type of lookup field — either a value list or a lookup list. If you are using a lookup list, you identify the source table that provides the data for the lookup field.
  • Edit the items in the value list.

-or-

Open the source table for the lookup list and edit the data in that table.

Identify a lookup field from a form

  1. In the Navigation Pane, right-click the form and click Design View.

-or-

If the form is already open, you can right-click the form tab and click Design View on the shortcut menu.

–or–

On the Home tab, in the View group, click the View button to toggle between available views. Alternatively, you can click the arrow under View, and then select one of the available views from the menu..

Access Ribbon Image

  1. Right-click the list box or combo box control and click Properties.
  2. In the property sheet, click the All tab and locate the Row Source Type and Row Source properties. The Row Source Type property should contain either Value List or Table/Query, and the Row Source property must contain either a list of items separated by semicolons or a query. As needed, enlarge the property sheet so that you can read the list of items or the query.

Typically, value lists use this basic syntax: "item";"item";"item"

In this case, the list is a set of items surrounded by double quotation marks and separated by semicolons.

Select queries use this basic syntax: SELECT [table_or_query_name].[field_name] FROM [table_or_query_name].

In this case, the query contains two clauses (SELECT and FROM). The first clause refers to a table or query and a field in that table or query. The second clause refers only to the table or query. Here is a key point to remember: SELECT clauses may not contain the name of a table or query, although they do contain the name of at least one field. However, all FROM clauses must refer to a table or query. So, you can always find the source table or source query for a lookup field by reading the FROM clause.

  1. Do one of the following.
    • If you are using a value list, edit the items in the list. Make sure you surround each item with double quotation marks, and separate each item with a semicolon.
    • If the query in the lookup list references another query, in the Navigation Pane, open that second query in Design view (right-click the query and click Design View). Note the name of the table that appears in the upper section of the query designer, and then go to the next step.

-or-

If the query in the Lookup field references a table, note the name of the table, and go to the next step.

  1. Double-click the table in the Navigation Pane to open it in Datasheet view, and then edit the list items as needed.

Identify a lookup field from a table

  1. In the Navigation Pane, right-click the table and click Design View.
  2. In the upper section of the query design grid, in the Data Type column, click or otherwise place the focus on any Text, Number, or Yes/No field.
  3. In the lower section of the table design grid, click the Lookup tab, and then look at the Row Source Type and Row Source properties.

The Row Source Type property must read Value List or Table/Query. The Row Source property must contain either a value list or a query.

Value lists use this basic syntax: "item";"item";"item"

In this case, the list is a set of items surrounded by double quotation marks and separated by semicolons.

Typically, select queries use this basic syntax: SELECT [table_or_query_name].[field_name] FROM [table_or_query_name].

In this case, the query contains two clauses (SELECT and FROM). The first clause refers to a table or query and a field in that table or query. The second clause refers only to the table or query. Here is a key point to remember: SELECT clauses may not contain the name of a table or query, although they do contain the name of at least one field. However, all FROM clauses must refer to a table or query. So, you can always find the source table or source query for a lookup field by reading the FROM clause.

  1. Do one of the following.
    • If you are using a value list, edit the items in the list. Make sure you surround each item with double quotation marks, and separate each item with a semicolon.
    • If the query in the Lookup field references another query, in the Navigation Pane, open that second query in Design view (right-click the query and click Design View). Note the name of the table that appears in the upper section of the query designer, and then go to the next step.

-or-

If the query in the Lookup field references a table, note the name of the table, and go to the next step.

  1. Double-click the table in the Navigation Pane to open it in Datasheet view, and then edit the list items as needed.

Top of Page Top of Page

Enter zero-length strings

Access allows you to distinguish between two kinds of blank values: Null values and zero-length strings. Null values indicate an unknown value, and zero-length strings indicate fields that contain a space. For example, suppose you have table of customer data, and that table contains a fax number field. You can leave the field blank if you are unsure of a customer's fax number. In that case, leaving the field blank enters a null value, which means you don't know what the value is. If you later determine that the customer doesn't have a fax machine, you can enter a zero-length string in the field to indicate that you know there is no value.

 Note   Remember that you can set a property that prevents fields from accepting null values. For more information about setting that property, see How data types affect the way you enter data, earlier in this article.

  1. Open a table in Datasheet view, or a form in Form view or Browse view.
  2. Select the field you want, and type two double quotation marks with no space between them ("").
  3. Move the cursor to another record to commit your changes to the database. By default, the quotation marks disappear.

Top of Page Top of Page

Add records by using the Data Collection feature

Access 2007 has a new feature called Data Collection that can be helpful when you want to collect data via e-mail and store it in Access. You can set up a data-entry form in Microsoft Office Outlook 2007, sent that form as an e-mail message to gather information, and store that data in an Access database. Explaining how to use the Data Collection feature is beyond the scope of this article.

For information about using Data Collection, see the article Collect data by using e-mail messages.

Top of Page Top of Page

Add records by importing or linking to data

You can import or link to data located in a variety of sources, including other Access databases, Excel workbooks, text files and Word documents, and lists on servers running Microsoft Windows SharePoint Services 2.0 and later.

Explaining how to import and link to those data sources is beyond the scope of this article. These links take you to information about importing and linking to data.

Top of Page Top of Page

Use an append query to add records from another database

An append query adds a group of records from one or more tables in a source database to one or more tables in a destination database. For example, suppose that you acquire some new customers and a database containing a table of information about those customers. To avoid entering that new data manually, you can append it to the appropriate table or tables in your database. You can also use append queries to:

  • Append fields based on criteria. For example, you might want to append only the names and addresses of customers with outstanding orders.
  • Append records when some of the fields in one table don't exist in the other table. For example, suppose that your Customers table has 11 fields, and the fields in the Customers table in another database match 9 of your 11 fields. You can use an append query to add just the data in the matching fields and ignore the others.

The process of creating an append query follows these basic steps:

  • Open the source database (the database that contains the records you want to append) and create a select query that returns only the records that you want to append.
  • Convert that select query to an append query.
  • Add the destination tables and fields to the append query. If you are appending records to another database, you first open that database and then select the tables.
  • Run the query to append the records.

 Note    Back up your data before you start. If you make a mistake, you can delete the appended records from the destination table — but manually deleting a large number of records can take a long time. Having a backup on hand can help you fix any mistakes in less time.

Create a select query

  1. Open your source database — the database that contains the records that you want to append.
  2. On the Create tab, in the Other group, click Query Design.

Access Ribbon Image

The query design grid appears, and the Show Table dialog box appears.

  1. Select the table that contains the records that you want to append, click Add, and then click Close.

The table appears as a window in the upper section of the query design grid. The window lists all the fields in the table. This figure shows a typical table in the query designer:

A table in the query designer

  1. Drag the fields that you want to append from the table to the Field row in the lower section of the query design grid.

You can add one table field per column in the lower section. To add all the fields quickly, click the asterisk (*) at the top of the list of table fields. This figure shows the query designer with several table fields added:

A query with three fields in the design grid

This figure shows the designer with all fields added:

A query with all table fields.

  1. On the Design tab, in the Results group, click Run.

Verify that the query returned the records that you want to append. As needed, you can clear the Show check box or press DELETE to remove unwanted fields. You can also drag additional fields to the design grid until you are satisfied with the query results.

  1. Continue to the next steps.

Convert the query to an append query

  1. On the Design tab, in the Query Type group, click Append.
  1. You can either append records from one table to another in the same database, or append records to a table in another database.

Append records to a table in the same database

  1. In the Append dialog box, click Current Database (if it isn't already selected) and then select the destination table from the Table Name list.
  2. Click OK.
    In step 4 of the previous section, you added part or all of the fields in the source table to the query design grid. If you added the entire table during that step, Access now adds the entire destination table to the Append to row, as shown here.
    An append query that includes all fields from two tables
    –or–
    If you added individual fields in step 4 in the previous section, and the field names in the source and destination tables match, Access automatically adds the destination field names in the Append to row, as shown here.
    An append query with matching fields
    –or–
    If you added individual fields, and some or all of the names in the source and destination tables don't match, Access leaves the mismatched fields in the Append to row blank. Click each blank field and select the source field you want from the resulting list, as shown here.
    Choosing destination fields for an append query
  3. To preview your changes, click View.
  4. Return to Design view and then click Run to append the records.

Append records to a table in another database

  1. In the Append dialog box, click Another Database.
  2. In the File Name field, type the location and name of the destination database.
  3. In the Table Name field, type the name of the destination table, and then click OK.

–or–

Click Browse and use the second Append dialog box to locate the destination database. Click OK after you locate and select the destination database. This closes the second dialog box. In the first dialog box, in the Table Name field, enter the name of the destination table, and then click OK.

Type the name of the destination table, and then click OK to close the first Append dialog box.

In step 4 of the previous section, you added part or all of the fields in the source table to the Field row in the query design grid. If you added the entire table during that step, Access now adds the entire destination table to the Append to row as shown here:

An append query that includes all fields from two tables

–or–

If you added individual fields in step 4 and the field names in the source and destination tables match, Access automatically adds the destination field names in the Append to row, as shown here:

An append query with matching fields

–or–

If you added individual fields, and some or all of the names in the source and destination tables don't match, Access leaves the mismatched fields in the Append to row blank. Click each blank field and select the destination field you want from the resulting list, as shown here:

Choosing destination fields for an append query

  1. To preview your changes, click View.
  2. Switch to Design view , and then click Run to append the records.

Top of Page Top of Page

Methods for saving time when entering data

Access provides a variety of ways to make the data entry process faster. Procedures in the following sections explain how to set a default value for fields and lists in forms and how to use shortcut keys to reuse values when you sort data.

Set a default value for a field or control

If a large number of records share the same value for a given field, such as a city or country/region, you can save time by setting a default value for the control bound to that field. When you open the form or create a new record, your default value appears in that control, by default.

Set a default value for a control

  1. Open the form in Design view.
  2. Right-click the control you want to work with and then click Properties.
  3. On the Data tab, set the Default Value property to the value you want.
  4. Repeat steps 1-3 to set a new default value.

Specify a different value

  • For a value in a text box, enter new data. You can overwrite a default value at any time.

–or–

  • For a value in a list, select a new value from the list.

Reuse values when sorting

As you move move between records, you may need to sort each record by using the same value. When you do, you can save time by using shortcut keys to repeat that sort value. For example, suppose you sort lists of orders by product type. In the first record, you type soft drinks to sort on orders where customers bought soft drinks. When you move to the next record, you can copy and reuse that sort value.

 Note   These steps assume that you have a form open in Form view.

  1. Move to the field that reuses the value from the corresponding field in the previous record.
  2. Press CTRL+' (apostrophe).

Top of Page Top of Page

 
 
Applies to:
Access 2007