Help with Database Access

Due to the increasing amount of employee discipline for technology violations, you created a Microsoft Excel workbook to use for tracking employees and their Netiquette violations in your department. Your boss loves it! She has pitched the idea to the company President that this system should be used across all departments company-wide. You feel this task can better be accomplished using Microsoft Access, as the company has several hundred employees. 

Instructions

Import the Data

1. Create a new blank Access database.

2. Save the Access database as “ Violations_MEID.accdb.” N ote: Replace MEID with your MEID number.

3. Import the  Violations-Import.xlsx Excel spreadsheet as external data.

4. Be sure to check the  First Row Contains Column Headings. Let Access add the Primary Key. Name the new table  Employees. Once you finish your import, open the Employees table to verify your data has imported correctly and is sorted in ascending order by Name.

5. Make the following field property changes:  Name: Change the field size to 50.  Note: Increase the field size if needed to match the longest Name you created and entered.

6. Repeat the above process for Position.

7. Length of Employment: Change the caption to 'Months of Employment'.  

8. Save the design changes.

9. Choose yes if you receive the "Some data may be lost" warning.

Create a Form

1. Create a new  form with a title of  Employee Violation and input fields for each of your six data points.

· Adjust the data field sizes so they all fit on the page.

· Apply a  theme of your choice to the form for visual interest.

· Save the form as  Employee Violations.

2. Go back into  Design Mode for the  Form

· Remove the Department field.

· Create a new Drop Down object where you will type in the choices for the departments. Bind the drop down to the Department field. (HINT: You should type in all departments that exist in your data).

3. Verify your new  Employee Violations form works by  entering a new violation record using your instructor's name, position of "Supervisor" and 36 months of employment. You may choose the data for the other fields.

Create a Query

1. Create a simple  query from the  Employees Table including the Name, Department, Financial Impact, and Nbr of Incidences. Title the query  Employees by Violation.

· Sort by  Financial Impact descending.

· Create a new expression within the Query called "Per Impact Cost" that divides the Financial Impact by the Nbr of Incidences.  Format this new field as Currency.

· Run the query to ensure that the calculated field was created correctly.

· Close and  Save the query.

Create a Report

1. Create a new  report using your  Employees by Violation query. (Hint: Use the Report Wizard)

· Include  all fields from the query on the report.

· Group the report by Department.

· Sort the report by Name.

· Change the report title to  Employees by Department and ensure your fields fit within the report page borders.

· Apply a theme of your choice to the report and  save the report.

· Create an embedded macro within the Report that displays a reminder box each time it is closed, the box should display  "Be sure to send reports to your supervisor weekly!"

· Close and  save the report.

· Reopen the report and then close it again to ensure that your macro runs and displays the message box as intended.