Excel Project

sirleo

Assignment2WINTER20181.pdf

Home >Business & Finance homework help >Accounting homework help >Excel Project

CIS 310 – Winter 2018 Assignment 2 Database Design and Implementation Project

Health Care Benefits Database for Irrigation Equipment Specialists Inc. 50 points

Written by Dr. Preiser

DUE DATE: Due Friday 3/2, upload to Blackboard by 11:59 PM

Academic Integrity This is an individual assignment. Collaboration with another student is not

allowed. Any sharing of work or factual data between students constitutes academic dishonesty

as defined in the University Catalog. All work must be original for this project in this quarter.

You may not use work from other classes or courses to fulfill this assignment. Failure to adhere

to this requirement will result in an “F” grade for the class.

Project Objectives:

This project is designed to give you a better understanding of how data can be organized into a

relational database and then used to provide answers to management queries and to extract

information for reports. The software we will be using for Assignment 2 is Microsoft Access

2010, 2013 or 2016. If you use a Mac, you must save the file as a Windows Access file before

submittal.  Note: CPP email servers block Access files so don’t email files to instructor or TA.

Project Background:

Irrigation Equipment Specialists employs 100 employees who specialize in purchasing parts and

supplies (inbound logistics), shipping products to customers (outbound logistics), order

fulfillment (sales and marketing), product support (service), and the administrative functions of

information technology infrastructure / systems development, planning, research and

development, human resources, and financial management.

IES’s management recognizes that its employees have different health care needs, and thus,

provides them with a flexible benefits plan. For medical insurance, an employee must enroll

with either a preferred provider organization (PPO) or a health maintenance organization

(HMO). IES currently pays the monthly medical premiums for its employees. However, if an

employee enrolls in a dental or vision plan, the employee must pay a small monthly premium for

the optional plan. An employee may carry, medical, dental, and/or vision coverage for one or

more of its family members. In order to carry insurance on a family member, the employee must

carry the same coverage for himself/herself. For example, to carry a vision insurance on a child

(or a spouse), the employee must also carry vision insurance on himself/herself.

In the past, the Human Resources Department of IES administered health benefits enrollment

and analysis functions manually, with a minimal support of Excel spreadsheets and Word

documents. The manual process was time-consuming, error prone, and inefficient.

The health benefits enrollment process at IES will begin in several months, and Mr. David

Andrews, the Human Resource Director of IES, has hired you, as a database consultant, to

design, develop, and implement a relational database that will keep track of the health benefits

information on IES employees. Your consulting assignment consists of several tasks, including

the design, development, and implementation of a health benefits database for IES, using MS

Access software. You will also design, test and implement queries and report to assist Mr.

Andrews in the analysis of benefits enrollment information.

Follow the tasks listed below to complete the project.

 Note: If a particular style/ format / layout is listed as a requirement, and your

version of Access does not support it, pick a style or format from those available.

Project Tasks:

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

http://www.microsoft.com/

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)”.

Exhibit 2. Insurance Table Structure. Field Name Data Type Field Description Field Size Comments

insCode Text Stores the insurance company code 4 * Serves as Primary Key

* Field Property: required,

indexed (no duplicates)

insName Text Stores the insurance company name 50

insComments Text Stores a brief comment about the

type of insurance

Task 3B. Create table structure for Employee table.

Employee table contains data about the employees employed by IES. The structure of the

Employee table is listed in Exhibit 3. Use the information provided in Exhibit 3 to create the

structure of the Employee table. Set the field “eid” as a Primary Key of the table. Set the Field

Properties listed in the Comments column of Exhibit 3.

Exhibit 3. Employee Table Structure.

Field Name Data

Type

Field Description Field

Size

Comments

eid Number Stores the employee’s

identification number

Long

Integer

* Serves as Primary Key

* Field Property: required, indexed

(no duplicates)

* Field Property: set the format

property to 00000

deptId Number Stores the department code for

the department in which the

employee works

Long

Integer

* Field Property: required, indexed

(Duplicates OK)

