Design considerations for updating data

A well-designed database not only helps ensure data integrity, but is easier to maintain and update. An Access database is not a file in the same sense as a Microsoft Word document or a Microsoft PowerPoint slide deck. Instead, it's a collection of objects — tables, forms, reports, queries, and so on — that must work together to function properly.

Users enter data primarily through controls. What you do with a given control depends on the data type set for the underlying table field, any properties set for that field, and properties set for the control. Finally, consider using additional database techniques such as validation, default values, lists and lookups, and cascading updates. 

For more information on updating data from a user point-of-view, see Ways to add, edit, and delete records.

In this article

How database design affects data entry

The information that you keep in a database is stored in tables, which contain data about a particular subject, such as assets or contacts. Each record in a table contains information about one item, such as a particular contact. A record is made up of fields, such as name, address, and telephone number. A record is commonly called a row, and a field is commonly called a column. These 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. Consider the following:

  • Tables    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 be based on one subject. For example, a table of business contact information should not contain sales information. If it does, finding and editing the correct information can become difficult, if not impossible.

  • Data types    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 and fast rule. For example, you can store numbers (such as postal codes) in a field set to the Short Text data type, but you cannot perform calculations on that data because Access considers it a piece of text.

    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 Excel, which lets 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. 

  • Multivalued fields    Access provides a feature called the multivalued field to attach multiple pieces of data to a single record, and to create lists that accept multiple values. You can always identify a multivalued list because Access displays a check box next to each list item. For example, you can attach a Microsoft PowerPoint 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 Access enforces the rules "behind the scenes," by storing the data in special, hidden tables.

  • Using forms    You typically create forms when you want to make a database easier to use, and 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 even datasheets. In turn, each of the controls on the form either reads data from or writes data to an underlying table field.

For more information, see Database design basics and Create a table and add fields.

Top of Page

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 or the field itself. When you open the form or table to create a new record, your default value appears in that control or field.

In a Table

  1. Open the table in Design View.

  2. At the top of the view, select the field.

  3. At the bottom of the view, select the General tab.

  4. Set the Default Value property to the value you want.

In a Form

  1. Open the form in Layout or 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.

Top of Page

Using validation rules to limit data

You can validate data in Access desktop databases as you enter it by using validation rules. Validation rules can be set in either table design or table datasheet view. There are three types of validation rules in Access:

  • Field Validation Rule    You can use a field validation rule to specify a criterion that all valid field values must meet. You should not have to specify the current field as a part of the rule unless you are using the field in a function. Restrictions on types of characters to be entered in a field may be easier to do with an Input Mask. For example, a date field might have a validation rule that disallows values in the past.

  • Record Validation Rule     You can use a record validation rule to specify a condition that all valid records must satisfy. You can compare values across different fields using a record validation rule. For example, a record with two date fields might require that values of one field always precede values of the other field (for example, StartDate is before EndDate).

  • Validation on a form    You can use the Validation Rule property of a control on a form to specify a criterion that all values input to that control must meet. The Validation Rule control property works like a field validation rule. Typically, you use a form validation rule instead of a field validation rule if the rule was specific only to that form and not to the table no matter where it was used.

For more information, see Restrict data input by using validation rules.

Top of Page

Working with value lists and Lookup fields

There are two types of list data in Access:

  • Value lists    These contain a hard-coded set of values that you enter manually. The values reside in the Row Source property of the field.

  • Lookup fields    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. The query retrieves 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 you set the lookup field, you can select one or more items from that list.

    Note    Lookup fields can confuse new Access users because a list of items appears in one location (the list that Access creates from the data in the lookup field), but the data can reside in another location (the table that contains the data).

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

To edit lists, you can also run the Edit List Items command or you can edit the data directly in the Row Source property of the source table. When you update the data in a lookup field, you update the source table.

For more information, see Create a list of choices by using a list box or combo box.

