Keeping Information Accurate

Core Training for Microsoft Office Access 2003

Using Data Type Settings to Restrict Data

1	To back up the GardenCo database, click the File menu, and then click Back Up Database.
2	To specify the database name, in the Save Backup As dialog box, for the purposes of this exercise, press SPACEBAR, wait as the text is typed for you, and then click Save.
3	To reject the security warning, in the Security Warning message box, click Cancel.
4	To open the backup database, click the File menu, click Open, and then in the Open dialog box, double-click Field Test.
5	To acknowledge the security warning, in the Security Warning message box, click Open.
6	To create a new table in Design view, on the Objects bar, click Tables, and then double-click Create table in Design view.
7	To create a new field, press SPACEBAR, and the text will be typed for you in the first cell of the Field Name column.
8	To assign the Text data type for the TextField column, click the Data Type box.
9	To create the second field, click the cell in the second row of the Field Name column, press SPACEBAR, and the text will be typed for you.
10	To assign a data type for the NumberField row, click the Data Type box, click the list arrow, and then click Number.
11	To save the table, click the File menu, click Save, in the Save As dialog box, press SPACEBAR, wait as the text is typed for you, and then click OK.
12	To discard the creation of a primary key, in the Microsoft Office Access message box, click No.
13	To switch to Datasheet view, click the View menu, and then click Datasheet View.
14	To add the TextField value, press SPACEBAR, and the text will be typed for you.
15	To add the second value, drag to select the text in the NumberField cell, press SPACEBAR, wait as the text is typed for you, and then press ENTER.
16	To acknowledge the message, in the Microsoft Office Access message box, click OK.
17	To insert a valid value, drag to select the text in the NumberField cell, press SPACEBAR, and the text will be typed for you.
18	To add the third value, click the DateField cell, press SPACEBAR, wait as the text is typed for you, then press ENTER, and in the Microsoft Office Access message box, click OK.
19	To add the fourth value, drag to select the text in the CurrencyField cell, press SPACEBAR, wait as the text is typed for you, then press ENTER, and in the Microsoft Office Access message box, click OK.
20	To add the last value, click the BooleanField check box, and to switch to Design view, click the View menu, and then click Design View.
21	To change a property, click the TextField cell, in the Field Size property cell in the Field Properties section, drag to select the text, press SPACEBAR, and the text will be typed for you.
22	To select a property of the NumberField column, click the NumberField cell in the Field Name column, in the Field Properties section, click the Field Size property box, and then click the list arrow.
23	To change the property setting, in the list, click Byte, and then to switch to Datasheet view, click the View menu, and then click Datasheet View.
24	To save the table, in the Microsoft Office Access message box, click Yes, and to acknowledge the risk of losing the data, click Yes, and then to accept the deletion of the field contents, click Yes.
25	To close the table, click its Close button.

Using Input Masks to Restrict Data

1	To open the Field Property Test table in Design view, on the Database window toolbar, click Design.
2	To create a new field in the table, click the first empty cell in the Field Name column, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you.
3	To assign a data type for the PhoneField field, click the Data Type cell, and Text is set as the data type.
4	To set the input mask property for PhoneField, click the Input Mask cell in the Field Properties section, and then click the Browse button, as indicated.
5	To save the table, in the Input Mask Wizard message box, click Yes.
6	To use the Phone Number mask and to go to the second page of the wizard, click Next.
7	To change the area code of the Phone Number input mask from optional to required, double-click 999, press SPACEBAR, and the text will be typed for you.
8	To change the placeholder, click the Placeholder character arrow, click #, and then click Next.
9	To accept the storage of data without symbols, click Next, and then to complete the process of creating the input mask, click Finish.
10	To save the changes made to the table, on the Table Design toolbar, click the Save button, and to switch to Datasheet view, click the View menu, and then click Datasheet View.
11	To test the input mask, click the PhoneField cell of the first record, drag to select the text, press SPACEBAR, and the text will be typed for you.
12	To switch to Design view, click the View menu, and then click Design View.
13	To create another field in the table, click the first empty cell in the Field Name column, press SPACEBAR, and the text will be typed for you.
14	To assign a data type for the LastName field, click the Data Type cell, and Text is set as the data type.
15	To set the input mask property for the LastName column, click the Input Mask property cell in the Field Properties section, press SPACEBAR, and the text will be typed for you.
16	To save the changes made to the table, on the Table Design toolbar, click the Save button, and to switch to Datasheet view, click the View menu, and then click Datasheet View.
17	To test the input mask, click the LastName cell for the first record, press SPACEBAR, and the text will be typed for you.
18	To close the table, click its Close button.

