2023 CIS 310 Database Design and Implementation Project Health Care Benefits Database for Irrigation Equipment Specialists Inc Academic Integrity This | Assignment Collections

 
CIS 310 
Database Design and Implementation Project
Health Care Benefits Database for Irrigation Equipment Specialists Inc.
 
 
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 3 is Microsoft Access 2010 or 2013.
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
2
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 or format 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 3.
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 announcement for additional Access tutorials.
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 2013, 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. Extension “accdb” applies to MS Access 2010.
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 3 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
Task 3A. Create table structure for Insurance table.
3
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)”.
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.
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
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.
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.
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.
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
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 3 in Blackboard. This file contains tab delimited data. Import employee.txt into Employee table as follows:
9
 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.
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 “Spouse 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” 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
11
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 3. That is, he is interested in the sum of insurance rates per employee in Department 3 . 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 example use only.
Employee Id Last Name Monthly Payroll Deduction —- —– —— Total 999.99
Task 6C. Query3 – Employee Dependent Code per Insurance
12
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.
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)
13
b. Modify Page Header section
i. Change column names to correspond to the report layout in Exhibit 9.
d) Save the report.
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
00003 Slovacek Gordon E
Task 7B. Report 2 –Employee Personalized Enrollment Report
Mr. Andrews also requests that you create a 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.
14
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.
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
Insurance Company Dependent Code Rate
Best Health Care E $0.00
Best Health Care C1 $92.33
15
Assignment 3 Deliverables:
You will turn in one file for Assignment 3. 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 or 2013 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 3 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 3 (i.e., XYHealth.aacdb) to the following naming convention: first initial, last name, 3 (e.g., for Andy Smith’s assignment the file will be named asmith3). Upload your file to Blackboard using the appropriate upload links provided (follow the upload instructions listed under Assignment 3 link).
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.
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
G: Employees By Insurance Report.
8
16
Insurance Report (reportEmployeesbyInsurance) and its associated query (QueryEmployeesByInsurance)
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.
8
Total: 50

1    1    Art    Gumble    2007 Logan    Chino Hills    CA    91709    4789
2    2    Robert    Walters    748 Birch    Long Beach    CA    90801    2792
3    3    Gordon    Slovacek    2255 Woodcreek    Redlands    CA    92373    5595
4    4    Perla    Furukawa    19404 Golden Eagle    Chino Hills    CA    91709    5710
5    5    Jaque    Votaw    203 Chowning Avenue    Anaheim    CA    92801    3201
6    1    Tang    Tong    1920 Timberlane Road    Temecula    CA    92593    4788
7    1    Patricia    Vyapuri    1412 Henny Penny    Escondido    CA    92029    4777
8    2    Melinda    Fogle    1312 Cherryville    Long Beach    CA    90801    2793
9    3    Eric    Gonzalez    1921 Summerfield Drive    Long Beach    CA    90801    5596
10    4    Akiko    Chee    817 Madison    Redlands    CA    92373    5711
11    5    Chia-Yi    Lou    301 Lancaster    Redlands    CA    92373    3202
12    1    Terrance    Estrada    14901 Geeta Grove    Chino Hills    CA    91709    4776
13    2    Antonio    Enriquez    916 Kennifix    Chino Hills    CA    91709    2794
14    2    Donald    D’Amico    200 W 14th Street    Chino Hills    CA    91709    2795
15    2    Vicky    Carder    3901Santa Fe    Chino Hills    CA    91709    2796
16    3    Donatica    Angelo    611 Praire Avenue    Chino Hills    CA    91709    5597
17    4    Lorraine    Wakefield    3705 Sleepy Lane    Chino Hills    CA    91709    5712
18    5    James    Lester    710 Ashecroft    Temecula    CA    92593    3203
19    3    Rex    Tickner    36095 Lincoln    Anaheim    CA    92801    5598
20    3    Deona    Timanus    1514 Washington    Anaheim    CA    92801    5599
21    4    Gayle    Yates    1300 Cleveland    Anaheim    CA    92801    5713
22    5    Zee    Yereshenko    7712 Martin Luther    Long Beach    CA    90801    3204
23    5    Xia    Lujin    38292 Kickingbird    Chino Hills    CA    91709    3205
24    4    Wei    Wang    1623 Hanah Parkway    Temecula    CA    92593    5714
25    3    Ying    Fang    2476 North Elm    Redlands    CA    92373    5594
26    6    Brenda    Fees    248 Lane Way    Long Beach    CA    90801    3734
27    6    Becky    Hendrix    4971 Ranch Acres    Long Beach    CA    90801    1333
28    5    Bernard    Landry    63658 Iowa Drive    Redlands    CA    92373    8410
29    1    Carol    Dawenport    918 Anabor Lane    Redlands    CA    92373    8246
30    4    Dane    Carnes    715 SW 60th    Chino Hills    CA    91709    3582
31    2    Erik    Branning    7310 Deerhurst    Chino Hills    CA    91709    9953
32    1    Elliott    Brainard    38475 Shady Lane    Chino Hills    CA    91709    6047
33    6    Henry    Luft    261 Johnson Drive    Chino Hills    CA    91709    5676
34    2    Allan    Robnett    817 Rushing Waters    Chino Hills    CA    91709    2528
35    6    Doyle    Tysdell    7103 Royal Bridge    Chino Hills    CA    91709    3037
36    6    Elvera    Shimp    8392 Avenda Avenue    Temecula    CA    92593    1951
37    5    Rachelle    Logue    6038 Village Street    Anaheim    CA    92801    3114
38    4    Annie    Hull    712 8th Street    Anaheim    CA    92801    0745
39    1    Deka    Delarosa    1604 Mohican Avenue    Anaheim    CA    92801    5185
40    6    Ben    Hartmiller    11590 Heritage Drive    Chino Hills    CA    91709    3007
41    3    Gerry    Leffingwell    2020 Shining Willow    Chino Hills    CA    91709    1322
42    6    Tammie    DeLoach    18808 Woodhaven    Chino Hills    CA    91709    6022
43    2    Luis    Munoz    640 Venturi Drive    Anaheim    CA    92801    4363
44    5    Bryan    Russell    2216 Milling Street    Chino Hills    CA    91709    1082
45    3    Jim    Brittingham    8756 Lima Street    Alhambra    CA    90801    2865   

