Power Access Techniques

Power Table Techniques

Working with Field Properties

After you have added fields to a table, you need to customize their properties. Field properties let you control how Access stores data as well as what data the user can enter into a field. The available properties differ depending on which field type you select. You can find a comprehensive list of properties under the Text data type. The following sections describe the various field properties. Notice that the lower portion of the Design view window is the Field Properties pane. This is where you can set properties for the fields in a table.

The Field Size Property: Limiting What the User Enters into a Field

The Field Size property is available for Text and Number fields only. It's best to set the Field Size property to the smallest value possible. For Number fields, a small size means lower storage requirements and faster performance. The same is true for Text fields. To modify the Field Size property, follow these steps:

1. Select the desired field name from the top pane of the Design view window.

2. Click the Field Size property text box in the Field Properties pane.

3. Type the desired field size.
   

It's important to note that for Number fields, you should select the smallest Field Size property value that can store the values you will be entering. Limiting the Field Size property of Number fields saves disk space.

Placeholders That Allow You to Build a Custom Format

Placeholder	Function
0	Displays a digit if one exists in the position; otherwise, displays a zero. You can use the 0 placeholder to display leading zeros for whole numbers and trailing zeros for decimals.
#	Displays a digit if one exists in the position; otherwise, displays a blank space.
$	Displays a dollar sign in the position.
. %,	Displays a decimal point, percent sign, or comma at the indicated position.
/	Separates the day, month, and year to format date values.
M	Used as a month placeholder: m displays 1, mm displays 01, mmm displays Jan, mmmm displays January.
D	Used as a day placeholder: d displays 1, dd displays 01, ddd displays Mon, dddd displays Monday.
Y	Used as a year placeholder: yy displays 95, yyyy displays 1995.
:	Separates hours and minutes.
h, n, s	Used as time placeholders for h hours, n minutes, and s seconds.
AM/PM	Displays time in 12-hour format, with AM or PM appended.
@	Indicates that a character is required in the position in a text or memo field.
&	Indicates that a character is optional.
>	Changes all the text characters to uppercase.
<	Changes all the text characters to lowercase.

Working with Table Properties

In addition to field properties, you can specify properties that apply to a table as a whole. To access the table properties, you click the Properties button on the toolbar while in a table's Design view.

The Description property is used mainly for documentation purposes. The Default View property designates the view in which the table appears when the user first opens it. The Validation Rule property specifies validations that must occur at a record level instead of a field level. For example, credit limits might differ depending on what state a customer is in. In that case, what's entered in one field depends on the value in another field. If you enter a table-level validation rule, it doesn't matter in what order the user enters the data. A table-level validation rule ensures that Access enforces the proper dependency between fields. The validation rule might look something like this:

[State] In ("CA","NY") And [CreditLimit]<=2500 Or _
   [State] In ("MA","AZ") And [CreditLimit]<=3500 Or _
   [State] Not In ("CA", "NY", "MA", "AZ")

This validation rule requires a credit limit of $2,500 or less for applicants in California and New York and a limit of $3,500 or less for applicants in Massachusetts and Arizona, but it doesn't specify a credit limit for residents of any other states. Table-level validation rules can't be in conflict with field-level validation rules. If they are in conflict, you will not be able to enter data into the table.

The Validation Text property determines what message appears when a user violates the validation rule. If this property is left blank, a default message appears.

The Filter property is used to indicate a subset of records that appears in a datasheet, form, or query. The Order By property is used to specify a default order for the records. The Filter and Order By properties aren't generally applied as properties of a table.

The Subdatasheet Name property identifies the name of a table that is used as a drill-down. If this property is set to [Auto], Access automatically detects the drill-down table, based on relationships established in the database. The Link Child Fields and Link Master Fields properties are implemented to designate the fields that are used to link the current table with the table specified in the Subdatasheet Name property. These properties should be left blank when you select [Auto] for the Subdatasheet Name. You use the Subdatasheet Height property to specify the maximum height of the subdatasheet and the Subdatasheet Expanded property to designate whether Access automatically displays the subdatasheet in an expanded state.