* Field Property: set the format

property to 00

eFirstName Text Stores the employee’s first name 50

eLastName Text Stores the employee’s last name 50

streetAddress Text Stores the employee’s street

address

city Text Stores the employee’s city name 50

state Text Stores the employee’s state 2 * Field Property: default value “CA”

(note: the state abbreviation should

display in all caps. The default value

must be set to “CA”)

zipCode Text Stores the employee’s zip code 10

officeExt Text Stores the employee’s office

extension number

Task 3C. Create table structure for Dependent table.

The Dependent table contains data about the types of employee dependents (e.g., spouse and

children).The structure of the Dependent table is listed in Exhibit 4. Use the information

provided in Exhibit 4 to create the structure of the Dependent table. Set the field “depCode” as a

Primary Key of the table. Set the Field Properties listed in the Comments column of Exhibit 4.

Exhibit 4. Dependent Table Structure.

Field Name Data Type Field Description Field

Size

Comments

depCode Text Stores the

dependent code

3 * Serves as Primary Key

* Field Property: required, indexed (no

duplicates)

* Field Property: set the format property to >

(NOTE: 

You can create custom text formats by using the

following symbols.)

Symbol Description

> Forces all characters to uppercase

depDescription Text Stores the

description of the

dependent code

Task 3D. Create table structure for Rate table.

Rate table contains data on the current insurance rates for employee benefits. The structure of the

Rate table is listed in Exhibit 5. Use the information provided in Exhibit 5 to create the structure

of the Rate table. The Rate table has a combination key, consisting of the insCode and depCode

fields [note: insCode field is from the Insurance table, and depCode is from the Dependent table.

At this step, you just need to enter/create the fields for the table, and you will “link” two tables

(e.g., the Insurance and Rate tables) in Task 4.]. Thus, you will need to set both fields “insCode”

and “depCode” as a Primary Key of the Rate table [hint: in the Design View, use the mouse

pointer and hold down the “Ctrl” key to select both fields (i.e., horizontal rows) and click the

Primary Key button on the Ribbon; you should see a primary key symbol next to each of the two

fields). Set the Field Properties listed in the Comments column of Exhibit 5.

Exhibit 5 Rate Table Structure.

Field Name Data Type Field Description Field Size Comments

insCode Text Stores the insurance company

code

4 * Serves as part of the combination

key [note: this code is from the

Insurance table]

* Field Property: required, indexed

(Duplicates OK)

depCode Text Stores the dependent code 3 * Serves as part of the combination

key [note: this code is from the

Dependent table]

* Field Property: required, indexed

(Duplicates OK)

* Field Property: set the format

property to >

rate Currency Stores insurance rate amount * Field Property: default value 0

Task 3E. Create table structure for Enrollment table.

Enrollment table contains data on employee enrollments in the chosen insurance plans. The

structure of the Enrollment table is listed in Exhibit 6. Use the information provided in Exhibit 6

to create the structure of the Enrollment table. The Enrollment table has a combination key,

consisting of the eid, depCode and insCode. [note: eid is from the Employee table, depCode is

from the Dependent table and the insCode field is from the Insurance table. The order in which

these fields are set up in the Enrollment table is important – eid, depCode, insCode]. You will

need to set all three fields as a Primary Key of the Enrollment table [hint: in the Design View,

use the mouse pointer pointer and hold down the “Ctrl” key to select all three fields (i.e.,

horizontal rows) and click the Primary Key button on the Ribbon; you should see a primary key

symbol next to each of the three fields). Set the Field Properties listed in the Comments column

of Exhibit 6.

Exhibit 6 Enrollment Table Structure Field Name Data

Type

Field Description Field

Size

Comments

eid Number Stores the

employee’s

identification

number

Long

Integer

* Serves as part of the combination key [note: this

id is from the Employee table]

* Field Property: required, indexed (Duplicates

OK)

* Field Property: set the format property to 00000

depCode Text Stores the

dependent code

3 * Serves as part of the combination key [note: this

code is from the Dependent table]

* Field Property: required, indexed (Duplicates

OK)

* Field Property: set the format property to >

insCode Text Stores the

insurance

company code

4 * Serves as part of the combination key [note: this

code is from the Insurance table]

* Field Property: required, indexed (Duplicates

OK)

effectiveDate Date/Time Stores the date the

policy becomes

effective

Task4. Create relationships between the tables.

A database is a collection of related tables. In order to retrieve information from the database,

you must associate (or relate) different tables that contain the requisite information. Tables are

related via relationships, which are logical associations between the tables. For example, an

insurance company provides different rates for various plans and each rate is always associated

with one particular insurance company. This situation leads to an one-to-many relationship

between the Insurance table and the Rate table. [note: these two tables will be associated

through the common field, insCode, which is referred to as a Foreign Key in database design

terminology].

The purpose of Task 4 is to create five “one” to “many” relationships between the tables created

in Task 3.

To establish a relationship between the two tables, you click “Database Tools” menu item, then

click “Relationships” icon. In a pop window, hold down the “Ctrl” key and select all the five

tables (Insurance, Employee, Dependent, Rate, and Enrollment), and click the Add button, then

click the Close button. After you determine which field is common to both tables, click and drag

the field name from one table to the field name in the related table. The two field names of the

tables you are relating MUST have identical names.

Task 4A. Create relationship between Insurance and Rate tables.

Create a relationship between Insurance and Rate tables using insCode field. Drag and drop the

field insCode from the Insurance table directly onto the corresponding insCode in the Rate table.

The assumption is that “an insurance company can have many rates and each rate can be for one

company”. This implies a one-to-many relationship between “Insurance” and “Rate” tables,

which means that “1” symbol must be on the Insurance end of the table and the infinity symbol

(∞) must be on the Rate side of the table. Put a check mark next to the “Enforce Referential

Integrity” option in the Edit Relationship window. If you click “OK” before checking “Enforce

Referential Integrity” then you can always right click the relationship and select Edit

Relationships or you can delete the relationship and try again.

Task 4B. Create relationship between Dependent and Rate tables.

Create a relationship between Dependent and Rate tables using depCode field. The assumption is

that “a dependent can have many rates and each rate can be for one dependent”. This implies a

one-to-many relationship between “Dependent” and “Rate” table, which means that “1” symbol

must be on the Dependent end of the table and the infinity symbol must be on the Rate side of

the table. Put a check mark next to the “Enforce Referential Integrity” option in the Edit

Relationship window.

Task 4C. Create relationship between Employee and Enrollment tables.

Create a relationship between Employee and Enrollment tables using eid field. The assumption is

that “an employee can have many enrollments and each enrollment can be for one employee”.

This implies a one-to-many relationship between “Employee” and “Enrollment” table, which

means that “1” symbol must be on the Employee end of the table and the infinity symbol must be

on the Enrollment side of the table. Put a check mark next to the “Enforce Referential Integrity”

option in the Edit Relationship window.

Task 4D. Create relationship between Dependent and Enrollment tables.

Create a relationship between Dependent and Enrollment tables using depCode field. The

assumption is that “each employee can have many enrollments and each enrollment can be for

only one employee”. This implies a one-to-many relationship between “Dependent” and

“Enrollment” table, which means that “1” symbol must be on the Dependent end of the table and

the infinity symbol must be on the Enrollment side of the table. Put a check mark next to the

“Enforce Referential Integrity” option in the Edit Relationship window.

Task 4E. Create relationship between Insurance and Enrollment tables.

Create a relationship between Insurance and Enrollment tables using insCode field. The

assumption is that “each insurance company can have many enrollments and each enrollment can

be for only one insurance”. This implies a one-to-many relationship between “Insurance” and

“Enrollment” table, which means that “1” symbol must be on the Insurance end of the table and

the infinity symbol must be on the Enrollment side of the table. Put a check mark next to the

“Enforce Referential Integrity” option in the Edit Relationship window.

Task5. Import data into the database.

