Keeping Your Information Accurate

Core Training for Microsoft Access 2002

Restricting Data with the Data Type Setting

1	To create a new database and begin experimenting on how to restrict data entry into a table, in the New File taskpane, click Blank Database.
2	To select a path for the new database, in the File New Database dialog box, double-click Garden Company, and double-click Guests.
3	To assign a name to the new database, double-click db1 in the File name text box, type FieldTest, and click Create.
4	To maximize the database window, on the database titlebar, click the Maximize button (middle button).
5	To create a table for the new database, in the Tables pane, double-click Create table in Design view.
6	To define fields for the table, in the Table1 : Table window, in the first blank Field Name cell, type TextField, and press the TAB key.
7	The default data type, Text, is appropriate. To move to the second row, press the TAB key two times.
8	To define the second field for the table, in the second Field Name cell, type NumberField and press the TAB key.
9	To select a numeric Data Type for NumberField, and move to the third row, click the Data Type cell arrow, click Number, and press the TAB key two times.
10	To define another field for the table, in the Field Name cell, type DateField, and press the TAB key.
11	To assign a date or time Data Type to DateField, click the Data Type cell arrow, click Date/Time, and press the TAB key two times.
12	To save the table and assign a name to it, on the Standard toolbar, click Save, in the Save As dialog box, type Field Property Test, and then click OK.
13	This table will not require a Primary Key. To complete the procedure of saving the table, in the alert box that appears, click No.
14	To view the properties of a field, on the left side of the field name TextField, click the row selector.
15	To view the table in Datasheet view, click the View menu, and click Datasheet View.
16	To add data to a field, in the TextField cell, type This entry is 32 characters long, and press the TAB key.
17	To add data to the second field, type Five hundred, and press the TAB key.
18	Note that NumberField has a numeric data type, so Access prohibits the text entry. To continue, in the alert box, click OK.
19	To delete the text Five hundred, in the NumberField cell, point the mouse pointer on the left of the text Five, press and hold down the mouse button, drag the pointer to the right of the text Hundred, and press the DELETE key.
20	To add a numeric data and move to the next field, in the NumberField cell, type 500, and press the TAB key.
21	To verify the DateField field property, in the DateField cell, type 08182003, and press the TAB key.
22	The DateField is set to Date/Time data type, so Access rejects the data. To delete the DateField text, in the alert box that appears, click OK, and press the BACKSPACE key eight times.
23	To add a data that complies with the Date/Time data type and to move to the next field, in the DateField cell, type Aug. 18, and press the TAB key.
24	To add data to the CurrencyField field, in the CurrencyField cell, type 200 dollars, and press the TAB key.
25	Access rejects the data because the data type of CurrencyField is set to Currency. To enter a valid data, in the CurrencyField cell, in the alert box that appears, click OK, press the BACKSPACE key eleven times, type -45.3456, and press the TAB key.
26	To insert data in the BooleanField cell, click the BooleanField cell check box.
27	To close the table, in the upper-right corner of the window, click the lower of the two Close buttons.

Restricting Data with an Input Mask