The Orientation property determines the layout direction for a table when it is displayed. The default setting for USA English is Left-to-Right. The Orientation property is language specific, and the Right-to-Left setting is available only if you are using a language version of Microsoft Access that supports right-to-left language displays. Arabic and Hebrew are examples of right-to-left languages. You must run a 32-bit Microsoft operating system that offers right-to-left support, such as the Arabic version of Windows 2000, to take advantage of this feature in Access. By installing the Microsoft Office Multilanguage Pack and the Microsoft Office Proofing Tools for a specific language, and by enabling the specific right-to-left language under the Microsoft Office language settings, you can also turn on right-to-left support.

Using Indexes to Improve Performance

Indexes improve performance when you're searching, sorting, or grouping on a field or fields. Primary key indexes are used to maintain unique values for records. For example, you can create a single-field index that does not allow a duplicate order number or a multiple-field index that does not allow records with the same first and last names.

To create an index based on a single field (from Design view), follow these steps:

1. Select the field to be indexed.

2. Click the Indexed row of the Field Properties pane.

3. Select the desired index type—No, Yes (Duplicates OK), or Yes (No Duplicates). The Yes (Duplicates OK) option means that you are creating an index and that you will allow duplicates within that field. The Yes (No Duplicates) option means that you are creating an index and you will not allow duplicate values within the index. If the index is based on company name and you select Yes (Duplicates OK), you can enter two companies with the same name. If you select Yes (No Duplicates), you cannot enter two companies with the same name.

To create an index based on multiple fields (from Design view), follow these steps:

1. Choose View | Indexes. The Indexes window appears.

2. Type the index name in the Index Name column.

3. From the Field Name column, select the desired fields to include in the index.

4. Select the desired index properties

5. Click OK to close the Indexes dialog box.

Combining Your Skills

It's time to combine your skills. This task takes you through the process of creating a new database and then adding a table to it. It walks you through setting several properties of the table and even adding data to it:

1. Not using a wizard, create a new database called MYDB.

2. Create a new table, called tblEmployees, in Design view. The tblEmployees table should have the structure shown in Table

   Field Name	Data Type	Size	Description
   EmpID	Autonumber	Long Integer	Unique number given each record
   LastName	Text	25	Last name of employee
   FirstName	Text	15	First name of employee
   Address	Text	20	Address of employee
   City	Text	15	City of employee
   State	Text	2	State of employee
   Zip	Text	5	Zip code of employee
   Phone	Text	12	Employee phone
   DateofHire	Date	N/A	Date of hire
   HourlyRate	Currency	N/A	Hourly rate
   Pension	Yes/No	N/A	Does employee have a company pension?

3. Set a primary key on the EmpID field.

4. Save the table as tblEmployees.

5. Add to the new table the records shown in Table

   Last Name	First Name	Address	City	State	Zip	Phone	Date of Hire	Hourly Rate	Pension
   Forman	Shell	123 Main St.	York	PA	17401	717-755-8976	1/1/1995	$125.00	Yes
   Terry	Sue	478 Creek View Dr.	Camp Hill	PA	17011	717-737-9087	6/15/2000	$180.00	No

6. Add appropriate captions for the EmpID, LastName, FirstName, DateofHire, and HourlyRate fields.

7. Format the HourlyRate field as Currency. Provide a default value of $125.00. Add a validation rule to ensure that HourlyRate is between 0 and $250.00. Add appropriate validation text.

8. Format the DateofHire field as Medium Date. Provide a default value of today's date. Add a validation rule to ensure that DateofHire is less than or equal to today's date. Add appropriate validation text.

9. Use the Input Mask Wizard to place a phone number input mask on the Phone field. Depending on the input mask that you select, you can provide a different look and feel for the phone number.

10. Create separate indexes for the FirstName, City, State, HourlyRate, and DateofHire fields.

11. Create a compound index that includes the LastName and FirstName fields. Call the index FullName.