Using Validation Rules to Restrict Data

1	To open the Field Property Test table in Design view, on the Database window toolbar, click Design.
2	To add a field validation rule to the PhoneField field, click the PhoneField row, and then in the Field Properties section, click the Validation Rule box.
3	For the purposes of this exercise, press SPACEBAR, and wait as the text is typed for you in the Validation Rule box, and then press ENTER.
4	To add the validation text, in the Validation Text box, press SPACEBAR, and the text will be typed for you.
5	To change the field caption, click the Caption box, press SPACEBAR, and the text will be typed for you.
6	To save the changes made to the table, on the Table Design toolbar, click the Save button, and in the Microsoft Office Access message box, click No.
7	To switch to Datasheet view, click the View menu, and then click Datasheet View.
8	To test the validation rule, drag to select the text in the Phone Number cell of the first record, press SPACEBAR, wait as the text is typed for you, and then press ENTER.
9	To acknowledge the validation text message, in the Microsoft Office Access message box, click OK.
10	To insert the proper value, double-click the area code in the Phone Number cell of the first record, press SPACEBAR, wait as the text is typed for you, and then press ENTER.
11	To switch to Design view and to create a table validation rule, click the View menu, and then click Design View.
12	To create another field in the table, click the first empty cell in the Field Name column, press SPACEBAR, and the text will be typed for you.
13	To assign a data type for the Date2 column, click the Data Type cell, click the list arrow, and then click Date/Time.
14	To add a table validation rule, click the View menu, click Properties, and in the Table Properties window, click the Validation Rule box, press SPACEBAR, and the text will be typed for you.
15	To add the validation text, click the Validation Text box, press SPACEBAR, and wait as the text is typed for you, and then to close the Table Properties window, click its Close button.
16	To save the changes made to the table, on the Table Design toolbar, click the Save button, and in the Microsoft Office Access message box, click No.
17	To switch to Datasheet view, click the View menu, and then click Datasheet View.
18	To test the validation rule, click the DateField cell in the second record, press SPACEBAR, and the text will be typed for you.
19	To insert a date value in the Date2 column, click the Date2 cell in the second record, press SPACEBAR, wait as the text is typed for you, and then press ENTER.
20	To acknowledge the validation text message, in the Microsoft Office Access message box, click OK.
21	To insert the proper value, drag to select the text in the Date2 cell of the second record, press SPACEBAR, and the text will be typed for you.
22	To close the table, click its Close button.

Using Lookup Lists to Restrict Data

