• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 3. The Table—The Heart of Any Database > Building an Access 2002 Table

Building an Access 2002 Table

The following steps lead you through building this table from scratch using different quality checks and data types:

Open the Claims.mdb database from your AccessByExample folder.

Under Objects, click Tables; then click New to open the New Table dialog box.

Click the second option, Design View, when the next window appears, and then click OK to open table Design view.

After typing ClaimNumber, tab to the Data Type column. Notice that a down arrow automatically appears. You can choose any of the data types after clicking the down arrow. In this case, accept the default.

Click the Primary Key button to make it a primary key and then click on Field Size in the lower part of the window (or press F6).

Set the field size to 14, and then click on the blank space in the Field Name column under ClaimNumber. Type DOL (date of loss) for the next field.

Tab to the Data Type column, but this time start typing the d in Date/Time and watch it appear. Accept this data type by tabbing to the Description column type “Date of Loss,” as shown in Figure 3.2.

Figure 3.2. In fields that have an acronym, you can type what the acronym stands for in the description field as a reminder.

Press F6 to enter the Field Properties section. Click the drop-down box in the Format field; then choose Short Date. Click the Validation Rule field in the Field Properties section.


A validation rule is used as a basis against which field data can be checked when the user leaves the field. This is one of the things that sets a database apart. It’s much easier to prevent the user from entering invalid data during data entry than to go back after the fact and correct every mistake.

Click the expression builder (…) and then type not > date() in the window. Click OK.

Click Validation Text, and type Enter a date that is equal to or less than today's date in the box, as shown in Figure 3.3.

Figure 3.3. You can use the Validate rule to be sure the input data is correct and the Validation text to notify the user what the proper value should be.

Press F6 or click to the blank row under DOL. Type Claimant in the Field Name column, and then cursor down to the next field. Notice that it automatically places a default value of Text data type with a 50-character field length (unless you have set another default field size in Tools, Options, Tables/Queries).

In the Field Name column under Claimant, type Address. Click the Required field in the Field Properties section. (The default field size will be entered.) Notice that a down arrow appears. Click the drop-down box and choose Yes. This means you cannot avoid entering information in this field.


Double-clicking also toggles the yes/no state.

Type City in the blank row below Address, and then cursor down to the blank row below. Type State, press F6, and change the Field Size to 2.

In the blank row below State, type Zip, and press F6 and change the Field Size to 10. Click the blank row below Zip, type Status. Press F6 to go the Field Size box and change it to 1.

Click the Default Value field and type O for open. This value is automatically inserted into a new record; however, it can be changed.

Type SSN in the blank row under Status. Click the Field Size box, and change it to 11.

In the Field Properties section, click the input mask and then on the ellipses (…) on the side. Click Yes when asked if you want to save the table first and save the table as Claims. The Input Mask Wizard opens. Click the Social Security option and try it with your own Social Security number, as shown in Figure 3.4.

Figure 3.4. The Input Mask Wizard enables you to try the mask while still in table Design view.

Click Next and then finish the wizard.

Under SSN, type ClaimDescription. Press Tab and change the Data Type to Memo.

Under ClaimDescription, type ClaimAmount. Press Tab and change the data type to Number; then click Field Size and change it to Double.

Under the Field Size box, click Format and change it to Standard.

Under the Format box, change Decimal Places to 0, and then click the Save button.

Click the View button. This is actually a short cut. Rather than having to Exit design view and open the table, this is a one-click procedure to prepare you to enter data.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint