Exploring Microsoft Access 2016, Chapter 3

Employee Compensation

You have been hired by a local small manufacturing company to analyze employee compensation. There have been several complaints that employee salary and bonuses are completely arbitrary and not tied to their years of service, job title, or annual review score. You will analyze data by creating several queries to help substantiate or disprove the complaints.

Create a Query with a Calculated Field

You will create a new query that returns each employee’s total compensation along with a total for all compensation.

• Open a03_exam_chap_data and save as a03_exam_chap_LastFirst.
• Create a new query named qryTotalCompensation that includes all fields from tblCompensation.
• Add a new calculated field named Total Compensation, which is a total of salary and bonus.
• Set the format of the new field to Currency with 0 decimals.
• Add a new Total row to qryTotalCompensation for the Salary, Bonus, and Total Compensation fields.
• Create a new query named qryTotalByTitle that is a total query of all employees by title.
• Add the minimum and maximum salaries for each title. Include the following grouping and aggregations:
• Create a new query named qryEmployeeTitle.
• Add the following fields and aggregations:
• Create a new query named qryOperators that includes all fields from tblCompensation.
• Set the criteria so that the query will display all operators that are making less than the average salary ($40,000) and have a rating higher than the average rating (5).
• Save and close the file. Based on your instructor’s directions submit a02_exam_chap_LastFirst.

Create a Query with Grouping

You need to create a new query that returns total compensation grouped by Employee Title along with the highest and lowest paid employee with each Title.

• Employee Title = Grouped by
• Salary = Total of Salary
• Top (new field) = Highest Salary
• Bottom (new field) = Lowest Salary

Create a Query to Analyze Average Rating and Average Salary

You need to create a new query that returns employee compensation grouped by Employee Title against what the average rating and average salary for each group.

• Employee Title = Grouped by
• Salary = Average Salary
• Review Rating = Average Rating
• Bonus = Average Bonus

Create a Query to Analyze Possible Discrepancies

You need to create a new query that returns individuals who received a high rating and are getting paid less than the average salary.