Written by Dr. Preiser
1
CIS 3100 MS Access Assignment
Database Design and Implementation Project
Health Care Benefits Database for Irrigation Equipment Specialists Inc.
50 points
This assignment is worth 10% of your grade.
DUE DATE: See Course Schedule
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 this assignment 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.
Written by Dr. Preiser
2
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 this assignment.
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 Lynda.com (sign in with your CPP.edu email) to learn more about using MS Access.
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 This assignment 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
Written by Dr. Preiser
3
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)”.
Written by Dr. Preiser
4
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
50
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
50
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
4
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.
Written by Dr. Preiser
5
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
50
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
Written by Dr. Preiser
6
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.
Written by Dr. Preiser
7
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.
Written by Dr. Preiser
8
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
Written by Dr. Preiser
9
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 / ACCESS ASSIGNMENT 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.
Written by Dr. Preiser
10
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
“Employee Only” 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
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).
Insurance Name Insurance Comment Total Number of Employees
Written by Dr. Preiser
11
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 relabel 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
Written by Dr. Preiser
12
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. 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)
Written by Dr. Preiser
13
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)
All American Life Care
00002 Roach Marcie E
00005 Votaw Jaque S
00005 Votaw Jaque E
Best Health Care
00001 Rhames Sherman E
00001 Rhames Sherman C1
03 Slovacek Gordon E
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.
Written by Dr. Preiser
14
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.
Data below are for demonstration purposes only.
Exhibit 10. Employee Personalized Enrollment Report
Employee Personalized Enrollment
Thursday, May 31, 2012
Prepared for: Sherman Rhames Department Code: 01
Employee Identification Number:00001 Monthly Payroll Deduction: 92.33
Written by Dr. Preiser
15
Insurance Company Dependent Code Rate
Best Health Care E $0.00
Best Health Care C1 $92.33
Assignment Deliverables:
You will turn in one file for this assignment. 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 this assignment’s 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 his assignment (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 / ACCESS ASSIGNMENT (same folder you found these
instructions), 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.
Written by Dr. Preiser
16
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
populating tables Insurance and Dependent
with data
A: Data Entry forms: PopulateInsurance and
PopulateDependent
4
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
6
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.
4
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.
6
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.
9
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.
8
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.
8
Total: 50
