Access 2003 Personal Trainer (Personal Trainer (OReilly))

Lesson Summary

Understanding Field Properties

To Display a Table in Design View: Open the table and click the View button on the toolbar, or, in the Database window, click the Tables icon in the Objects bar, select the table, and click the Design button.

Indexing a Field

To Index a Field: Display the table in Design view. Click the field you want as an index, click the Indexed box, click the list arrow, and select one of the following: Yes (Duplicates OK) if you want to allow multiple records to have the same data in the field, or select Yes (No Duplicates) if you want to ensure that no two records have the same data in the field.

Adding a Primary Key to a Table

To Add a Primary Key to a Table: Display the table in Design view and click the field that you want to set as the primary key. If such a field doesn't exist, you will have to create it. Click the Primary Key button on the toolbar or right-click the field you want to use as the primary key and select Primary Key from the shortcut menu.

Inserting, Deleting, and Reordering Fields

To Insert a Row: Click the row selector for the field that will be below the new field you want to insert and press Insert.

To Change the Order of Fields in a Table: Click the row selector for the field you want to move and click and drag the selected row to the desired location.

To Delete a Field from a Table: Click the row selector for the field and press Delete.

Changing a Field's Data Type

To Change the Data Type for a Field: Display the table in Design view, click the field's Data Type box, click the list arrow, and select the data type.

Using Field Descriptions

To Add a Description to a Field: Display the table in Design view, click the field's Description box, and type the description.

Adding a Caption

To Add a Caption to a Field: Display the table in Design view, click the field you want to add a caption to, click the Caption box in the Field Properties section, and type the caption.

Changing the Field Size

To Change the Field Size: Display the table in Design view, click the field whose size you want to change, click the Field Size box in the Field Properties section, and either type the Field Size value (for text fields) or choose the value from the list (for number fields).

Formatting Number, Currency, and Date/Time Fields

To Format Number and Currency Fields: Display the table in Design view, click the field you want to format, click the Format box in the Field Properties section, click the list arrow, and select a number format.

To Change the Number of Decimal Places: Display the table in Design view, click the field you want to format, click the Decimal Places box in the Field Properties section, click the list arrow, and select the number of decimal places you want to display.

Formatting Number, Currency, and Date/Time Fields by Hand

To Manually Format a Date/Time, Number, or Currency Field: Display the table in Design view, click the field you want to format, click the Format box in the Field Properties section, and enter the appropriate formatting characters or symbols.

Formatting Text Fields

To Manually Format a Text Field: Display the table in Design view, click the text field you want to format, click the Format box in the Field Properties section, and enter the appropriate text formatting symbols.

Setting a Default Value

To Enter a Default Value for a Field: Display the table in Design view, click the field you want to add a default value to, click the Default Value box in the Field Properties section, and enter the default value you want to appear in the field for new records.

Requiring Data Entry

To Require Data Entry for a Field: Display the table in Design view, click the field you want to require data entry for, click the Required box in the Field Properties section, click the list arrow, and select Yes.

Validating Data

To Validate Field Data: Display the table in Design view, click the field you want to apply a validation rule to, click the Validation Rule box in the Field Properties section, and enter an expression you want to use to validate the field's data. Next, click the Validation Text box in the Field Properties section and type the text that Access will display when the user tries to enter incorrect data for the field.

Creating an Input Mask

To Create an Input Mask for a Field: Display the table in Design view, click the field you want to create an input mask for, and click the Input Mask box in the Field Properties section. Click the Build button to start the Input Wizard, then either select an input mask from the predefined list or manually create the input mask by entering the appropriate characters.

Creating a Lookup Field

To Create a Lookup Field: Display the table in Design view, click the field's Data Type box, click the list arrow, and select Lookup Wizard. Click the I want the lookup column to look up the values in a table or query option, click Next, then select the table or query you want to use for the look up list and click Next. Select the fields you want to add to the lookup field and click Next. Select a sort order for your list (optional) and click Next. Adjust the width of the columns that will appear in the lookup list and indicate whether or not to include the primary key in the list. If the table or query doesn't have a primary key, you will be prompted for the column that will act as the bound column. Click Next. Enter a label for the Lookup column, and click Finish.

Creating a Value List

To Create a Value List: Display the table in Design view, click the field's Data Type box, click the list arrow, and select Lookup Wizard. Click the I will type in the values that I want option and click Next. Specify the number of columns you want to appear in the value list, then enter the values in the list. Resize the column widths, if necessary, and click Next when you're finished. Enter a label for the Lookup column and click Finish.