Note: if you cannot download/save the data files from Blackboard with Internet Explorer, try

using Firefox, as there may be a problem with Bb and your version of Internet Explorer.

Once the database structure is created, the next task is to import data into each table of the

database.

Task 5A. Populate Insurance table with data

Use the Form Wizard and follow steps 1 – 3 to create a data entry form to populate Insurance

table with data:

Step 1: click “Create” menu item

Step3: click Form Wizard

 Note: If a particular style/ format / layout is listed as a requirement, and your

version of Access does not support it, pick a style or format from those available.

In the popup window select the Insurance table from the drop down menu entitled

“Tables/Queries”. A list of available fields from the Insurance table will appear. Click “>>” (all)

button to select all fields. Use “Columnar” layout from the following window then click next.

Title the form PopulateInsurance. Once the form is created, use it to populate the Insurance table

with the data listed in Exhibit 7. Remember to hit enter after every row entry. Use the mouse to

select PopulateInsurance from the left pane, right click the mouse and click “Design View”, now

re-label the fields in the form detail section—only those on the left, as follows: Insurance Code,

Insurance Name, Insurance Comment. [note: insCode, insName, and insComments are the field

names in the Insurance table].

Exhibit 7. Insurance table data

insCode insName insComments

AALC All American Life Care PPO

BHC Best Health Care HMO

MD Midwest Dental Dental

PV Perfect Vision Vision

Task 5B. Populate Employee table with data

The data source for the Employee table is a text file. Download the input file employee.txt from

ASSIGNMENTS / ASSIGNMENT 2 in Blackboard. This file contains tab delimited data. Import

employee.txt into Employee table as follows:

 Close Employee table.

 Right click on Employee table name (under All Access Objects panel); select Import;

select text file.

 Specify the source of the data, where ever you saved employee.txt, and choose “Append

copy of the records to the table” option and select the correct table (i.e., Employee) to

import data in [note: data file is tab delimited and has no text qualifier]

Task 5C. Populate Dependent table with data

Use the Form Wizard to create a data entry form to populate Dependent table with data. Use

“Tabular” layout and “Module” style for the form. Title the form PopulateDependent. Once the

form is created, use it to populate the Dependent table with the data listed in Exhibit 8. Label the

fields on the form header portion, not the detail portion, as follows: Dependent Code,

Dependent Description. [note: depCode and depDescription are the field names in the Dependent

table].

Exhibit 8. Dependent table data

depCode depDescription

C1 Only One Child

C2 Two or More Children Only

E Employee Only

S Spouse Only

S1 Spouse and One Child

S2 Spouse and Two Or More Children

Task 5D. Populate Rate table with data

The data source for the Rate table is an Excel file. Download the input file rate.xls from

Blackboard. Import rate.xls into Rate table as follows:

 Close Rate table.

 Right click on Rate table name (under All Access Objects panel); select Import; select

Excel file.

 Specify the source of the data and choose “Append copy of the records to the table”

option and select the correct table, Rate, to import data in [note: first row of data file

contains column headings, which should not be imported as data].

Task 5E. Populate Enrollment table with data

The data source for the Enrollment table is a text file. Download the input file enrollment.txt

from Blackboard. This file contains comma delimited data. Import enrollment.txt into

Enrollment table as follows:

 Close Enrollment table.

 Right click on Enrollment table name (under All Access Objects panel); select Import;

select text file.

 Specify the source of the data and choose “Append copy of the records to the table”

option and select the correct table, Enrollment, to import data in [note: data file is comma

delimited and has no text qualifier]

Task6. Design and test three ad hoc queries to answer questions about IES’s employee

enrollments and benefits plans. Use Query Design feature of Access to design/test the queries.

General guidelines for designing ad hoc queries:

1) Review data and come up with expected results for the query (what should the content of

the output report look like?)

2) Identify table(s) to be joined for the query [hint: due to the nature of relational databases

you may need to include more tables in your query than only those from which data will

be eventually displayed in the query result]

