When we talk about data analysis & reporting, it would be hard to imagine a day of work without Excel. From the simplest of calculations to complex dashboards and VBA-backed reports, Excel has been and will be the go-to tool for anything related to calculations.

However, among the most commonly used applications in the Office Suite like Word, Excel, Outlook and Power-point, lies a hidden gem – the Microsoft Access (available in Microsoft Office Professional version only).

I prefer to label MS Access as the big brother of Microsoft Excel : it not just simplifies the data analysis and reporting tasks that Excel supports, but offers a lot more.

I started learning Microsoft Access about 15 years ago, beginning with my first project “the digital auto-book”, auto-book : the then popular trend of storing your friend’s info in a diary full of pictures, quotes and shiny words. Lets go through the basic terms related to Microsoft Access using “the digital auto-book” as a reference:

• Database – we refer to an Access files as Database, just like we call an Excel file a spreadsheet. For my project, I had a single database – autobook.accdb.

• Tables – Tables are where all of our data is stored. They are similar to excel based tables where we have a set of rows and columns. For my first project, I had a single table – autobook which stored all the data: name, address, favorites, images, etc. Generally we would have multiple tables in our database, as storing all data in a single table can have performance implications as we will see in the future access related posts.

• Forms – We can think of forms as the User Interface or the front-end. We use forms to add or delete data. A simple way to understand form would be to compare it against the interface of the ERP applications where we perform data entry. In my project, I had a single form to add / update data in the table.

• Queries – Queries are what we use to compute / join and analyze the data stored in tables. A simple example: we can write a query in Access to summarize the data as what a Pivot Table would do in Excel. As we will see in the future posts, queries are very very powerful and all of our data analysis and reporting projects make use of queries. I did not have any queries in the project as it did not involve any computations.

• Reports – As the name suggests, reports are the final output from calculation and analysis. General flow in access database is – table > queries > reports, with the forms being the interface to edit the data stored in tables. In my project, I had a single report. The report was a printable form of the data in my table.

• Modules – Modules are the collection of VBA codes and macros for our database. We write Visual Basic codes in Microsoft Access to perform automated tasks.

Having learned about the basic powers of Access in the early years, it was easy for me to adopt it to my workplace. However, my discovery of the power of Microsoft Access has never ceased to continue: I still learn about new possibilities with Microsoft Access everyday. Here’s what some of my databases do:

• 1-Click Formatted Reports: With a click, the database reads .csv, .xlsx files from a folder, runs multiple queries (aggregations / look-ups / logic) and exports the formatted report to a specified folder.

• Data Warehousing: Stores large amount of historical data in a single database, providing the ability to run analysis on large volume of data without any lags.

• Interface to ERP Software: The database Interfaces with the ERP software’s main database and provides 1-click real time reports.

• Email Analysis: The database connects to Microsoft Outlook and fetches a summary of emails, analyzes the response-time and generates a to-do report.

• Web Scraper: Connects to a specified website, fetches the data from the pages and stores them in for future analysis.

• Excel Merger: Merges the files from specified folder into a single Excel Spreadsheet document.

In my future posts, I will be sharing practical use-cases of Microsoft Access along with the completed databases for everyone to download and use.

Its never too late to learn. Try Access.