1	To open the Field Property Test table and begin using an input mask, in the Tables pane, click Field Property Test, and on the Database toolbar, click Design.
2	To add a new field, in the Field Name column, click the blank cell below BooleanField, type PhoneField, press the TAB key, and to accept the default data type Text, press the TAB key again.
3	To make PhoneField the first field of the table, on the left side of the field name PhoneField, on the row selector, click once, press and hold down the mouse button, drag the pointer up until it reaches the first row, and release the mouse button.
4	To save the changes made in the table, on the Standard toolbar, click Save.
5	To use an Input Mask, at the lower portion of the table, in the Field Properties section, click the Input Mask property text box.
6	To use and edit an Input Mask with the help of a wizard, in the Field Properties section of the table, next to the Input Mask text box, click the ... button.
7	To apply the Phone Number Input Mask to PhoneField, in the Input Mask Wizard, click Next.
8	To require an area code for any phone number entry in the table, in the Input Mask Wizard, in the Input Mask text box, click the first 9 from the left, and type 000 (three zeros).
9	To change the placeholder character, in the Input Mask Wizard, click the arrow next to the Placeholder character: text box, click #, and click Next.
10	The default selection—Without the symbols in the mask like this: 4442315281—of how to store the data is fine. To move to the next step, click Next.
11	To finish editing the Input Mask property, click Finish.
12	To accept the Input Mask after viewing it and save the changes done in the table, press the ENTER key, and on the Standard toolbar, click Save.
13	To enter a record to the table in Datasheet view, on the Standard toolbar, click View.
14	To enter a new record and verify the Input Mask property, press the down arrow key, and type 1234567890a.
15	To add a new field in Design view and apply another input mask, on the Standard toolbar, click View.
16	To add a new field, in the Field Name column, below the field name BooleanField, click in the blank cell, type LastName, press the TAB key, and to accept the data type Text, press the TAB key again.
17	To start assigning an input mask to the LastName field, in the Field Name column, to the left of the field name LastName, click the row selector.
18	To specify the input mask, in the Field Properties section of the table, click in the Input Mask text box, and type >L<?.
19	To add a record in Datasheet view after saving the changes, on the Standard toolbar, click Save, and then click View.
20	To enter a new record and verify the Input Mask property, in the LastName column, move the mouse pointer to the left edge of the third blank cell from the top until the pointer changes to a plus sign, click the cell, and type smith.
21	To type in a name in a different format in the next blank LastName cell, move the mouse pointer to the left edge of the cell, until the pointer changes to a plus sign, click the cell, and type SMITH.
22	To type another name in a different format, move the mouse pointer to the left edge of the cell, until the pointer changes to a plus sign, click the cell, and type McDonald.
23	To close the table, in the upper-right corner of the window, click the lower of the two Close buttons.

Restricting Data with Validation Rules

1	To open the Field Property Test table in Design view and to begin using validation rules to restrict data, on the Database toolbar, click Design.
2	To add a validation rule to the PhoneField field of the table, in the Field Properties section, click in the Validation Rule text box.
3	To prevent the entry of an area code other than 206 or 425, in the Validation Rule text box, type *Like “206” Or Like “425”, and press the ENTER* key.
4	To display an alert message if an area code other than 206 or 425 is entered, in the Validation Text text box, type Area code.
5	To assign a new caption to the PhoneField field of the table, in the Field Properties section of the table, click the Caption text box, and type Phone Number.
6	To save the changes to the table, on the Standard toolbar, click Save, and after viewing the message box that appears, click No.
7	To view the table in Datasheet view, on the Standard toolbar, click View.
8	To enter a record and verify the Validation Rule setting, in the Phone Number column, in the first cell that displays a phone number, type 3605550009, and press the ENTER key.
9	To enter a new phone number with one of the allowed area codes, in the alert box that appears, click OK, press the BACKSPACE key ten times, type 4257079791, and press the ENTER key.
10	To return to Design view, on the Standard toolbar, click View.
11	To add a new field to the table, in the Field Name column, below the LastName field, click in the blank cell, type Date2, and press the TAB key.
12	To assign a data type to the Date2 field, click the arrow next to the Data Type cell, and click Date/Time.
13	To place the two date fields above each other, position the mouse pointer over the Date2 row selector, press and hold down the mouse button, drag the pointer up until it is on top of the field CurrencyField, and release the mouse button.
14	To start entering a validation rule, click the View menu, and click Properties.
15	To enter a validation rule and ensure that the date in the Date2 field is always a later date than in DateField, in the Table Properties dialog box, click the Validation Rule text box, and type [DateField]<[Date2].
16	To display an alert message if the Date2 date entered is not later than in DateField, click the Validation Text text box, and type Date2 must be later than DateField.
17	To close the Table Properties dialog box, click the Close button.
18	To save the changes done to the table, on the Standard toolbar, click Save, and in the message box that appears, click No.
19	To view the table in Datasheet view and enter a record, on the Standard toolbar, click View.
20	To enter a record and verify the Validation Rule setting, in the DateField column, click the first cell, type 08/18/03, press the TAB key, type 08/15/03, and click in the first cell of the second row.
21	To enter a new date in the Date2 field that complies with the Validation Rule setting, click OK in the alert box that appears, press the DELETE key nine times, and type 08/20/03.
22	To close the table, in the upper-right corner of the window, click the lower of the two Close buttons.

Restricting Data with a Lookup List