3) Select columns to be displayed in the query result

4) (optional) Select additional columns which are not displayed in the result, but may be

used for totals or criteria values.

5) Specify criteria values and sorting options

6) Change column names under Caption in Property Sheet for each displayed column

7) Test the query and compare its result to the expected results derived in Step 1. If the

query results and expected results differ, modify the design of your query and re-test it.

Task 6A. Query1 – Employees Per Health Insurance

Mr. Andrews wants to know how many of the IES’s employees (note: only those with

“Employee Only “ ) are currently signed up for an HMO insurance plan? For PPO insurance

plan? The query result should include insName, insComments, and an aggregated employee total

column. The query should be sorted by insComments (ascending order). Create a query that

selects only the records that meet Mr. Andrews’ requests. Name the query – Query1EmplPerIns.

Label the columns of the query result as listed below. [hint: use Caption in Property Sheet to

label the output columns]

Select Query Design under the “Create” tab. Then identify the tables that will be joined by

holding down “Ctrl” and selecting the appropriate tables, then pressing “Add”: Dependent,

Enrollment, and Insurance. Link the tables as the diagram below indicates. Select the appropriate

columns as shown in the bottom half of the diagram. It’s important to note that the data will be

grouped by the insurance name and insurance comments. The selection criteria for insComments

is “HMO” or “PPO”. The total number of employees that have an HMO or PPO AND have a

“Spouse only” dependent description will be recorded in this column, an aggregation (i.e., the COUNT function) [note: you must click the “Totals” button in the design toolbar] for the Totals

row to appear]. The depDescription is an important part of the criteria, but as the columns of the

query listed above

Insurance Name Insurance Comment Total Number of Employees

indicate it is not necessary to list depDescription column in the final output, thus uncheck the

“Show” box. Make sure to re-label the columns of the query result to Insurance Name, Insurance

Comment, and Total Number of Employees (instead of insName, insComments, and eid).

Task 6B. Query2 – Employee Monthly Payroll Deductions

Mr. Andrews wants to know the monthly payroll deduction for each employee in Department 4.

That is, he is interested in the sum of insurance rates per employee in Department 4 . The query

result should include eid, eLastName, and a computed monthly payroll deduction columns. The

query should be sorted by monthly payroll deduction (descending order). Create a query that

selects only the records that meet Mr. Andrews’ request. The query should also display an

Average value of all of the IES’s employees deductions [hint: in Query Datasheet view (you can

switch to Datasheet View by right clicking mouse from Design View of the current query), use

the Totals function at the bottom of the results to compute the Average value for the Monthly

Payroll Deduction column). Name the query – Query2MonthlyPayrollDeductions. Be sure to re-

label the columns of the query result as they appear below. Numbers below are for

demonstration purposes only.

Employee Id Last Name Monthly Payroll Deduction

---- ----- ------

Total 999.99

Task 6C. Query3 – Employee Dependent Code per Insurance

Given the current benefits enrollment data for IES, Mr. Andrews wants to know the count of

dependent code by insurance company. More specifically, he is interested to review each

insurance company and the number of enrollment records for each dependent code associated

with that company. For example, in reviewing IES’s data, you can see that “Perfect Vision”

insurance company has one enrollment for “Spouse Only” dependent code, and eighteen

enrollments for “Employee Only” dependent code, etc.

The query result should include insName, depDescription, and computed dependent code count

columns. The query should be sorted by insName (ascending) and dependent code count

(descending order). The query should display a grand total showing the sum of all of the

dependent code counts. Create a query that selects only the records that meet Mr. Andrews’

request. Name the query – Query3DepCodeperInsurance.

NOTE: Numbers below are for demonstration purposes only.

Insurance Name Dependent Description Dependent Code Count

---- ----- ------

Total 999

Task7. Create a report for Mr. Andrews using Report Wizard feature of Access.

Task 7A. Report 1 – Employee by Insurance Report

Mr. Andrews requests that you create an Employee by Insurance report for him. This report

