Introduction

After working with your database, you could find the you should make some alters to the tables that save your data. Access makes it easy to modify her tables to suit her database's needs.

You are watching: Validation rules are rules that a user must follow when entering the data.

In this lesson, you'll learn just how to create and rearrange table fields. You'll additionally learn how to ensure her table data is correctly and also consistently formatted by setting validation rules, character limits, and data types in your fields. Finally, us will direct you to extr options because that performing simple math features within her tables.

Throughout this tutorial, we will certainly be making use of a sample database. If friend would favor to follow along, you'll need to download our accessibility sample database. Girlfriend will need to have accessibility installed on your computer system in bespeak to open up the example.

Watch the video below to learn an ext about editing and enhancing tables.


Modifying tables

In enhancement to making simple modifications to her tables, like adding and moving fields, you have the right to make more advanced changes that let you collection rules for her data. All of these changes can assist make her tables even much more useful.

Adding and rearranging fields

Access makes it straightforward to rearrange currently fields and also add brand-new ones. As soon as you add a new field, you can even set the data type, i beg your pardon dictates which type that data have the right to be gone into into the field.

There room several species of fields you can add to a table:

Short Text: This is the default option and also is finest for many text in Access. You should also choose it because that numbers friend don't plan to execute math with, choose postal codes and also phone numbers.Number: This is finest for numbers you might want to perform calculations with, like quantities of an object ordered or sold.Currency: This automatically formats numbers in the money used in your region.Date & Time: This enables you to pick a date from a pop-out calendar.Yes/No: This inserts a checkbox into your field.Rich Text: This allows you to add formatting come text, like bold and italics.Long Text: This is right for huge amounts that text, choose product descriptions.Attachment: This permits you to affix files, prefer images.Hyperlink: This create a connect to a URL or email address.To include a new field to an existing table:Open the wanted table, then click the header v the text Click come Add. If you already have several fields, you may need to scroll all the means to the best to watch this option.
*

*

*

To move a field:Locate the field you want to move, then hover your computer mouse over the bottom border of the field header. The cursor will end up being a four-sided arrow.
*

*

Advanced ar options

On the vault page, friend learned about setup the data type for brand-new fields. When you collection field data type, you are really setting a rule for the field. Databases often incorporate rules due to the fact that they assist ensure users get in the correct kind of data.

Why is this important? computer systems aren't together smart as people about specific things. If you might recognize that two and also 2 or NC and also North Carolina are the exact same thing, accessibility will not and therefore won't team these points together. Making certain to enter your data in a standard style will assist you much better organize, count, and understand it.

Rules can additionally determine which options you have actually for working through your data. Because that example, you have the right to only do math through data gone into in number or currency fields, and also you have the right to only format text entered into text fields.

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

To readjust the data form for present fields:Select the ar with the data kind you desire to change.Select the Fields tab, then find the Formatting group. Click the Data Type drop-down arrow.
The ar data form will it is in changed. Depending upon the data type you chose, friend may notification changes to her information. For instance, because we set the data form for the Email field to Hyperlink, every one of the email addresses in the ar are now clickable links.

You shouldn't adjust the ar data kind unless friend are specific your ar data is in the correct style for the new data type. An altering a ar containing just text to the Number type, for instance, will delete every one of your ar data. This procaltoalsimce.orgre is regularly irreversible.

Field personality limits

Setting the personality limit for a ar sets a rule about how numerous characters—letters, numbers, punctuation, and even spaces—can be gotten in into that field. This can aid to keep the data in your records concise and even force users to go into data a details way.

In the example below, a user is entering records that include addresses. If you set the character limit in the State ar to 2, users deserve to only go into two characters the information. This means they must enter postal abbreviations for the states rather of the complete name—here, NC instead of north Carolina. Keep in mind that you deserve to only collection a character limit for fields defined as text.

To collection a character border for a field:Select the wanted field.Click the Fields tab, then locate the Properties group.In the Field size box, form the maximum variety of characters you desire to enable in her field.
Save her table. The character limit for the field will be set.Validation rules

A validation rule is a dominance that dictates which information can be entered into a field. When a validation rule is in place, that is impossible for a user to enter data that violates the rule. Because that example, if us were asking users to entry a state name right into a table with call information, we could create a ascendancy that limits the valid responses come U.S. State postal codes. This would protect against users from inputting something the wasn't actually a real state postal code.

In the example below, we will use this preeminence to ours Customers table. It's a fairly simple validation rule. We'll just name all of the valid responses a user can enter, which will mean users can't kind anything else right into the record. However, it's possible to produce validation rule that room much much more complex. For in-depth information on just how to write validation rules, testimonial this tutorial from Microsoft on developing validation rules.

To develop a validation rule:Select the field where you want to add a validation rule. In our example, we'll collection a dominance for the State field.Select the Fields tab, then locate the Field Validation group. Click the Validation drop-down command, then pick Field Validation Rule.
The Expression Builder dialog box will certainly appear. Click the text box and form your validation rule. In our example, we desire to limit data in the State ar to really state postal codes. We'll form each of the valid responses in quotation marks and separate castle with the word Or, i beg your pardon lets access know that this ar can accept the solution "AL" Or "AK" Or "AZ" or any kind of of the various other terms we've entered.
Once you're satisfied with the validation rule, click OK. The dialog box will close.Click the Validation drop-down command again. This time, pick Field Validation Message.
A dialog box will appear. Kind the expression you want to appear in an error message when users shot to get in data the violates the validation rule. Her message have to let them recognize what data is permitted.
When you're satisfied with the error message, click OK.The validation dominion is now included in the field. Customers will be can not to go into data the violates the rule.

Simple validation rules can be written specifically like query criteria. The only difference is the query criteria search for data, if an identical validation rule either permits or rejects data. Come see instances of questions criteria, review our query Criteria rapid Reference Guide.

More table options

Calculated fields and also totals rows

Adding calculation fields and also totals rows to your table lets you perform calculations utilizing your table data. A calculated field calculates data in ~ one record, when a totals row performs a calculate on whole field the data. Anytime you see a subtotal because that one record, you room looking in ~ a calculation field. Similarly, anytime you watch a grand total at the bottom the a table, you're looking in ~ a totals row.


To learn exactly how to develop calculated areas and totals rows, testimonial this lesson.

Challenge!

Open the Products Table and adjust the data type for the description field come Long Text.Open the Customers Table and add a new field that supplies the Short Text data type. Name the new field Credit Card.Add the adhering to Field Validation Rule come the credit Card field: "VS" Or "MC" Or "AMX".Set a ar validation message for the same field. It should say Enter a valid credit Card Type: have to be VS, MC, or AMX.

See more: How Many Calories In Double Quarter Pounder With Cheese Meal

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 blog post you added above.