Sales Database and Reporting Dashboard Built in Microsoft Access
Our client is selling Telecoms products to customers in an area covering 3 counties. They are required to keep track of certain information and metrics for them but also record metrics for their own use. To do this they were using a mixture of spreadsheets provided by the Telecoms company and paper form filling. Some parts of the spreadsheets were irrelevant and they were not particularly user-friendly, hence they were looking for a way to improve their data input; to make it more user-friendly and also so that historic data was kept together and backed up appropriately.
The company were also intending to update and upgrade their IT systems, including updating their windows software to Office 365 and installing screens in their offices to give suitable displays of key metrics.
The key requirements were:
A cloud-based data storage solution that would be easily accessible from different locations and be backed up at regular intervals
New report designs to give appropriate, at a glance, feedback to their Sales Agents and to be displayed on their new office screens.
It was decided to use an Azure SQL Server Database to store the data in the cloud, with an MS Access Front End. Appropriate data would be exported to Excel Templates in order to generate the various reports.
Part 1: Sales and Orders Database
Initially, the database was designed in Access to allow the data structure to be amended before it was migrated to Azure.
There were three separate data sources which needed to be integrated into the database.
This spreadsheet contained details of all orders, making use of several different lookup tables on different tabs of the spreadsheet. It had multiple lines per order with many fields duplicated and a large number of calculated fields which were normally hidden. In addition, each field was completed manually, introducing the possibility of errors within the data. The data was normalised to prevent duplication and IDs were introduced for a number of fields (such as Customers and Agents) so that these could then be selected rather than typed to prevent errors. All sets of lookup data were created as individual tables and the Product and charging information was split into a number of tables to facilitate access to this information. An importer was built to bring this data in from the latest spreadsheet to the appropriate tables.