Access

Modifying Tables

Introduction

After working with your database, you might find that you need to make some changes to the tables that store your data. Access makes it easy to modify your tables to suit your database's needs.

In this lesson, you'll learn how to create and rearrange table fields. You'll also learn how to ensure your table data is correctly and consistently formatted by setting validation rules , character limits , and data types in your fields. Finally, we will direct you to additional options for performing simple math functions within your tables.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access sample database . You will need to have Access installed on your computer in order to open the example.

Watch the video below to learn more about modifying tables.

Modifying tables

In addition to making basic modifications to your tables, like adding and moving fields, you can make more advanced modifications that let you set rules for your data. All of these changes can help make your tables even more useful.

Adding and rearranging fields

Access makes it easy to rearrange existing fields and add new ones. When you add a new field, you can even set the data type , which dictates which type of data can be entered into that field.

There are several types of fields you can add to a table:

  • Short Text : This is the default option and is best for most text in Access. You should also choose it for numbers you don't plan to do math with, like postal codes and phone numbers.
  • Number : This is best for numbers you might want to do calculations with, like quantities of an item ordered or sold.
  • Currency : This automatically formats numbers in the currency used in your region.
  • Date & Time : This allows you to choose a date from a pop-out calendar.
  • Yes/No : This inserts a checkbox into your field.
  • Rich Text : This allows you to add formatting to text, like bold and italics .
  • Long Text : This is ideal for large amounts of text, like product descriptions.
  • Attachment : This allows you to attach files, like images.
  • Hyperlink : This creates a link to a URL or email address.

To add a new field to an existing table:

  1. Open the desired table, then click the header with the text Click to Add . If you already have several fields, you may need to scroll all the way to the right to see this option.
    Adding a new field
  2. A drop-down menu will appear. Select the desired data type for the new field.
    Selecting a data type
  3. Type a name for your field, then press the Enter key.
    Naming the new field

To move a field:

  1. Locate the field you want to move, then hover your mouse over the bottom border of the field header. The cursor will become a four-sided arrow.
    Hovering the mouse over the field
  2. Click and drag the field to its new location.
    Moving a field
  3. Release the mouse. The field will appear in the new location.
    The field moved to a new location

Advanced field options

On the previous page, you learned about setting the data type for new fields. When you set field data type, you are really setting a rule for that field. Databases often include rules because they help ensure users enter the correct type of data.

Why is this important? Computers aren't as smart as humans about certain things. While you might recognize that two and 2 or NC and North Carolina are the same thing, Access will not and therefore won't group these things together. Making sure to enter your data in a standard format will help you better organize, count, and understand it.

Rules can also determine which options you have for working with your data. For example, you can only do math with data entered in number or currency fields, and you can only format text entered into text fields.

There are three main types of rules you can set for a field: data type , character limit , and validation rules .

To change the data type for existing fields:

  1. Select the field with the data type you want to change.
  2. Select the Fields tab, then locate the Formatting group. Click the Data Type drop-down arrow.
    Clicking the Data Type drop-down arrow
  3. Select the desired data type.
    Selecting a new field data type
  4. The field data type will be changed. Depending on the data type you chose, you may notice changes to your information. For instance, because we set the data type for the Email field to Hyperlink , all of the email addresses in the field are now clickable links.
    All of the entries formatted as hyperlinks

You shouldn't change the field data type unless you are certain your field data is in the correct format for the new data type. Changing a field containing only text to the Number type, for instance, will delete all of your field data. This process is often irreversible.

Field character limits

Setting the character limit for a field sets a rule about how many characters—letters, numbers, punctuation, and even spaces—can be entered into that field. This can help to keep the data in your records concise and even force users to enter data a certain way.

In the example below, a user is entering records that include addresses. If you set the character limit in the State field to 2 , users can only enter two characters of information. This means they must enter postal abbreviations for the states instead of the full name—here, NC instead of North Carolina. Note that you can only set a character limit for fields defined as text.

To set a character limit for a field:

  1. Select the desired field.
  2. Click the Fields tab, then locate the Properties group.
  3. In the Field Size box, type the maximum number of characters you want to allow in your field.
    Setting a 2-character limit on the State field
  4. Save your table. The character limit for the field will be set.

Validation rules

A validation rule is a rule that dictates which information can be entered into a field. When a validation rule is in place, it is impossible for a user to enter data that violates the rule. For example, if we were asking users to input a state name into a table with contact information, we might create a rule that limits the valid responses to U.S. state postal codes. This would prevent users from typing something that wasn't actually a real state postal code.

In the example below, we will apply this rule to our Customers table . It's a fairly simple validation rule. We'll just name all of the valid responses a user could enter, which will mean users can't type anything else into the record. However, it's possible to create validation rules that are much more complex. For detailed information on how to write validation rules, review this tutorial from Microsoft on creating validation rules .

To create a validation rule:

  1. Select the field where you want to add a validation rule. In our example, we'll set a rule for the State field.
  2. Select the Fields tab, then locate the Field Validation group. Click the Validation drop-down command, then select Field Validation Rule .
    Clicking the Field Validation Rule command
  3. The Expression Builder dialog box will appear. Click the text box and type your validation rule. In our example, we want to limit data in the State field to actual state postal codes. We'll type each of the valid responses in quotation marks and separate them with the word Or , which lets Access know that this field can accept the response "AL" Or "AK" Or "AZ" or any of the other terms we've entered.
    Typing our validation rule, which will contain every state postal code, each separated by the word "Or"
  4. Once you're satisfied with the validation rule, click OK . The dialog box will close.
  5. Click the Validation drop-down command again. This time, select Field Validation Message .
    Clicking the Field Validation Message command
  6. A dialog box will appear. Type the phrase you want to appear in an error message when users try to enter data that violates the validation rule. Your message should let them know what data is permitted.
    Typing the phrase that will appear as an error message
  7. When you're satisfied with the error message, click OK .
  8. The validation rule is now included in the field. Users will be unable to enter data that violates the rule.
    Data has been entered that violates the validation rule

Simple validation rules can be written exactly like query criteria . The only difference is that query criteria search for data, while an identical validation rule either permits or rejects data. To see examples of query criteria, review our Query Criteria Quick Reference Guide .

More table options

Calculated fields and totals rows

Adding calculated fields and totals rows to your table lets you perform calculations using your table data. A calculated field calculates data within one record, while a totals row performs a calculation on an entire field of data. Whenever you see a subtotal for one record, you are looking at a calculated field. Similarly, whenever you see a grand total at the bottom of a table, you're looking at a totals row.

Example of a calculated field and totals row in a table of orders

To learn how to create calculated fields and totals rows , review this lesson .

Challenge!

  1. Open our practice database .
  2. Open the Products Table and change the data type for the description field to Long Text .
  3. Open the Customers Table and add a new field that uses the Short Text data type. Name the new field Credit Card .
  4. Add the following Field Validation Rule to the Credit Card field: "VS" Or "MC" Or "AMX" .
  5. Set a field validation message for the same field. It should say Enter a valid Credit Card Type: Must be VS, MC, or AMX .
  6. Test your field validation rule by typing Visa into a cell in the Credit Card field. If you've added the rule correctly, a dialog box should appear with the message you added above.