associates employees with their chosen health insurance carriers. It is sorted in ascending order

by insurance company and displays employee information by insurance. Exhibit 9 shows a

sample format of the report. For each employee, Mr. Andrews would like to see the employee’s

identification number, first and last name, and dependent code. As this report requires data from

multiple tables, you will need to first create a select query, and then, base the report on that

query. As part of the report’s header, you will include the report’s title (Employee by Insurance)

and current date. To complete Task 7A, follow the steps (a – d) listed below:

a) Use Query Design (located under Create tab…Other group) to create a query named

QueryEmployeeByInsurance. The query should capture the requisite information for the

report. Make sure to sort the query results in ascending order by insName field.

b) Use Report Wizard (located under Create tab…Reports group) to build the Employee by

Insurance report based on QueryEmployeeByInsurance you have just created.

a. Sort the Detail records by employee identification number (eid) field.

b. Select ‘Stepped’ layout (if available….otherwise choose another)and ‘Portrait’

orientation.

c. Use whichever style you’d like to.

d. Name the report – ReportEmployeeByInsurance.

e. Preview the report and then modify its appearance and content

c) Open the report in Design View.

a. Modify Report Header section

i. Change report title: Employees by Insurance

ii. Underneath the title, put a built-in function “=Date()” to display current

date (this function must go within a text box—not a label)

b. Modify Page Header section

i. Change column names to correspond to the report layout in Exhibit 9.

d) Save the report.

Data below are for demonstration purposes only.

Exhibit 9. Employee by Insurance Report (example of format, your data may be different)

Task 7B. Report 2 –Employee Personalized Enrollment Report  This final report will require research on your part. Please refer to online / help

resources before consulting with the TA.

Mr. Andrews also requests that you create an Employee Personalized Enrollment report, so that

he can distribute it to each employee during the benefits enrollment period. The report is sorted

in ascending order by employee identification number and displays employee information,

including employee’s chosen enrollments in medical/dental/vision plans. Exhibit 10 shows a

sample format of the report. For each employee, Mr. Andrews would like to see the employee’s

identification number, first and last name, department code, enrollment information and a

monthly payroll deduction, which is the sum of the employee’s enrollment rates. As this report

requires data from multiple tables, you will need to first create a select query, and then, base the

report on that query. As part of the report’s header, you will include the report’s title (Employee

Personalized Enrollment) and current date. To complete Task 7B following the steps (a-d) listed

below:

a) Use Query Design (under Create tab…Other group) to create a query named

QueryPersonalizedEnrollmentNew. The query should capture the requisite information

for the report. Make sure to sort the query results in ascending order by eid field.

b) Use Report Wizard (under Create tab…Reports group) to build the Personalized Employee Enrollment report based on QueryPersonalizedEnrollmentNew you have just created. Group report data by employee identification number (eid).

a. Name the report – ReportEmployeePersonalizedEnrollment. b. Modify report’s layout to match Exhibit 10.

Open the report in Design View.

i. Modify Page Header section 1. Change report title to Personalized Employee Enrollment and move to

Page Header section.

2. Underneath the title, put a built-in function Date() to display current date ii. Modify EID Header and Detail sections

1. Move the requisite fields from the detail section to the eid Header. To accomplish this, you can cut and paste the text boxes from the detail to the eid Header (be sure to cut those in the detail section—not the page header that they are linked to). The labels that these text boxes were linked to will then be gone.

2. Create new labels to replace the defaults that were deleted, then name and arrange everything according to what’s shown in Exhibit 10. Sizes of some text boxes may need to be adjusted. (note: you should not yet have a value for Monthly Payroll Deduction).

3. Use the Force New Page property (under Property Sheet of the EID Header) to cause each employee’s report to print on a new page.

iii. Add a calculated control for monthly payroll deduction to display the sum of employee’s rates listed on the report. A calculated control displays the results of an expression. An expression may include operators, object names, functions, literal values and constants. [note: from the Controls group located on the Design tab, click the Text Box button. Position the control on the report. Click inside the control, and type the expression to add the values of the rate field (i.e., =SUM(rate))

c) Check the Print Preview View to ensure everything works correctly. Then Save the

