This year I had a great opportunity to learn Microsoft Access.

MS Access is a database software that comes with Microsoft Office (Word, PowerPoint, Excel, et cetera). To my surprise, I found it was packaged with my home edition of MS Office. If you are paying for a home edition of MS Office, you already have it for as long as you pay the monthly fee ($8).

In this article I will show you the basics of what I learned. I will describe how you can use this software to solve problems in your Quality unit or in the larger organization. I will show you how you can gain the highly advantageous moniker of “the software person” on your quality team within a short timeframe. And I will highlight some great opportunities to pursue once you get a handle on this powerful database tool.

What is MS Access?

You can easily read the Wikipedia page, visit the Microsoft tutorial site, or watch a couple of Youtube videos on what Access does, so I won’t restate all that information here. In a nutshell, an Access database helps you go beyond Excel spreadsheets in robustness and functionality.

Think of the biggest, oldest, most bloated spreadsheet you use at work. Perhaps it is a list of approved suppliers. Perhaps it is a team tracking spreadsheet. Perhaps it is a spreadsheet for tracking defects and corrective actions in outgoing products.

Now think of the problems involved: individuals are often locked out as another person edits the spreadsheet. Multiple versions are floating around, and there is confusion over which one is definitive. They constantly need to be reconciled. The spreadsheet is old, so it is opened in compatibility mode and some macros have broken down, resulting in extra clicks with every opening and closing of the file. The file is large and slow. Individuals can easily delete, replace, or fat-finger their entries without much traceability. When it comes to analyzing the data, you have to make extra sheets and you question whether you are drawing from the correct source cells. Redundant and ambiguous values proliferate.

A database helps you get around these problems. For instance, instead of navigating to the correct sheet and entering data in an individual row, the user can fill out an attractive, easy-to-use form. Instead of relying on finicky charts, you can export a standardized report with parameters you have specified. Finally, there is the extremely important advantage of unambiguous (unique) values. A properly designed Access database, unlike a spreadsheet, will not give you ambiguous answers when you ask, “What is client X’s current email address?”

The four main components of Access

Access has four pillars: tables, forms, queries and reports.

Tables

An Access table is basically a spreadsheet. In fact, you can enter data directly in each table just like you would with an Excel spreadsheet. Early on, however, you will assign a primary key to each table that ensures unique records. Access tables can also be linked in very versatile and customizable ways that allow you to answer tailored questions and perform trending and other analysis.

Forms

Access forms are exactly that: an organized way for the user to enter data. Instead of typing into a row on a spreadsheet, the user can follow the prompts in a pretty, organized form. The data goes precisely to the table or tables specified by the designer. Invalid data entries can be rejected.

Queries

A database can grow to huge proportions. A query allows a user to ask a simple question, filter data to answer a question, combine data from multiple tables, or add, alter or delete data from tables.

Reports

Reports are fun. They allow you to analyze and visualize vast amounts of database data based on your specific parameters. You can customize the report’s header and graphics.

Assistance for the learner

All of the above components are accompanied by a “wizard.” This is Microsoft’s module for helping you efficiently select the correct option when constructing your database.

In addition, you will not be writing code unless you want to. If you have a giant database that needs to do something highly specialized, you can learn macros and Visual Basic to solve the problem. But you don’t need to learn to write code, or google snippets of code, to get the job done. Instead, Access is populated with tools, drop-down lists, drag-and-drop functionality, and modules to help. Access also provides “views” such as Design View, Layout View, Datasheet View, Form View, et cetera that display essentially the same information, but in a different way. This allows you to get the job done in a very visual manner or in a spreadsheet-like way. And you can switch back and forth between views at any time to understand what you are looking at.

Three uses for Access in a Quality unit

Do you have an Approved Suppliers List? This important, often regulated document is sometimes nothing more than an Excel spreadsheet. Auditors may ask to see it and question whether it is locked down with the correct editing permissions. They may ask if it is the definitive record, or just a notation for the paper copies, which are definitive. An Access database can help you meet and surpass these expectations. Of course, you will have to validate the use of the database. But in the end it will be much less slippery than a spreadsheet (and less pricey than paying for supplier management software!)

