skills – Daily Dose of Excel

I recently saw a resume with a bunch of VBA and SQL skills on it. I’ve never seen anything like it. I gave up trying to find someone with the right combination of skills a long time ago. Someone to whom I could offload some of my projects. But this had promise.

I couldn’t get a great read on his skills just from the interview, so I, with the help of a really smart coworker, devised a test. We set up a PC, gave him some files, gave him internet access, and watched to see if he could complete the tasks. We’ve never devised a test before, so I had some doubt about whether we did as good a job as we thought. That’s where you come in. If you have Excel, VBA, and SQL skills, I’d like to know how easy or hard you find these questions.

Some of the necessary files are provided below. If you don’t have SQL Server and Adventure works, you can just describe how you would do it using your database of choice. Note that we installed SQL Server Express and Adventureworks on the test PC, so he had all the tools necessary to complete the tasks.

Leave your answers to these questions, your thoughts about these questions, or both in the comments.

Using the Adventureworks2012 database on the local SQL Express, create a report showing Salesperson Name, Total Due, Total Quantity, and the count of unique products for the top five salespeople sorted by Total Due highest to lowest.
Here’s a sample of the results.

Salesperson Total Due Total Quantity Unique Products

David Campbell $44,214,217.28 2,313 126

Pamela Ansman-Wolfe $46,015,977.48 2,622 128

Here is the schema needed to get this information
From the LoyTrans.csv file, report the sum of gallons for the TrxDate of April 3, 2017.
In Excel, create an External Data Query to the AdventureWorks data. Bring in the sales order data for the year 2007. Create a pivot table showing the average unit price by product and month.
Open ReadWriteRange.xlsm. Write a VBA procedure to read in all the data in Sheet1, multiply each number by 10, and write the data back to a new worksheet.
Create an Excel workbook where the user chooses a Salesperson. The workbook should display the name, title, phone, address, territory data, and SalesQuota. The user will enter a new SalesQuota number. Your code will update the database with the new SalesQuota number.