1	To start creating a lookup list of months, in the FieldName column, click the blank cell below the LastName field, type Month, and press the TAB key.
2	To use a wizard to create the lookup list, in the Data Type column, click the arrow next to the Data Type cell for the Month field, and click Lookup Wizard.
3	To add values for the lookup list manually, in the Lookup Wizard dialog box, click the I will type in the values that I want option, and click Next.
4	The default selection Number of columns: 1 is fine. To add the name of the months, in the Lookup Wizard dialog box, click the blank cell below Col1.
5	To add the name of the first month, type January, and press the TAB key.
6	To proceed to the next step, in the Lookup Wizard dialog box, click Next, and click Finish.
7	To view the Lookup information for the Month field, in the Field Properties section of the table, click the Lookup tab.
8	To allow entry of only the lookup list information, click the Limit To List property, click the arrow that appears, and click Yes.
9	To save the changes and view the table in Datasheet view, on the Standard toolbar, click Save, and then click View.
10	To view the Month field, click once to the right of the scroll box on the horizontal scroll bar.
11	To view the lookup list and add data, in the Month column, click the first blank cell, click the cell arrow that appears, and click February.
12	To verify the Lookup property, in the Month column, click the next blank cell, type jly, press the ENTER key, and in the alert box that appears, click OK.
13	To close the drop-down lookup list and remove the invalid data, press the ESC key, and press the BACKSPACE key three times.
14	To return to Design view and add a new field to the table, on the Standard toolbar, click View.
15	To set a mathematical way to deal with month names, in the Field Name column, click the blank cell below Month, type Month2, and press the TAB key.
16	To use a wizard to create the lookup list for the new Month2 field, in the Data Type column, click the cell arrow, and click Lookup Wizard.
17	To add values for the lookup list manually, in the Lookup Wizard dialog box, click the I will type in the values that I want option, and click Next.
18	To add a second column, in the Number of columns text box, type 2, and click the blank cell below Col1.
19	To add a serial number and the name of the first month, type 1, press the TAB key, and type January.
20	To move to the next step, on the lower portion of the Lookup Wizard dialog box, click Next.
21	The data from Col1 will be entered when a selection is made from the list. To accept the default selection and finish creating the lookup column, click Finish.
22	To allow entry of only the lookup list information, in the Field Properties section of the table, click the Limit To List property text box, click the arrow that appears, and click Yes.
23	To save your changes and return to Datasheet view, on the Standard toolbar, click Save, and then click View.
24	To view the Month2 column, on the lower portion of the table, click once to the right of the scroll box on the horizontal scroll bar.
25	To add a data and verify the Lookup property, click the first blank cell of the Month2 column, click the arrow that appears, and click January.
26	To return to Design view and edit the Lookup property, on the Standard toolbar, click View.
27	To hide the first column of the lookup list, in the Field Properties section of the table, click on the left end of the Column Widths property text box, press the DELETE key two times, type 0, and press the ENTER key.
28	To save the changes add data in Datasheet view, on the Standard toolbar, click Save, and then click View.
29	To view the Month2 column, click once to the right of the scroll box on the horizontal scroll bar.
30	To verify the Lookup property, in the Month2 column, click the blank cell below January, click the arrow that appears, and click February.

Deleting Records from a Table

1	To start creating a query from a table, in the GardenCo database, in the display pane, double-click Create query in Design view.
2	To specify a table to create a query, in the Show Table dialog box, on the Tables tab, double-click Products.
3	To close the Show Table dialog box, on the title bar of the dialog box, click the Close button.
4	To add all the fields of the Products table to the query, at the top of the Products list of fields, double-click the asterisk.
5	To copy a field to the second column of the design grid, in the Products list of fields, click two times below the scroll box on the vertical scroll bar, and double-click Discontinued.
6	To convert this select query to a delete query, click the Query menu, and click Delete Query.
7	To allow the deletion criteria to be performed, in the Discontinued column, click the blank cell below Where, and type Yes.
8	To return to Datasheet view and verify the query that has been created, on the Standard toolbar, click View.
9	To view the list of records that have check marks (indicating Yes, they will be deleted), on the lower portion of the table, click once to the right of the scroll box on the horizontal scroll bar.
10	To return to Design view and start deleting the records, on the Standard toolbar, click View.
11	To delete the records, on the Standard toolbar, click the Run button, in the alert message box that appears, click Yes, and in the second alert message box that appears, click Yes again.
12	To return to Datasheet view and view the two discontinued products that were not deleted, on the Standard toolbar, click View.
13	To save the query after assigning a name to it, on the Standard toolbar, click Save, in the Save As dialog box, type Deleted Discontinued Products, and click OK.
14	To close the query, in the upper-right corner of the window, click the lower of the two Close buttons.