Modifying a Lookup List

To Modify a Lookup List: Display the table in Design view, click the lookup list's field name box, then click the Lookup tab in the Field Properties section. Click the Row Source button to display the SQL Statement: Query Builder window, make the desired changes, and then close the SQL Statement: Query Builder window.

Quiz

1. Which of the following is NOT a field property?

   1. Field Size.

   2. Format.

   3. Color.

   4. Indexed.

2. Indexing a field dramatically speeds up queries and sorts performed on the field, therefore you should always index every field in a table. (True or False?)

3. Which of the following statements is NOT true?

   1. The Indexed property has three settings: No, Yes (Duplicates OK), and Yes (No Duplicates).

   2. Primary key fields are automatically indexed.

   3. The Yes (No Duplicates) index option prevents duplicate entries in your table.

   4. You can index any type of field: text, date/time, AutoNumber, number, currency, yes/no, memo, OLE object, and hyperlink fields.

4. Which of the following fields would NOT make a suitable primary key?

   1. An AutoNumber field.

   2. A customer's social security number.

   3. An invoice number.

   4. A date field.

5. Text entered in the field Description box will appear in a pop-up window whenever a user selects that field. (True or False?)

6. The Field Size property works differently, depending on whether the field is a text or number field. (True or False?)

7. Which of the following Format properties would display the full name of the month?

   1. MONTH.

   2. FULLMONTH.

   3. mm.

   4. mmmm.

8. Which of the following statements is NOT true?

   1. The Default Value property is automatically entered in a field when a new record is created.

   2. The Required property determines if a user must enter a value in a field or not.

   3. A lookup field lets you pick a field's entry from a list of values, which often comes from another table or query.

   4. (__ _) __ _ - ____ _ is an example of a Required property.

9. What does adding a > into the Format box of a text field do?

   1. Requires all characters entered in the field to be in uppercase.

   2. Displays the characters in the field in uppercase.

   3. Requires all characters entered in the field to be numbers.

   4. Displays the characters in the field in a larger font.

10. Which of the following statements is NOT true?

    1. The Default Value box lets you specify a value that is automatically entered in a field when a new record is created.

    2. The Required box lets you specify if data entry is required for a field.

    3. The maximum length of a memo field is 255 characters.

    4. The Validation Rule box lets you test data to make sure that it conforms to what you want to appear in the table. For example, entering 100 would require that a number be less than 100.

11. You want to create a field that lets you add a customer's name by picking it from a drop-down list. Which of the following fields would let you do this?

    1. A memo field.

    2. A lookup field.

    3. An OLE field.

    4. A hyperlink field.

12. What is the corresponding text for the <#1/1/95# Validation Rule Setting?

    1. Enter a date before 1995.

    2. Enter a value less than 1,195.

    3. Enter a value greater than 1,195.

    4. Value must be less than 95 characters.

13. You can set a field as the primary key by selecting the field and clicking the Primary Key button on the toolbar. (True or False?)

Homework

1. Open the Homework database.

2. Open the Customers table in Design View.

3. Limit the Field Size of the LastName and FirstName fields to 15 characters.

4. Specify a format for the State field that will display all entries in uppercase.

5. Create an Input Mask for the SSN field so that users must enter information in a __ _-_ _-__ _ format and make the SSN field a required field.

6. Index both the LastName and FirstName fields (duplicates OK).

7. Make the SSN field the table's primary key.

8. Save your changes to the Customers table and close the Homework database.

Quiz Answers

1. C. There are Color properties for other database objects, but not for fields in a table.

2. False. Only index those fields that you frequently use to filter or sort information. Indexing too many fields in a table slows down filters and sorts, defeating the whole idea behind the index property.

3. D. This was a tricky questionyou can't index memo, OLE object, or hyperlink fields.

4. D. Because the date isn't usually a unique value (the same date might appear more than once in the same table), it wouldn't normally make a good candidate for a table's primary key.

5. False. Text in the field Description box will appear in the Status bar when a user selects that field.

6. True. Text fields and Number/Currency fields have a different set of Field Size properties.

7. D. Another difficult question"mmmm" would display the full name of the month.

8. D. (__ _) __ _ - ____ _ is a example of an Input Mask property, not a Required property.

9. B. Adding a to a text field's Format box displays all characters in uppercase.

10. C. Memo fields have a maximum length of 64,000 characters.

11. B. A lookup field lets you select a value from a drop-down list.

12. A.

13. True. This is the procedure for setting a field as a table's primary key.