Analytics mindset

IntegrateCo

Part III: 

Now that the data is loaded into the tool you will use to analyze the results, you should answer the questions you developed. In addition to answering those questions, Noah has developed a specific set of questions for you to answer.  

Required

1. Complete the following chart using the data provided by Noah. Note that there should be a row for each unique job code. Sort the results by job code. Make sure to include all job codes, even if there are no responses for a job code in one of the years (an error listed is OK if there is no data in that field). Using a formula, round the per hour fields and the Gross pay difference field to two decimal places. 

– Note the following: The 2015 and 2016 Gross pay columns should list the gross wages for each year. The average cost per hour is equal to the cost for that year divided by the number of hours for that year. The columns Hours difference and Gross pay difference should be computed by taking the 2016 amounts and subtracting them from the 2015 amounts (i.e., positive amounts mean 2016 was more than 2015).  

– When saving your queries, name the final query with your complete answer as qry_Part3_Q1_FINAL. If you use any queries to create your final query, name them as qry_Part3_Q1_DESCRIPTION, where DESCRIPTION provides a brief, useful description of what you are doing in that query.

2. Write a query to return the information listed in the chart below. Design the query so that the user is prompted to enter the job code and the results are only returned for that job code. Only return results for employees who have more than zero hours and zero wages in a job code for both 2015 and 2016. Sort the query in ascending order by the Gross pay difference. Using a formula, round the per hour, Hours difference and Gross pay difference fields to two decimal places. Investigate the three job codes with the largest positive wage differences found in the previous query that had wages and hours for both 2015 and 2016.

3. Write a memo to Noah about what you have learned. Make sure to include the tables above in the memo and any additional tables that you created. Discuss any discrepancies that you observed and make suggestions about what you recommend Noah should do next.

Data