1	To open the Field Property Test table in Design view, on the Database window toolbar, click Design.
2	To create a new field in the table, click the first empty cell in the Field Name column, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you.
3	To assign a data type for the Month field, click the Data Type cell, click the list arrow, and then click Lookup Wizard.
4	To fill the lookup column with values, in the Lookup Wizard, click I will type in the values that I want, and then click Next.
5	To provide values for the lookup column, click the Col1 box, press SPACEBAR, wait as the text is typed for you, and then click Next.
6	To complete the lookup list creation process, click Finish.
7	To view the Lookup information for the Month field, in the Field Properties section, click the Lookup tab.
8	To switch to Datasheet view, click the View menu, click Datasheet View, and then to save the changes, in the Microsoft Office Access message box, click Yes.
9	To select a value from the lookup list, click the Month field in the second record, click the list arrow, and then click February.
10	To switch to Design view, click the View menu, and then click Design View.
11	To change a property setting for the Month field, on the Lookup tab, click the Limit To List box, click the list arrow, and then click Yes.
12	To switch to Datasheet view, click the View menu, click Datasheet View, and then to save the changes, in the Microsoft Office Access message box, click Yes.
13	To insert a new value in the Month field for the first record, click the Month field, press SPACEBAR, wait as the text is typed for you, and then press ENTER.
14	To acknowledge the message, in the Microsoft Office Access message box, click OK, and to insert a proper value in the Month field, in the list, click July.
15	To switch to Design view, click the View menu, click Design View, and then to create a new field in the table, click the first empty cell in the Field Name column.
16	To continue, press SPACEBAR, wait as the text is typed for you, and to assign a data type for the new field, click the Data Type cell, click the list arrow, and then click Lookup Wizard.
17	To fill the lookup column with values, in the Lookup Wizard, click I will type in the values that I want, and then click Next.
18	To add a second column to the list, in the Number of columns box, press SPACEBAR, wait as the text is typed for you, and then click the Col1 box.
19	To provide values for the lookup columns, press SPACEBAR, wait as the text is typed for you in both the columns, and then click Next.
20	To accept the default selection of Col1 as the identifiable field containing the value to be used in your database, click Next.
21	To complete the lookup list creation process, click Finish.
22	To change a property setting for the Month2 column, on the Lookup tab, click the Limit To List box, click the list arrow, and then click Yes.
23	To switch to Datasheet view, click the View menu, click Datasheet View, and then to save the changes, in the Microsoft Office Access message box, click Yes.
24	To insert a new value in the Month2 field for the first record, click the Month2 cell, click the list arrow, and then click 1 January.
25	To hide the first column in the list, click the View menu, click Design View, click the Column Widths box, drag to select the text, press SPACEBAR, and the text will be typed for you.
26	To switch to Datasheet view, click the View menu, click Datasheet View, and then to save the changes, in the Microsoft Office Access message box, click Yes.
27	To insert a different value in the Month2 field for the first record, click the Month2 cell, click the list arrow, and then click February.
28	To close the table, click the File menu, click Close, and to close the database, click the File menu, and then click Close.

Updating Information in a Table

1	To create a query by using a wizard, on the Objects bar, click Queries, and then double-click Create query by using wizard.
2	On the Simple Query Wizard page, note that Table: Categories is selected under Tables/Queries, and to include fields from the Categories table, under Available Fields, double-click CategoryName.
3	To specify another table for creating the query, click the Tables/Queries list arrow, and then click Table: Products.
4	To include fields from the Products table, under Available Fields, double-click ProductName, double-click UnitPrice, and then click Next.
5	To accept creation of a detail query, click Next, and then to complete the query creation process, click Finish.
6	To switch to Design view, click the View menu, and then click Design View.
7	To define the selection criteria, click the Criteria cell in the CategoryName column, and then, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you.
8	To add to the existing criteria, click the or cell in the CategoryName column, press SPACEBAR, wait as the text is typed for you, and to run the query, click the Query menu, and then click Run.
9	To switch to Design view, click the View menu, click Design View, and to change the price for the selected records, click the Query menu, and then click Update Query.
10	To define a new expression, click the Update To cell in the UnitPrice column, press SPACEBAR, wait as the text is typed for you, and to run the query, click the Query menu, and then click Run.
11	To acknowledge the warning, in the Microsoft Office Access message box, click Yes, and to view the updated unit prices, click the View menu, and then click Datasheet View.
12	To close the query after saving the changes, click its Close button, and then in the Microsoft Office Access message box, click Yes.

Deleting Information from a Table

1	To create a new query in Design view, double-click Create query in Design view, and in the Show Table dialog box, double-click Products, and then click Close.
2	To include all the table fields in the query, in the Products field list, double-click *.
3	To copy a column to the design grid, in the Products field list, click the Down scroll arrow on the scroll box two times, until Discontinued displays, and then double-click Discontinued.
4	To convert the select query to a delete query, click the Query menu, and then click Delete Query.
5	To define the criteria, click the Criteria cell in the Discontinued column, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you.
6	To run the query, click the Query menu, click Run, and then to acknowledge the warning, in the Microsoft Office Access message box, click Yes.
7	To view the records after deletion, click the View menu, click Datasheet View, and then to close the query, click its Close button.
8	To save the changes, in the Microsoft Office Access message box, click Yes, and in the Save As dialog box, press SPACEBAR, wait as the text is typed for you in the Query Name box, and then click OK.
9	To close the database, click its Close button.