Access 2010 Modifying Tables

Author: mety Nagm Labels::


Access 2010

Modifying Tables

Introduction

Access 2010
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 that your table data is correctly and consistently formatted by setting validation rulescharacter limits, and data types in your fields. Finally, we will direct you to additional options for doing simple math functions within your tables.

We will be showing you how to modify tables with examples from our sample bakery database. If you would like to follow along, download example and use it to follow the procedures demonstrated in this lesson.

Modifying Tables

Launch video!Watch the video (4:29). Need help?
In addition to making basic modifications to your tables, like addingand moving fields, you can also make more advanced modifications that let you set rules for your data. All of these changes can help make your tables even more useful.
Watch the video to learn about modifying tables.

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.

To Add a New Field to an Existing Table:

  1. Open the table, then click the header with the text Click to Add. If you already have many fields, you may have to scroll all the way to the right to see this.
    Preparing to add a new fieldPreparing to add a new field
  2. A drop-down menu will appear. Select the data type you'd like for the new field.
    • Text: The default option, and best for text. You should also choose it for numbers you don't plan to do math with, like postal codes and phone numbers.
    • Number: Best for numbers you might want to do calculations with, like quantities of an item ordered or sold.
    • Currency: Automatically formats numbers in the currency used in your region.
    • Date & Time: Allows you to choose a date from a pop-out calendar.
    • Yes/No: Inserts a checkbox into your field.
    • Memo: Ideal for large amounts of text, like product descriptions. You can format text entered in Memo fields.
    • Attachment: Allows you to attach files, like images.
    • Hyperlink: Creates a link out of web or email addresses.
    Selecting a data typeSelecting a data type
  3. Type a name for your field, then press the Enter key.
    Naming the new fieldNaming the new field

To Move a Field:

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

    Advanced Field Options

    On the last 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 are a way of guaranteeing that 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 what options you have for working with that data. For example, you can only do math with data entered in number or currency fields, and you can only format text entered in memo fields.
    There are three main types of rules you can set for a field: data typecharacter limit, and validation rules.

    To Change the Data Type for Existing Fields:

    1. Select the field whose data type you wish to change.
    2. On the Ribbon, select the Fields tab and locate the Formatting group. Click the Data Type drop-down arrow.
      The Data Type drop-down arrowThe Data Type drop-down arrow
    3. Select the desired data type.
      Selecting a new field data typeSelecting 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, since 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 in this field are now formatted as clickable hyperlinksAll of the entries in this field are now formatted as clickable hyperlinks
    You shouldn't change 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, even spaces-- can be entered in that field.  This can be useful to keep the data in your records concise, or even to force the user to enter the data a certain way.
    For instance, in the example below, the user is entering records which include addresses. If you set the character limit in the "state" field as "2," the user can only enter 2 characters of information. This means that he 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 in the Table Tools tab group, then locate the Properties group.
    3. In the box next to Field Size, type the maximum number of characters you would like to allow in your field.
      Setting a 2-character limit on the State fieldSetting a 2-character limit on the State field
    4. Save your table.

    Validation Rules

    validation rule is a rule that dictates what 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 that rule. For example, if we were asking the user to input a state name into a table with contact information, we might create a rule which 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 that 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 the user 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 official Microsoft office tutorial on creating validation rules.

    To Create a Validation Rule:

    1. Select the field you wish to add a validation rule to. For our example, we'll set a rule for the State field.
    2. On the Ribbon, select the Fields tab and locate the Field Validation group. Click the Validation  drop-down command and select Field Validation Rule.
      The Validation Rule commandThe Field Validation Rule command
    3. The Expression Builder dialog box will appear. Click the text box and type in 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"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
      The Field Validation Message commandThe Field Validation Message command
    6. The Enter Validation Message dialog box will appear. Type the phrase you would like to appear in an error message when a user tries to enter data that violates the validation rule. Your message should let the user know what data is permitted.
      Typing the phrase that will appear as an error message when someone attempts to break the validation ruleTyping the phrase that will appear as an error message when someone attempts to break the validation rule
    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 ruleData 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. Likewise, a grand total at the bottom of a table is really a totals row.
    Example of a calculated field and totals row in a table of ordersExample of a calculated field and totals row in a table of orders
    To learn how to create calculated fields and totals rows, review our mini-lesson located in our Extras section:How to Create Calculated Fields and Totals Rows in Tables.

    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. Likewise, a grand total at the bottom of a table is really a totals row.
    Example of a calculated field and totals row in a table of ordersExample of a calculated field and totals row in a table of orders
    To learn how to create calculated fields and totals rows, review our mini-lesson located in our Extras section:How to Create Calculated Fields and Totals Rows in Tables.

0 comments |

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2