Microsoft Access 2010: How to Modify Tables

This tutorial will teach you various ways in which you can modify fields in your tables in Microsoft Access 2010.

As you work with Microsoft Access 2010, it is highly likely that you will run in to times where you need to modify the fields contained within your tables.  Luckily, this is a task that is not hard to accomplish, and this tutorial will teach you how to do so.

Before you begin modifying tables, you should be aware that there are basically three different ways in which you can affect or control the type of data that enters your fields, which are data types, character limits, and validation rules.  We will be taking a look at them today, so let’s begin, shall we?  Keep in mind that for this tutorial we will be using our own sample table, but you can follow along to see how the techniques are applied and then implement them on your own sample in Access 2010.

To begin, we must first open a table, which is done by selecting a table from the left pane of our Access window.  We are going to start by using the Customers table from our sample database.

First, we scroll to the far right end of our table until we see the blank field labeled Click to Add in italics.  Clicking on this field brings up a menu that lists various data types.

It is no surprise that from this list of data types, the first option, Text, is the default.  We use this data type for things such as names or numbers that are not used in actual calculations, such as a phone number.

If you want your field to contain numbers that will be used for calculations, the Number data type should be your choice.  The Currency data type option refers to money, while Date & Time is pretty self explanatory.  For our sample, we are going to choose the data type option of Yes/No, which gives us a checkbox when selected.
Clicking on the Yes/No data type option adds a column or field labeled Field1 that contains checkboxes for each of our records.

Instead of using Field1 for our field name, we click in the box to change the name to Add to Mailing List.  To make the entire field name visible, resize it by clicking on its right border and dragging it to the right.  By adding this Yes/No field, we now have the option to check off customers that want to be added to our mailing list.

Let’s say you want to change a field’s data type.  To do so, select the field you want to change, click on the Fields tab at the top of the program window, and change the data type via its drop down menu within the formatting section.

To experiment, let’s change the data type for our Add to Mailing List field to Text.  This will allow us to add descriptive text as needed to each record, and gives us a Yes/No answer instead of a checkbox.  For instance, some customers may want a different type of newsletter, so this allows us to specify their preferences in the field.

We just worked on changing a field’s data type.  Now it is time to move on to the topic of character limits.  For our example, we want our State field to be limited to just two characters.  Using this abbreviated method looks cleaner than having each state spelled out entirely.  By imposing such a limit, this keeps users from entering in the entire state name, which could cause uniformity issues later on, as Access would classify NY, for example, as completely different from New York.  This would affect such tasks as sorting or filtering.

To change our character limit, go to the Fields tab up top and look for the Field Size box in the Properties section.  By default, Access gives us a field size of 255 characters.  We do not want that for our State field, so we change it to 2 instead.

A warning may pop up saying that data may be lost.  Click Yes to accept the change in field size.  It should be noted that when changing data types, character limits, etc. there is a chance that you may lose some data, so make these changes only when absolutely necessary to save any headaches.

The final way in which we will modify fields in our table is through the application of a validation rule.  Think about our State field for a minute.  What if someone enters FX instead of FL for Florida?  Doing so would affect some of our tasks, and FX simply does not correspond to any actual state in the United States.

To prevent this, we can use validation.  With the State field selected and the Fields tab selected, look for the Validation option up top in the Field Validation section.

Clicking on this option gives us a menu of different validation options.  We select the Field Validation Rule option, which restricts the values that can be entered by users into the field.

The Expression Builder window appears, asking us to enter an expression to validate the State field’s data.  In the box, we enter the two-letter abbreviations for the different states, such as AK, AL, and so on.  Each abbreviation should be surrounded by quotation marks, and separated by the word Or.

Once all of the states are entered, we click OK.  To complete validation, it is a good idea to add a validation message.  With the Fields tab selected, click on the Validation option once again.  Select the Field Validation Message option.
Adding this message means a prompt will appear each time a user enters an incorrect type of data.  You want the message to be descriptive and helpful so that the user can understand what they need to enter.  For our sample, we type in the validation message telling the user that their input must be of the 2-letter abbreviation of a US state.

We just showed you how we can modify our tables to control the data that is entered by tweaking the data types, character limits, and adding validation.  You won’t need to do this for all of your fields, but adding the proper modifications can help ensure that your tables and databases feature uniformity and remain as organized as possible.

Be sure to visit us again for more Microsoft Access 2010 tutorials in the future.

[gp-comments width="770" linklove="off" ]