When you use the Table Wizard to create tables and populate them with the fields you specify, it sets a variety of properties for each field. These properties determine what data can be entered in a field and how the data will look on the screen.
The field properties set by Access are a good starting place, and most of them are probably fine as they are. However, suppose some of the properties don t meet your needs. You can change some of them without affecting the data stored in the table; others might affect the data, so it pays to be cautious about making drastic changes until you have some experience working with Access.
In this exercise, you will review and edit a few of the property settings for one of the tables in the GardenCo database.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Office 2003 SBS\CreateNew\Refine folder and can also be accessedby clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
In the database window, double-click Employees in the Tables pane to open the table in Datasheet view.
Your table window might be a different size than this one. Notice that any field name that is composed of two words (such as FirstName) has a space between the words, whereas the name you specified in the wizard had no space. Remember this when you are looking at the table later, in Design view.
As with other Microsoft Office applications, you can change the size of the window by moving the pointer to a corner and, when the pointer becomes a double- headed arrow, clicking and dragging to expand or reduce the size of the window.
On the toolbar, click the View button to display the table in Design view.
In Design view, the top portion of the window contains a list of the table s fields. The Field Name column contains the names you, or the wizard, specified when you created the table. Notice that there are no spaces in the names . The Data Type column specifies the type of data that the field can contain. The Description column can contain a description of the field.
You can use field names that include spaces, but this can affect how queries and modules have to be written, so it is best not to do so.
Notice the Primary Key icon to the left of the EmployeeID field. The value in the primary key field is used to uniquely identify each record; that is, no two records can have the same value in this field. You can take responsibility for entering this value, or you can let Access help you with this chore. When the data type of a field is set to AutoNumber, Access fills this field in every new record with the next available number.
If you no longer want the table to have a primary key, select the field designated as the primary key in the top portion of the window, and on the Edit menu, click Primary Key. If you want to assign a different field as the primary key, select that field, and click Primary Key on the Edit menu to toggle it off.
Click in the Data Type cell for the EmployeeID field ”the one with AutoNumber in it ”and then click the down arrow that appears.
The cell expands to show a list of all possible data types . Each data type cell contains this list, which you use to set the appropriate data type for each field. The data type setting restricts data entry to that specific type. If you try to enter data that is incompatible with that type, Access rejects it.
For a description of all the data types, search for the data type topic in Access online Help.
Press the [ESC] key to close the list without changing the data type.
At the bottom of the table window, click in each box in the Field Properties section.
The number of properties in the Field Properties section varies with each data type. For example, the AutoNumber data type has six properties, four of which have drop down lists from which you can select settings. As you click each property, a description of that property appears in the area on the right.
For more information about a particular property, click in its box, and press the [F1] key to see the pertinent Access online Help topic.
The Field Size property determines the size and type of value that can be entered in the field. For example, if this property is set to Long Integer, the field will accept entries from “2,147,483,648 to 2,147,483,647. If the data type is AutoNumber, the entries in this field will start with 1, so you could conceivably have over two billion employees before you outgrew this table.
The Increment setting for the New Values property specifies that Access should use the next available sequential number. The alternative (which you can see by expanding the list for this cell) is Random.
The Format property determines how data from the field is displayed on the screen and in print; it does not control how it is stored. Some data types have predefined formats, and you can also create custom formats.
Remember that when you displayed the table in Datasheet view, some of the field names had spaces in them? The way the field names are displayed in Datasheet view is controlled by the Caption property. If there is an entry for this property,it is used in place of the actual field name.
The Yes (No Duplicates) setting for the Indexed property indicates that the information in this field will be indexed for faster searching, and that duplicate values are not allowed. For the primary key field, this property is automatically set to Yes (No Duplicates), but a field can also be indexed without being a primary key.
New in Office 2003 ”Smart Tags The ability to apply a Smart Tag to a field is a new feature with Office 2003. See the sidebar at the end of this chapter for more information about Smart Tags.
With the EmployeeID field still selected (as indicated by the arrow in the row selector ), click in the Format box, and enter three zeros ( 000 ).
The ID number generated by Access will now be displayed as three digits.If the number isn t three digits long, it will be padded on the left with zeros.
Click the Photograph field, and change its data type from OLE Object to Text .
The Table Wizard included the Photograph field in this table and set this field s data type to OLE Object so that you can store a graphic in the field. But you will be storing the file name of a graphic, not the graphic itself, so Text is a more appropriate data type.
Click in the HomePhone field to display the field s properties.
The data type for the HomePhone field is Text, even though the data will be a string of numbers . Because this type of entry can also contain parentheses, dashes, and spaces and is not the type of number that you would use in a calculation, Text is the appropriate data type.
Looking at the Field Properties section for this field, you can see that fields with this data type have more properties than fields with the AutoNumber data type.
The Field Size property for a field with the Text data type determines the numberof characters that can be entered in the field. If you attempt to enter too many characters, Access displays a warning message, and you won t be able to leave the field until you reduce the number of characters to this many or fewer.
The Caption property is set to Home Phone. This name will be used at the top of the field s column in Datasheet view. The wizard supplies these descriptive names, but you can change them.
Click in the DateHired field to display the field s properties.
The Format property for this field is set to Short Date, which looks like this:4/21/2003. If a valid date is entered in just about any standard format, suchas 21 April 03, this property displays the date as 4/21/2003.
Exercises in this book that use the short date format assume that the year display is set to four digits (M/d/yyyy). This is set in the Regional and Language Settings dialog box in Microsoft Windows XP. To check or change this on your computer click Start, click Control Panel, click Date, Time, Language, and Regional Options, and then click the Regional and Language Options icon. The process is similar in earlier versions of Microsoft Windows, but some of the command names are a little different.
This field also has its Input Mask property set to 99/99/00;0. An input mask controls how data looks when you enter it and the format in which it is stored. Each 9 represents an optional numeral, and each 0 represents a required one. When you move to this field to enter a date in Datasheet view, you will see a mask that looks like this: __/__/__. The mask indicates that the date must be entered in the 4/21/01 format, but as soon as you press [ENTER] to move to the next field, the date will change to whatever format is specified by the Format property.
Another interesting property is Validation Rule. None of the wizard-generated tables use validation rules , because the rules are too specific to the data being entered to anticipate, but let s take a quick look at how they work.
Click in the Validation Rule box, and enter < Now() . Then click in the Validation Text box and type Date entered must be today or earlier.
A rule is created stating that the date entered must be before (less than) the current instant in time, as determined by the system clock of the computer where the database is stored. If you enter a date in the future, Access will not accept it and will display the validation text in an alert box.
The Format, Input Mask, and Validation Rule properties seem like great ways to be sure that only valid information is entered in your tables. But if you aren t careful, you can make data entry difficult and frustrating. Test your properties carefully before releasing your database for others to use.
Click the View button to return to Datasheet view, clicking Yes when prompted to save the table.
When you try to switch from Design view to Datasheet view after making changes (and sometimes even if you haven t made any changes), you are presented with an alert box stating that you must save the table. If you click No, you remain in Design view. If you click Yes, Access saves your changes and switches to Datasheet view. If you want to switch views without saving changes that you have made inadvertently, click No, and then click the table s Close button. When Access displays another alert box, click No to close the table without saving any changes.
Enter a future date in both the Birthdate and Date Hired fields.
The Birthdate field, which has no validation rule, accepts any date, but the Date Hired field won t accept a date beyond the one set on your computer.
Click OK to close the alert box, change the Date Hired value to a date in the past, and then click the Close button to close the Employees table.
In the database window, click Suppliers , and click the Design button to open the table in Design view.
Delete the Country/Region , PaymentTerms , EmailAddress , and Notes fields by clicking in the row selector and pressing the [DEL] key.
Access alerts you that deleting the Email Address field requires deleting the field and all its indexes. Click Yes. (You will see this alert again in step 17; click Yes each time to delete the fields.)
Close the Suppliers table, clicking Yes to save your changes.
Open the Customers table in Design view, and delete the following fields: CompanyName , CompanyOrDepartment , ContactTitle , Extension , FaxNumber , EmailAddress , and Notes .
Click in the CustomerID field, and change the Field Size property from 4 to 5 .
Change the following fields and their captions (note that there is no space in the first two new field names, but there is a space between the words in their captions):
Original field name
New field name
Close the Customers table, clicking Yes to save it.
CLOSE the GardenCo database.