Examine a lookup field in a form

  1. Open the form in Layout or Design View.

  2. Right-click the list box or combo box control and click Properties.

  3. 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 should contain either a list of items separated by semicolons or a query. For more space, right-click the property, and select Zoom or press Shift+F2.

    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 don't have to contain the name of a table or query, although it is recommended, and they must 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.

  4. 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, open that second query in Design View (right-click the query in the Navigation Pane 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.

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

  5. Open the table in Datasheet View, and then edit the list items as needed.

Examine a lookup field in a table

  1. Open the table in 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 don't have to contain the name of a table or query, although it is recommended, and they must 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.

  4. 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.

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

  5. Open the table in Datasheet View, and then edit the list items as needed.

Top of Page

Deleting the data from a values list or Lookup field

The items in a value list reside in the same table as the other values in a record. By contrast, the data in a lookup field resides in one or more other tables. To remove data from a value list, open the table and edit the items.

Removing data from a lookup list requires additional steps, and those steps vary depending on whether the query for the lookup lists takes its data from a table or another query. If the query for the lookup list is based on a table, you identify that table and the field that contains the data that appear in the list. You then open the source table and edit the data in that field. If the query for the lookup list is based on another query, you must open that second query, find the source table and field from which the second query takes its data, and change the values in that table.

Remove data from a value list

  1. Open the table in Design View.

  2. In the upper section of the design grid, select the table field that contains the value list.

  3. In the lower section of the grid, click the Lookup tab, and then locate the Row Source property.

    By default, double quotation marks surround the items in a value list, and semicolons separate each item: "Excellent";"Fair";"Average";"Poor"

  4. As needed, remove the items from the list. Remember to delete the quotation marks that surround each deleted item. Also, do not leave a leading semicolon, do not leave pairs of semicolons together (;;), and if you remove the last item in the list, delete the final semicolon.

    Important    If you delete an item from a value list, and records in the table already use that deleted item, the deleted item remains part of the record until you change it. For example, suppose your company has a warehouse in City A, but then sells that building. If you remove "City A" from the list of warehouses, you will see "City A" in your table until you change those values.

Remove data from a lookup field

  1. Open the table in Design View.

  2. In the upper section of the design grid, select the lookup field.

  3. In the lower section of the design grid, click the Lookup tab and locate the Row Source Type and Row Source properties.

    The Row Source Type property should display Table/Query, and the Row Source property must contain a query that references a table or another query. Queries for lookup fields always begin with the word SELECT.

    Typically (but not always), a select query uses 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 and to a field in that table; conversely, the second clause refers only to the table. A point to remember: The FROM clause will always tell you the name of the source table or query. SELECT clauses might not always contain the name of a table or a query, although they always contain the name of at least one field. However, all FROM clauses must refer to a table or query.

  4. Do one of the following:

    • If the query in the Lookup field references another query, click the Build button (On the Data tab of the property sheet, click Builder button next) to open the query in Design view. Make note of the name of the table that appears in the upper section of the query designer, and then continue to step 5.

    • If the query in the Lookup field references a table, make note of the name of the table, and then continue to step 5.

  5. Open the source table in Datasheet View.

  6. Locate the field that contains the data used in the lookup list, and then edit that data as needed.

Top of Page

How data types affect the way you enter data

When you design a database table, you select a data type for each field in that table, a process that helps ensure more accurate data entry.

View data types

Do one of the following:

Use Datasheet View

  1. Open the table in Datasheet View.

  2. On the Fields tab, in the 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:

    The Data Type list

Use Design View

  1. Open the table in Design View.

  2. Look at the design grid. The upper section of the grid shows the name and data type of each table field.

    Fields in Design view

How data types affect data entry

The data type that you set for each table field provides the first level of control over what type of data is permitted into a field. In some cases, such as a Long Text field, you can enter any data that you want. In other cases, such as 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 Access provides, and describes how they affect data entry.

For more information, see Data types for Access desktop databases and Modify or change the data type setting for a field.

Data type

Effect on data entry

Short Text

Note, beginning in Access 2013, Text data types have been renamed to Short Text.

Short 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 Long Text field — from 0 to 255 characters. In some cases, you can use conversion functions to perform calculations on the data in a Short Text field.

Long Text

Note, beginning in Access 2013, Memo data types have been renamed to Long Text.

You can enter large amounts of text and numeric data in this type of field up to 64,000 characters. Also, you set 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. For more information, see Insert or add a rich text field.

In addition, Long Text fields have a property called Append Only. When you enable that property, you can append new data to a Long Text 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 Long Text 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.

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

Number

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

Large Number

Note, Large Number data types are available only in the Office 365 subscription version of Access.

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

For more information, see Using the Large Number data type.

Date/Time

You can enter only dates and times in this type of field. 

You can 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 2017 in the spaces provided. You cannot enter a full month name, or a two-digit year value. For more information, see Control data entry formats with input masks.

If you don't create an input mask, you can enter the value using any valid date or time format. For example, you can type 11 Oct. 2017, 10/11/17, October 11, 2017, and so on.

You can also apply a display format to the field. In that case, if no 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/2017, but the display format might be set so that it displays the value as 11-Oct-2017.

For more information, see Insert today’s date.

Currency

You can enter only currency values in this type of field. Also, you don't have to manually enter a currency symbol. By default, Access applies the currency symbol ( ¥, £, $, and so on) specified in your Windows regional settings. You can change this currency symbol to reflect a different currency, if needed.

AutoNumber

You can't 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.

Calculated

You can't enter or change the data in this type of field at any time. The results of this field are determined by an expression you define. Access updates the values in a Calculated field whenever you add or edit 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.

Attachments provide a faster, easier, and more flexible way to view data from other programs.

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 more information, see 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.

By contrast, a lookup field uses a query to retrieve data from one or more of the other tables in a database. 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

You can also use a list box which displays several items with a scroll bar to see more items:

A basic list box control on a form

Depending on how you set the lookup field and the combo box, you can edit the items in the list and add items to the list by turning off the Limit To List property of the lookup field.

If you cannot directly edit the values in a 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.

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 Store multiple values in a lookup field.

Top of Page

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.

View properties for a table field

Access provides two ways to view the properties for a table field.

In Datasheet view

  1. Open the table in Datasheet View.

  2. Click the Fields tab and use the controls in the Formatting group to view the properties for each table field.

In Design View

  1. Open the table in Design View.

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

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

How properties impact data entry

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.

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-2017.

Input masks only control how you enter data, not how Access stores or displays that data.

For more information see Control data entry formats with input masks and Format a date and time field.

Limit to List

Lookup tab

Yes/No

Enables or disables changes to the items in a lookup field. 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 want 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.

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 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.

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

Use cascading updates to change primary and foreign key values

At times, you may need to update a primary key value. If you use that primary key as a foreign key, you can automatically update your changes through all child instances of the foreign key.

As a reminder, a primary key is a value that uniquely identifies each row (record) in a database table. A foreign key is a column that matches the primary key. Typically, foreign keys reside in other tables, and they enable you to create a relationship (a link) between the data in the tables.

For example, suppose you use a product ID number as a primary key. One ID number uniquely identifies one product. You also use that ID number as a foreign key in a table of order data. That way, you can find all the orders that involve each product, because any time someone places an order for that product, the ID becomes part of the order.

Sometimes, those ID numbers (or other types of primary keys) change. When they do, you can alter your primary key value and have that change automatically cascade through all related child records. You enable this behavior by turning on referential integrity and cascading updates between the two tables.

Remember these important rules:

  • You can enable cascading updates only for primary key fields set to the Text or Number data types. You cannot use cascading updates for fields set to the AutoNumber data type.

  • You can enable cascading updates only between tables with a one-to-many relationship.

For more information about creating relationships. see Guide to table relationships and Create, edit or delete a relationship.

The following procedures go hand-in-hand and explain how to first create a relationship and then enable cascading updates for that relationship.

Create the relationship

  1. On the Database Tools tab, in the Show/Hide group, click Relationships.

  2. On the Design tab, in the Relationships group, click Show Table.

  3. In Show Table dialog box, select the Tables tab, select the tables that you want to change, click Add, and then click Close.

    You can press SHIFT to select multiple tables, or you can add each table individually. Select only the tables on the "one" and "many" sides of the relationship.

  4. In the Relationships window, drag the primary key from the table on the "one" side of the relationship and drop it on the Foreign key field of the table on the "many" side of the relationship.

    The Edit Relationships dialog box appears. The following figure shows the dialog box:

    Edit Relationships dialog box with existing relationship

  5. Select the Enforce Referential Integrity check box and click Create.

Enable cascading updates in primary keys

  1. On the Database Tools tab, in the Show/Hide group, click Relationships.

  2. The Relationships window appears, and displays the joins (shown as connecting lines) between the tables in the database. The following figure shows a typical relationship:

  3. Right-click the join line between the parent and child tables, and click Edit Relationship.

    a relationship between two tables

    The Edit Relationships dialog box appears. The following figure shows the dialog box:

    The Edit Relationships dialog box

  4. Select Cascade Update Related Fields, ensure that the Enforce Referential Integrity check box is selected, and then click OK.

Top of Page

Why ID numbers sometimes seem to skip a number

When you create a field that is set to the AutoNumber data type, Access automatically generates a value for that field in every new record. The value is incremented by default, so that every new record gets the next available sequential number. The purpose of the AutoNumber data type is to supply a value that is suitable for use as a primary key. For more information, see Add, set, change, or remove the primary key.

When you delete a row from a table that contains a field that is set to the AutoNumber data type, the value in the AutoNumber field for that row is not always automatically reused. For that reason, the number that Access generates might not be the number you expect to see, and gaps might appear in the sequence of ID numbers — this is by design. You should rely only on the uniqueness of the values in an AutoNumber field, and not their sequence.

Top of Page

Bulk updating data by using queries

Append, Update, and Delete queries are powerful ways to add, change, or remove records in bulk. Furthermore, bulk updates are easier and more efficient to perform when you use good principles of database design. Using an append, update, or delete query can be a time-saver because you can also reuse the query.

Important    Back up your data before you use these queries. Having a backup on hand can help you quickly fix any mistakes you might inadvertently make.

Append queries    Use to add many records to one or more tables. One of the most frequent uses of an append query is to add 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. For more information, see Add records to a table by using an append query.

Update queries    Use to add, change, or delete part (but not all) of many existing records. You can think of update queries as a powerful form of the Find and Replace dialog box. You enter a select criterion (the rough equivalent of a search string) and an update criterion (the rough equivalent of a replacement string). Unlike the dialog box, update queries can accept multiple criteria, allow you to update a large number of records in one pass, and allow you to change records in more than one table. For more information, see Create and run an update query.

Delete queries    When you want to either quickly delete a lot of data or delete a set of data on a regular basis, a delete is useful because the queries make it possible to specify criteria to quickly find and delete the data. For more information, see Ways to add, edit, and delete records.

Top of Page

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×