Excel Project

Task1. Preparing for Assignment 2. 

Prior to starting on this assignment, it is recommended that students complete online tutorials (www.microsoft.com and search for Access) to learn the essential features of MS Access. Refer to instructions in Bb/Assignment2 to view Lynda.com material re: Access if needed. 

Task2. Create an empty database file. 

•   Launch MS Access
   
•   Create a new (blank) database file and name the file XYHealth (where “X” is the first
  initial of your first name, and “Y” is the first initial of your last name). [for example: a
  database file for Alexander Smith will be named ASHealth).
   
•   Save the database file to your hard (or flash) drive. Note that when Access saves the file,
  it adds an extension .aacdb (depending on the version, i.e. for 2010, the extension would be accdb). Thus, the full name for your database file will be – XYHealth.accdb (where “X” and “Y” are your initials as described above.
  Task3. Create the database table structure.
  A database is a collection of related tables. Table structure includes horizontal rows (records) and vertical columns (fields or attributes that describe the type of data stored in the column; for example, data can be stored as text or number or date/time)).
  The purpose of Task 3 is to create table structure for five tables that together will comprise your Benefits database. The description of the database tables required for Assignment 2 is listed in Exhibit 1.
  Exhibit 1. Tables for Benefits Database.
   

Table Name 

Table Description 

Insurance 

o Contains data about the insurance companies contracted by IES 

Employee 

o Contains data about the employees employed by IES 

Dependent 

o Contains data about the employee dependents (e.g., spouse and children) 

Rate 

o Contains data on the current insurance rates for employee benefits 

Enrollment 

o Contains data on employee enrollments in the chosen insurance plans 

2 

Task 3A. Create table structure for Insurance table. 

Insurance table contains data about the insurance companies contracted by IES to provide health care benefits to its employees. As shown in Exhibit 2, the Insurance table structure consists of three fields each of which has a name (the name of the table column), data type (the type of data the column can hold – e.g., text or numeric data), description (the description of the table column), size (the size of the table column), comments (various comments about the column property and uniqueness). 

Follow steps (1 through 5) to create the structure of the Insurance table:
Step 1: Click “Create” menu item.
Step 2: Click “Table Design” icon.
Step 3: According to Exhibit 2, for each field, enter its name, data type, and description. 

Step 4: Set the field “insCode” as a Primary Key of the table. The primary key can be set by having the cursor placed on the desired field, i.e. clicking on the field item, and then selecting the “Primary Key” icon from the design tab or by right-clicking on the field and selecting “Primary Key”. A key icon will now be visible to the left of your field name. If you accidentally selected the wrong primary key, follow the same procedure to select a different primary key. 

Step 5: Set the Field Properties according to the Comments column of Exhibit 2. The Field Properties are located just below the area where the field name, data type, and description were set. Each field has its own corresponding set of properties. For example selecting “insCode” displays the Field Properties for that field. Continuing with the example, the Field Size for “insCode” should be set to 4, Required should be set to “Yes” from the drop down box, and Indexed should be set to “Yes (No Duplicates)”.