Another use is for tracking your team’s work. Suppose you are trying to standardize the various tasks the typical Quality unit does. This often begins with timing studies. For a defined period, each Quality person can use the database to enter their daily tasks, along with the time it took to complete them. The form they use can even include a box for entering unexpected problems (Pareto events) that led to extra time beyond the standard/expected/takt time. When analyzing this timing data, management can pull up customized reports that zeroes in on outlier tasks and the corresponding problems. This allows management to focus on the 20% of the issues that are causing 80% of the problems.

Finally, if you are a preclinical testing laboratory, the GLP regulations require that QA maintain a Master Schedule of all studies. An Access database, validated of course, is a great place for this. QA can edit while still making it available on a view-only basis to anyone in the organization who needs to see it.

An example from my own life: a personal finance tool

Early on while learning Access I put one of my personal finance spreadsheets (my net worth tracker) into Access. I had been relying on a Google Drive spreadsheet for this task. However, as some accounts are opened and some are closed, it became more and more unwieldy. The auto-updating charts and tables I had created were becoming inaccurate and drawing from the wrong data sources.

So, I exported some of this data into Excel, and then into Access. After tweaking some of the reporting functions, I found this to be a more robust way of tracking my financial goals. I also created a better form for entering my biweekly update on payday. Currently my financial info is small enough to manage with spreadsheets. But over the course of decades, a database may be the way to go. If I ever start a business, I will be well-positioned to track this kind of info because of what I have learned to do with Access.

Growth and value to your company

I would encourage you to learn Access and teach it to others in your group. Don’t just preach, though: actually solve a problem. If you identify one or more people struggling with a bloated spreadsheet or with aging tracking and trending reports that are becoming less relevant, create a user-friendly database for them with a beautiful splash page. Show them the possibilities and then once they get the hang of it, offer to go all the way and import the source data with the support of management.

You will benefit immensely from gaining a reputation as “the software person” in Quality. People who can solve software issues in their group without expensive in-house IT and vendor involvement are greatly valued.

Once you have gained proficiency in Access, consider moving on to more advanced database software such as SQL. In this program, also provided by Microsoft, you can create advanced databases that integrate with web-based apps that face customers or internal groups. The reporting functionality is correspondingly advanced.

Where you can go to learn the software

Start with the training at Microsoft Support. Watch Youtube videos for every level of expertise.

Visit Lynda.com for an excellent, comprehensive guide to getting proficient. This guide is by an Access expert named Adam Wilbert. The course is 5 hours total and has been viewed 330,000 times. It is an excellent resource. My only complaint is that the guy talks very fast. I got around this by playing the tutorial at 0.75x speed! The course is filed under Business > Databases > Access 2016. I am fortunate to have free access to this learning suite through my metropolitan library. Check whether you have such a perk. If not, the monthly fee may still be worth it. Consider it a career expense.

Lastly, take an in-person course. It’s hard to learn from a screen alone. Although community education systems (through public schools or libraries) may offer Excel classes, they are unlikely to offer Access classes. So, look to community colleges and universities. My manager and I took an 8-hour, one-day course at Normandale Community College in Bloomington, Minnesota. Since we had acquainted ourselves already with the basics, we skipped to the intermediate level course. It was the right choice! I would recommend this to save some money. Again, I am fortunate that my employer covered this excellent career development opportunity.

My challenge to you

I would like to challenge you to a short-term goal of learning the basics of Microsoft Access. Create several tables with data relating to something you are into, such as sports, personal finance info, or something from work. Create a form, a query, and a report. Identify a problem at work that you might be able to help with. Lastly, let me know how it goes. I would love to hear from someone who has used Access to make the job easier for themselves and their colleagues. I would also like to hear from those who progressed successfully from Access to SQL or another web-integrated database software.