COSC1702AB21SS-Project5

Ques 1.Problem: You are employed in the personnel office of a major corporation. You asked each of your three branch offices to send you an updated list of their employees complete with the employee's office location, number, name, job code, job status, current salary, years of service, and number of dependents. One office sent you a text file, one office sent you an HTML file, and the last office sent you an Access database file. Instructions: Perform the following tasks: 1. Open the Employee List workbook named Project5-Q1_Start_File.xlsx illustrated in Figure P5-1. The file is posted in CMS along with this question. Format the range to look nice (choose a unique font and colour scheme) but not exactly like the following (Note: do not yet make this into a table): Figure P5-1 2. Click cell A3. Import the text file from the Edmonton office using the From text (Legacy) import wizard, Project5-Q1_Edmonton.txt (posted in CMS). It is a comma delimited text file. In step 2 of the Text Import Wizard, click the Comma check box but accept all other default settings. Do not adjust column widths. Convert the imported data from a table to a range. 3. Click cell A8. Import the HTML file using the From Web (Legacy) importer for Montreal, Project5-Q1_Montreal.htm (Note1: if you get any script errors loading the legacy importer this is fine, it is simply a script issue with the default web page the importer uses, clicking no should allow you to dismiss them) (Note 2: I have given you the file locally on your system as a part of the starting documents. You can use the location that you extracted/ downloaded the file to in the address bar (something like file:///C:/Users/You/Downloads/Project5/Project5-Q1_Montreal.htm OR if you have a hard time finding it locally on your system, I have also uploaded this file to the web for you: https://storage.googleapis.com/cosc1702project5/Project5-Q1_Montreal.htm both of these are acceptable as they are the same file). Click the Arrow next to the table data you wish to import (you dont need to import the headers). When the Import Data Prompt COSC1702 Project #5 appears Click Properties, and in the External Data Range Properties dialog box deselect the formatting and layout options to preserve formatting and adjust column width. Click ok and import the data. 4. Click cell A13. Windows Users: Import the Access database file Project5-Q1_Regina.accdb for the Regina office (posted in CMS). Add all the fields from the Employee table. Accept all the default settings. Convert the imported data from a table to a range, and remove the header row that was imported with this table. A special note for mac users: If you are running windows you can ignore this part Excel for mac does NOT support microsoft access, therefore it does NOT support the accdb file format. While there are a handful of converters online (https://mdbviewer.herokuapp.com/ for example) This is generally NOT a best practice. While for the purposes of this class we are just uploading made up data, however the data you may be dealing with in the future may contain sensitive customer information, and you should NOT be uploading it to random websites on the internet. That being said for the purposes of completing this assignment, using a site like that is fine, however, in the real world you or the company you work for should purchase a license for a piece of conversion software that would run locally on your computer, to ensure that you are not uploading sensitive data to some random server on the internet. If you do not feel comfortable using this site to convert the file to a csv, I have uploaded a special already converted CSV file to the CMS specifically for mac users. 5. Remove any formatting from the data and column heading cells do so using the Format Painter by copying the format of an empty cell over the other cells. Then convert all of the data into a table and provide a style of your choosing to the new table. 6. Enter your name, the course number, the title of this question in the Document Properties tab. Save the workbook as Yourname-Project5-Q1.xlsx where Yourname is your own last name. 7. Create a Pivot Table with Row fields of office and job status and Data Item of Current Salary. Change the Data Item field settings to average instead of sum. Name the pivot table sheet 21stPivotTable, name the pivot table PV_21st and apply a different style to the table (your choice). Format the Average Current Salary data to Currency with no decimal places. 8. Create a pivot chart from your pivot table and add it to the current worksheet. Select the chart type to 3-D column chart. Personalize the chart (different style, colours, theme, alternate titles...) DO NOT COPY THE COLOURS OR STYLES SEEN BELOW. 9. Add Slicers for the Office and JobStatus. Give them separate styles and colours. DO NOT COPY THE COLOURS OR STYLES SEEN BELOW. Also, Hide the Axis Filters on COSC1702 Project #5 the Chart. Again, adjust the colours of your slicers and chart so they are easy to read but are unique to your project and not the same as the following example 10. Save the workbook again and upload it to CMS.