report.

Note: Data below are for demonstration purposes only.

Exhibit 10. Employee Personalized Enrollment Report

Employee Personalized Enrollment

Saturday, February 10, 2018

Prepared for: Mary Keleher

Employee Identification Number: 00001

Department Code: 01

Monthly Payroll Deduction: $ 94.75

Insurance Company Dependent Code Rate

Best Health Care E $0.00

Best Health Care C1 $94.75

Assignment 2 Deliverables:

You will turn in one file for Assignment 2. The file will be an electronic, working copy of your

database that meets the criteria specified in Tasks 1 - 8 (see above). The file will be in Access

2010, 2013 or 2016 format and will contain all Access objects in their final form for this

assignment. Access objects (in a database file) to be turned in for Assignment 2 deliverables are:

 Tables with data (tasks 2 – 5)

o Insurance, employee, dependent, rate, enrollment

 Forms

o PopulateInsurance and PopulateDependent (tasks 5A and 5C)

 Queries

o Query1EmplperIns (task 6A)

o Query2MonthlyPayrollDeductions (task 6B)

o Query3DepCodeperInsurance (task 6C)

o QueryEmployeesByInsurance (task 7A)

o QueryPersonalizedEnrollmentNew (task 7B)

 Report

o ReportEmployeesbyInsurance (task 7A)

o ReportPersonalizedEmployeeEnrollment (task 7B)

Rename the database file you created for Assignment 2 (i.e., XYHealth.accdb) to the

following naming convention: Bronco name, 2 (e.g., for instructor, it would be

dalopez2.accdb). Upload your file to Blackboard using the appropriate upload links provided

(in Blackboard / ASSIGNMENTS / ASSIGNMENT2 (same folder you found these instructions in), You will have two attempts to upload, just in case something goes wrong with the first attempt. If it uploads the first time, no need to do it again. If you submit twice prior to the

deadline, the second attempt will be graded. Again, do not email the file as the CPP email

servers block the Access attachments for security reasons.

Grading Rubric:

The following table lists the points for each item. All items must be included in the Access

database file submitted as deliverable for this assignment. If you don’t get full credit, look for

feedback in Blackboard based on the middle column letters, i.e. A(-2) refers to 2 points

subtracted from the data entry forms, etc.

Evidence Deliverable Item Points

Instructor will review data entry forms for A: Data Entry forms: PopulateInsurance and 4

populating tables Insurance and Dependent

with data

PopulateDependent

Instructor will review Data Sheet and Design

Views for ALL database tables: Insurance,

Employee, Dependent, Rate and Enrollment

B: Access objects (tables) with data: Insurance,

Employee, Dependent, Rate, Enrollment

Instructor will review relationships,

cardinality (e.g., one-to-many) and referential

integrity.

C: Relationships for all tables. 5

Instructor will evaluate the design and output

of Query 1: Query1EmplperIns

D: Query Datasheet and Design Views. Query

results will be evaluated for correct output.

Instructor will evaluate the design and output

of Query 2:

Query2MonthlyPayrollDeductions

E: Query Datasheet and Design Views. Query

results will be evaluated for correct output.

Instructor will evaluate the design and output

of Query 3: Query3DepCodeperInsurance

F: Query Datasheet and Design Views. Query

results will be evaluated for correct output.

Instructor will review Employees by

Insurance Report

(reportEmployeesbyInsurance) and its

associated query

(QueryEmployeesByInsurance)

G: Employees By Insurance Report.

Consistent, professional design is important to

have for this deliverable. Correct content of the

report is also important.

Instructor will review Employee Personalized

Enrollment Report

(reportEmployeePersonalizedEnrollment) and

its associated query

(QueryPersonalizedEnrollmentNew)

H: Employee Personalized Enrollment Report .

Review both Report View and Design Views.

Consistent, professional design is important to

have for this deliverable.

Total: 50