Access to Excel Pivot Table

Problem:
Large Access database with many tables and queries
Why it mattered
Sales manager and Reps needed sales information by product, product lines and province and YTD and LYTD comparisons and industry wide sales
Characters:

  • National Sales Support Manager
  • Sales Reps
  • Manager, Reporting and BI

Attempts at resolution:
Use Access pivot tables
Solution:
Created and automated several queries and ended up with a view which was imported into Excel. Using the Excel Pivot table features, created Pivot tables based on the parameters managers were interested in, such as time, provinces, reps and products.
Summary:
This allowed the sales manager to view and compare year-today sales with last year’s sales for any product, product line and province and the percent increase or decrease. The pivot tables and drill downs also showed seasonal sales and sales by product size.