1,C1,BHC,1/1/2010 0:00:00
1,E,BHC,1/1/2010 0:00:00
2,E,AALC,1/1/2010 0:00:00
2,E,PV,1/1/2010 0:00:00
2,S2,AALC,1/1/2010 0:00:00
2,S2,PV,1/1/2010 0:00:00
3,E,BHC,1/1/2010 0:00:00
4,E,BHC,1/1/2010 0:00:00
4,E,MD,1/1/2010 0:00:00
4,E,PV,1/1/2010 0:00:00
5,E,AALC,1/1/2010 0:00:00
5,E,MD,1/1/2010 0:00:00
5,E,PV,1/1/2010 0:00:00
5,S,AALC,1/1/2010 0:00:00
5,S,MD,1/1/2010 0:00:00
5,S,PV,1/1/2010 0:00:00
6,E,AALC,1/1/2010 0:00:00
6,E,MD,1/1/2010 0:00:00
6,S,AALC,1/1/2010 0:00:00
6,S,MD,1/1/2010 0:00:00
7,E,AALC,1/1/2010 0:00:00
8,E,BHC,1/1/2010 0:00:00
8,E,MD,1/1/2010 0:00:00
9,C1,BHC,1/1/2010 0:00:00
9,C1,MD,1/1/2010 0:00:00
9,C1,PV,1/1/2010 0:00:00
9,E,BHC,1/1/2010 0:00:00
9,E,MD,1/1/2010 0:00:00
9,E,PV,1/1/2010 0:00:00
10,E,BHC,1/1/2010 0:00:00
10,E,MD,1/1/2010 0:00:00
10,E,PV,1/1/2010 0:00:00
10,S2,BHC,1/1/2010 0:00:00
10,S2,MD,1/1/2010 0:00:00
10,S2,PV,1/1/2010 0:00:00
11,E,BHC,1/1/2010 0:00:00
12,E,AALC,1/1/2010 0:00:00
12,S1,AALC,1/1/2010 0:00:00
13,C2,AALC,1/1/2010 0:00:00
13,E,AALC,1/1/2010 0:00:00
14,E,AALC,1/1/2010 0:00:00
15,C2,BHC,1/1/2010 0:00:00
15,C2,MD,1/1/2010 0:00:00
15,C2,PV,1/1/2010 0:00:00
15,E,BHC,1/1/2010 0:00:00
15,E,MD,1/1/2010 0:00:00
15,E,PV,1/1/2010 0:00:00
16,E,BHC,1/1/2010 0:00:00
16,E,PV,1/1/2010 0:00:00
17,E,AALC,1/1/2010 0:00:00
17,E,MD,1/1/2010 0:00:00
17,E,PV,1/1/2010 0:00:00
17,S2,AALC,1/1/2010 0:00:00
17,S2,MD,1/1/2010 0:00:00
17,S2,PV,1/1/2010 0:00:00
18,E,BHC,1/1/2010 0:00:00
19,E,AALC,1/1/2010 0:00:00
19,S2,AALC,1/1/2010 0:00:00
20,E,AALC,1/1/2010 0:00:00
20,S,AALC,1/1/2010 0:00:00
21,E,BHC,1/1/2010 0:00:00
21,E,MD,1/1/2010 0:00:00
21,S2,BHC,1/1/2010 0:00:00
22,E,AALC,1/1/2010 0:00:00
22,E,MD,1/1/2010 0:00:00
22,E,PV,1/1/2010 0:00:00
23,E,AALC,1/1/2010 0:00:00
24,E,BHC,1/1/2010 0:00:00
24,E,MD,1/1/2010 0:00:00
25,E,AALC,1/1/2010 0:00:00
25,E,MD,1/1/2010 0:00:00
25,E,PV,1/1/2010 0:00:00
25,S2,AALC,1/1/2010 0:00:00
26,E,AALC,1/1/2010 0:00:00
27,E,AALC,1/1/2010 0:00:00
27,E,MD,1/1/2010 0:00:00
27,E,PV,1/1/2010 0:00:00
27,S2,AALC,1/1/2010 0:00:00
27,S2,MD,1/1/2010 0:00:00
27,S2,PV,1/1/2010 0:00:00
28,E,AALC,1/1/2010 0:00:00
28,E,MD,1/1/2010 0:00:00
28,E,PV,1/1/2010 0:00:00
28,S2,AALC,1/1/2010 0:00:00
28,S2,MD,1/1/2010 0:00:00
28,S2,PV,1/1/2010 0:00:00
29,E,BHC,1/1/2010 0:00:00
30,E,BHC,1/1/2010 0:00:00
30,E,MD,1/1/2010 0:00:00
30,E,PV,1/1/2010 0:00:00
31,E,AALC,1/1/2010 0:00:00
32,E,BHC,1/1/2010 0:00:00
32,E,MD,1/1/2010 0:00:00
32,E,PV,1/1/2010 0:00:00
33,E,AALC,1/1/2010 0:00:00
34,E,AALC,1/1/2010 0:00:00
34,E,MD,1/1/2010 0:00:00
34,E,PV,1/1/2010 0:00:00
34,S1,AALC,1/1/2010 0:00:00
34,S1,MD,1/1/2010 0:00:00
34,S1,PV,1/1/2010 0:00:00
35,E,BHC,1/1/2010 0:00:00
35,S,BHC,1/1/2010 0:00:00
36,E,BHC,1/1/2010 0:00:00
37,E,BHC,1/1/2010 0:00:00
37,S,BHC,1/1/2010 0:00:00
38,E,BHC,1/1/2010 0:00:00
38,S1,BHC,1/1/2010 0:00:00
39,E,AALC,1/1/2010 0:00:00
39,S,AALC,1/1/2010 0:00:00
40,E,AALC,1/1/2010 0:00:00
41,C2,AALC,1/1/2010 0:00:00
41,C2,MD,1/1/2010 0:00:00
41,C2,PV,1/1/2010 0:00:00
41,E,AALC,1/1/2010 0:00:00
41,E,MD,1/1/2010 0:00:00
41,E,PV,1/1/2010 0:00:00
42,E,BHC,1/1/2010 0:00:00
43,E,AALC,1/1/2010 0:00:00
43,E,PV,1/1/2010 0:00:00
44,E,BHC,1/1/2010 0:00:00
44,E,MD,1/1/2010 0:00:00
44,S2,BHC,1/1/2010 0:00:00
44,S2,MD,1/1/2010 0:00:00
45,E,AALC,1/1/2010 0:00:00
45,E,MD,1/1/2010 0:00:00
45,E,PV,1/1/2010 0:00:00
45,S1,AALC,1/1/2010 0:00:00
45,S1,MD,1/1/2010 0:00:00
45,S1,PV,1/1/2010 0:00:00

InsCode    DepCode    Rate
AALC    c1    185.25
AALC    c2    247.12
AALC    E    0.00
AALC    S    287.50
AALC    s1    458.72
AALC    s2    525.99
BHC    c1    94.75
BHC    c2    136.66
BHC    e    0.00
BHC    s    178.95
BHC    s1    312.57
BHC    s2    378.77
MD    c1    32.75
MD    c2    43.89
MD    e    23.13
MD    s    34.57
MD    s1    64.76
MD    s2    69.88
PV    c1    18.11
PV    c2    31.16
PV    e    11.55
PV    s    14.15
PV    s1    45.47
PV    s2    45.88