MS Access Assignment

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:

 

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 – 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	Contains data about the insurance companies contracted by IES
Employee	Contains data about the employees employed by IES
Dependent	Contains data about the employee dependents (e.g., spouse and children)
Rate	Contains data on the current insurance rates for employee benefits
Enrollment	Contains data on employee enrollments in the chosen insurance plans

 

 

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

 

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

 

 

 

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 / 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.
• 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:

 

• Review data and come up with expected results for the query (what should the content of the output report look like?)
• 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]
• Select columns to be displayed in the query result
• (optional) Select additional columns which are not displayed in the result, but may be used for totals or criteria values.
• Specify criteria values and sorting options
• Change column names under Caption in Property Sheet for each displayed column
• 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]

 

Insurance Name

Insurance Comment

Total Number of Employees

 

 

 

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

 

 

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. 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:

1. 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.
2. Use Report Wizard (located under Create tab…Reports group) to build the Employee by Insurance report based on QueryEmployeeByInsurance you have just created.
   1. Sort the Detail records by employee identification number (eid)
   2. Select ‘Stepped’ layout (if available….otherwise choose another)and ‘Portrait’ orientation.
   3. Use whichever style you’d like to.
   4. Name the report –
   5. Preview the report and then modify its appearance and content
3. Open the report in Design View.
   1. Modify Report Header section
      1. Change report title: Employees by Insurance
      2. Underneath the title, put a built-in function “=Date()” to display current date (this function must go within a text box—not a label)
   2. Modify Page Header section
      9. Change column names to correspond to the report layout in Exhibit 9.
   3. 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

• 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:

 

1. 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
2. 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).
   1. Name the report –
   2. Modify report’s layout to match Exhibit 10.

Open the report in Design View.

1. 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
2. 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.

• 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))

1. 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

     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)
  • Insurance, employee, dependent, rate, enrollment
• Forms
  • PopulateInsurance and PopulateDependent (tasks 5A and 5C)
• Queries
  • Query1EmplperIns (task 6A)
  • Query2MonthlyPayrollDeductions (task 6B)
  • Query3DepCodeperInsurance (task 6C)
  • QueryEmployeesByInsurance (task 7A)
  • QueryPersonalizedEnrollmentNew (task 7B)
• Report
  • ReportEmployeesbyInsurance (task 7A)
  • 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.

 

 

 

 

 

 

 

 

 

 

 

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

 

 for fast devlivery please contact us at 

san.marshall.skills@gmail.com