Lab Instructions

Assignment:

In this lab you will work with the advanced features of Microsoft Access 2007. You will create tables and apply various field properties such as changing the format, applying Validation Rules and Validation Text, setting the Input Mask and entering the Lookup for specific fields. You will then enter the data provided to you and use the Table Analyzer to normalize the table data for the Department field as per the City. You will then save the query and run the query to view the output.

Create the Lab-01 Folder

Create the Lab-01 folder in the U:IT261 folder.

Show me step-by-step >>

Step 1: Create the Lab-01 folder in the U:IT261 folder. Perform the following actions:

a. Double-click the My Files shortcut on the desktop. The My Files (U:) window will open as shown in My Files (U:).

b. Double-click the IT261 folder. The IT261 window will display as shown in IT261.

c. Right-click in the details pane, point to New and select Folder from the menu. The IT261 window will display the new folder created as shown in IT261.

d. Overwrite New Folder with Lab-01 and press Enter. The new folder will be renamed as Lab-01 as shown in IT261.

Step 2: Minimize the IT261 window. Click the Minimize button in the top right corner of the IT261 window. The window will minimize and the desktop will display.

Create a Database named CEUEmployeeData

Use Microsoft Access 2007 to create a new database and save it as "CEUEmployeeData" in the U:IT261Lab-01 folder.

Show me step-by-step >>

Step 1: Start Microsoft Office Access 2007 CS4 to create a new database. Click Start » Microsoft Office Access 2007. Microsoft Access 2007 will open as shown in Microsoft Access.

Step 2: Create a database in the U:IT261Lab-01 folder and save it as "CEUEmployeeData". Perform the following actions:

a. Click the Office Button icon in the top left corner of the Microsoft Access window and select New from the menu. The Blank Database pane will open on the right as shown in Microsoft Access.

b. Click the Folder icon in front of the File Name: field. The File New Database dialog box will open as shown in File New Database.

c. Navigate to U:IT261Lab-01 folder, overwrite Database1.accdb with CEUEmployeeData and click OK. The File New Database dialog box will close and the Blank Database pane will display the path and the database name as shown in Microsoft Access.

d. Click Create. The CEUEmployeeData database will be created and the CEUEmployeeData: Database (Access 2007) - Microsoft Access window will open and display the Table1 table in Datasheet view as shown in CEUEmployeeData: Database (Access 2007) - Microsoft Access.

Create the Employeedata and Credits Tables and Define the Fields

Create the Employeedata table and add fields and data types to the table as given in the table below:

Field Name Data Type

EmployeeCode (PK) Number

Title Text

FirstName Text

LastName Text

EmailAddress Text

Department Text

School Text

Address Text

City Text

State Text

ZipCode Text

PhoneNo. Text.

HireDate Text

SalaryLevel Text

TeacherCertificateNumber Text

Endorsements Text

Create the Credits table and add fields and data types to the table as given in the table below:

Field Name Data Type

CreditId Number

EmployeeCode Number

CreditHours Text

Institution Text

CourseCode Text

CourseTitle Text

Show me step-by-step >>

Step 1: Create the Employeedata table. Perform the following actions:

a. Click the Office Button icon in the top left corner of the Microsoft Access window and select Save from the menu. The Save As dialog box will open as shown in Save As.

b. Overwrite Table1 in the Table Name: field with Employeedata and click OK

The Table1 table will be saved as Employeedata and the CEUEmployeeData: Database (Access 2007) - Microsoft Access window will display the Employeedata table in Datasheet view as shown in CEUEmployeeData: Database (Access 2007) - Microsoft Access.

Step 2: Change the format of the table to Design View. Click the drop-down arrow on the View button on the toolbar and select Design View from the menu. The Employeedata table will display as shown in CEUEmployeeData: Database (Access 2007) - Microsoft Access.

Tip: You can also right-click the Employeedata table in the All Tables pane on the left and select Design View from the menu.

Step 3: Enter the fields and data types in the Design view. Add the fields and the data types to the table in the Design view as shown below:

Field Name Data Type

EmployeeCode Number

Title Text

FirstName Text

LastName Text

EmailAddress Text

Department Text

School Text

Address Text

City Text

State Text

ZipCode Text

PhoneNo Text

HireDate Text

SalaryLevel Text

TeacherCertificateNumber Text

Endorsements Text

...