Excel or Access?
Microsoft has lots of great tools, and in most cases, it’s pretty clear which you need to use. If you need to write a letter, Word is your tool. If you need to create a slideshow, that’s PowerPoint. But often there’s confusion about Excel and Access. Since both can be used as database tools, which is best? The answer is: it depends.
If you’re asking the question, you are probably already familiar with Excel, and you’ve probably already come across some of the issues when using it as a database. You’ve probably also heard that Access is the superior tool for databases, but you’re not sure why. In this blog, I’ll give you some information to help you decide which is right for you.
Does this sound familiar?
Here’s a common scenario: you need to keep track of a list of some sort - let’s say a list of contacts, for example. Since you are already familiar with Excel, you create your list there. At first, everything goes well. But over time, you discover some things you don’t like. For example:
When you have multiple contacts for the same company, you find yourself re-typing the company’s name and address over and over.
Your inconsistent entries cause inefficiencies when sorting and filtering. For example, if you enter “ABC Company” and “ABC Co.”, Excel considers this 2 separate companies.
You find that your Excel data is often incomplete or incorrect. For example, you have email addresses that are incorrectly formatted, or telephone numbers that are missing a digit.
If this list sounds familiar, you are right to consider the switch to Microsoft Access. While Excel is great for reporting, sorting, filtering, and running complex calculations, Access has the edge when it comes to data integrity. By using a series of related tables, it allows you to streamline your data entry effort. In the scenario described above, you’d set up a company once, and then add each of the contacts for that company. That reduces the problem with inconsistent entries; since you only enter the company name once, you won’t have to worry about it being typed differently a second time.
Access also allows you to create data validation rules to ensure that your data is entered correctly and completely. For example, you can specify that email address is a required field and can’t be left blank.
So I should use Access, right?
Not necessarily. Here’s the rub. Access is a harder tool to learn. Let me clarify that: Access is a harder tool to learn to use correctly.
Lots of people jump right into Access and start using it just like it was an Excel database. At best, that’s an inefficient way to use Access. At worst, it creates a messy situation that can be difficult to clean up.
In order to use Access properly and take advantage of it’s features, you need to learn some relational database concepts. Without that, you’ll just be using Access as an extension of Excel. What a waste!
That doesn’t mean you shouldn’t take the plunge. Once you’ve got your data in Access, you’ll be able to start implementing the features that will make your data entry quicker and easier and protect the integrity of your data. In my experience, those are usually worthwhile investments.
Your data is the lifeline of your business. Investing the time to set up an effective database will save you time in the long run. Being able to quickly and accurately access your data will maintain your professional image in the eyes of your customers.
How do I get started?
Once you’ve decided that you need to make the leap to Access, the conversion process can be very easy or very difficult, depending on how complicated your data is. The good news is that Access has a feature that allows you to import data directly from Excel. The bad news is that will only be the first of many steps.
Before you open Access, you should spend some time learning about something called Relational Database concepts. You won’t need in-depth knowledge on the subject at this point, but you need to understand what a table is and how tables can be related to each other.
The next step will be to look over your Excel data and determine how many tables you’re going to need. A lot of people think they only need one table because they are only using one Excel sheet. But I encourage you to think carefully about that. If you’re experiencing any of the issues listed above in the “Does this sound familiar” section, you probably need more than one table. Go back to those Database Concepts and think that over carefully.
After you’ve determined how many tables you need, think about the Primary Key for each table. Every table should have a field or fields that make every record unique. For example, a Customer table would probably have a CustomerID. This is called the Primary Key or Primary Index. You want to make sure that every table in Access has a unique Primary Key field.
Off and running!
At this point I’ve given you just enough information to be dangerous in Access! I kept this simple - there is a lot more that could (and has been) said on the subject. If you’re going to attempt to convert your mission-critical business applications from Excel to Access, you’ll need to do a lot more learning. But if you’ve got a simple Excel file and some free time to play around, jump in and get your feet wet!
If you decide that you need help with your transition or would like to share a success story, reach out to me at North Port Solutions, LLC.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2016 North Port Solutions, LLC. All right reserved.