BYU Student Author: @Alex_Garrett
Reviewers: @Marco, @DylanKing, @Spencer
Estimated Time to Solve: 40 Minutes
We provide the solution to this challenge using:
- SQL (Microsoft Access)
Need a program? Click here.
Overview
PANIC! COVID-19 has been difficult for the audit firm because so much of your work used to be done at the client site. Additionally, travel restrictions and client withdrawal have made it even more challenging. Your manager is unsure of the firm’s future if COVID continues to disrupt its operations.
The budget for 2020 travel expenditures has been adjusted to reflect these changes, but your manager suspects that employees have been using the firm’s funds to vacation while “working from home.” While it is not the end of the year, you are tasked to investigate budget discrepancies in order to identify areas that require further investigation. Your manager shares with you the “Expense Database” which has expense data since the firm’s inception. Read the data dictionary to understand how the database is organized before running queries.
Instructions
This challenge focuses on manually typing SQL statements to retrieve the desired data. Query Design can also be used to solve this challenge, but it’s extremely helpful to be able to read SQL statements and know what is happening. I recommend manually typing the SQL statements before using Query Design. Click here for a quick video on how to get your SQL statements started.
- Show employees who have the most missing receipts (include all years), but show only employees who are missing more than 5 receipts. Use the following headers for your output: “EmpName” and “MissingReceipts”. Show the employee with the most missing receipts first.
- Show employees who underestimated their total costs for trips that began in 2020. Use the following headers for your output: “EmpName”, “TotalEstCosts”, “TotalActCosts”, and “Difference” (configure the “Difference” column so that an underestimated budget results in a negative difference). Round the difference column to 2 decimal places, and show the employee with the greatest difference first.
- Show employees who spent more than their budget in 2020. Include all trips that began in 2020. Use the following headers for your output: “EmpName”, “BudgetAmount”, “Expenses”, and “Difference”. Round the difference column to 2 decimal places, and show the employee with the greatest difference first.
- Show the company-wide budget for the past three years (2018-2020), and show how much money the company over- or under-spent for each of those years. Use the start date of trips to determine in what year we account for them. Use the following headers for your output: “BudgetYear”, “TotalBudget”, “TotalExpense”, and “Difference”. Round the expense and the difference columns to 2 decimal places and order this table by “BudgetYear” ascending.
Data Files
Suggestions and Hints
The UNION operator is a less-known tool, but it can be very useful in completing this challenge.
Solution
Challenge28_Solution.accdb
Solution Video: Challenge 28|SQL – Big Money Budget