8/12/2019 2nd Page MIS-she

1/5

Microsoft Access 2010: How to Modify Tables

Posted on May 31, 2012

by wubayou

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

As you work with Microsoft Access 2010, it is highly likely that you will run in to times where you needto 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 inwhich you can affect or control the type of data that enters your fields, which are data types, characterlimits, and validation rules. We will be taking a look at them today, so lets begin, shall we? Kee p inmind that for this tutorial we will be using our own sample table, but you can follow along to see howthe 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 Accesswindow. 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 datatype for things such as names or numbers that are not used in actual calculations, such as a phonenumber.

8/12/2019 2nd Page MIS-she

2/5

8/12/2019 2nd Page MIS-she

3/5

this keeps users from entering in the entire state name, which could cause uniformity issues later on, asAccess would classify NY, for example, as completely different from New York. This would affect suchtasks 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 Propertiessection. By default, Access gives us a field size of 255 characters. We do not want that for our Statefield, 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 maylose 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 sowould affect some of our tasks, and FX simply does not correspond to any actual state in the UnitedStates.

To prevent this, we can use validation. With the State field selected and the Fields tab selected, look forthe 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 ValidationRule 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 fieldsdata. In the box, we enter the two-letter abbreviations for the different states, such as AK, AL, and soon. Each abbreviation should be surrounded by quotation marks, and separated by the word Or.

8/12/2019 2nd Page MIS-she

4/5

Once all of the states are entered, we click OK. To complete validation, it is a good idea to add avalidation message. With the Fields tab selected, click on the Validation option once again. Select theField Validation Message option.

Adding this message means a prompt will appear each time a user enters an incorrect type of data. Youwant the message to be descriptive and helpful so that the user can understand what they need toenter. For our sample, we type in the validation message telling the user that their input must be of the2-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 thedata types, character limits, and adding validation. You wont need to do this for all of your fields, butadding the proper modifications can help ensure that your tables and databases feature uniformity andremain as organized as possible.

A database query is a piece of code (a query) that is sent to a database in order to get information backfrom the database. It is used as the way of retriving the information from database

A database "query" is basically a "question" that you ask the database. The results of the query is theinformation that is returned by the database management system. Queries are usually constructedusing SQL (structured query language) which resembles a high-level programming language.

The term 'query' means to search, to question, or to find. When you query a database, your searchingfor information in the database. Different query languages exist for different type of databases. MSAccess uses SQL, which stands for Structured Query Language. MS Access contains Tables, Forms, andQueries. The Forms are used to enter or display the data, the Tables are where the data is saved, andthe queries are used to search for specific data.

Introduction to queries

8/12/2019 2nd Page MIS-she

5/5

Queries is use to add, delete, change, or review data from our database.

Mainly queries is used to filter our data and to perform calculations with our data, and to summarize ourdata.

Queries is also use to automate data management tasks.

When we change our data before we commit to those changes.

Its can also use to supply data for a form or report.

A query is a request for data results, for action on data, or for both.

Queries is a process to assemble the data when we design our form or report.

When we want to represent our data by using a form or report is often located in several different