Microsoft Access is a great tool for those new to databases. With an interface that's familiar to anyone who uses Word or Excel, you can quickly start creating your Access application.
That may sound like a good thing, but it's a double-edged sword. Often times, those new to Access jump in and start treating it like it's another version of Excel. Excel is an amazing tool, and it has some database-ish features. But it's not a true relational database, like Access is. If you're going to use Access exactly the same way you'd use Excel, what's the point?
If order to gain the benefit of Access, you need to learn about relational database concepts. This is a big topic, and you can find volumes written on the subject on Google and on Amazon. But for today, I'm going to give you a nudge in the right direction, so that you can move forward on your Access learning journey.
How Many Tables?
The question we'll look at today is the number of tables you'll need in your Access database. As I said, those familiar with Excel will naturally start designing Access tables that are similar to Excel worksheets. Sometimes that's fine, most times its not.
I recently created a simple inventory database for a client, and they started our conversation on this project by telling me that they envisioned a database with three of four tables. In their minds: a Product table, a Transaction table, an Inspection table, and maybe a user table. Two months later, I presented them with a database with 22 tables. This is a typical scenario. If you're not familiar with relational databases, it's easy to envision a flat structure with few tables. But in reality, to fully realize the benefits of a relational database, you'll probably want more.
Don't be put off by the thought of creating 22 tables. You'll find that creating tables is quick and easy, and many of them will end up being low-maintenance tables.
Example
Let's get into an example to demonstrate what I'm talking about.
I'll use an Inventory database as our example. Let's see what my Inventory database might look like in Excel:
Without an understanding of relational database concepts, it might seem obvious to you that this is just a single table in Access. But at a glance, I see at least four tables here - probably more!
Call me crazy if you want to, but I'm right about this.
How do I know?
20+ years of evaluating client's data tells me that repetition is a recipe for a hot mess in your tables. See that the part description has been typed into Excel over and over? Every time they have an entry for 12-345, they have to type Widget. Not Wigdet. Not Widge. They've got to type it correctly every time.
Same story with the lot number. 23-0001 is not the same as 23-001, nor is it the same as 23 - 0001. When it comes to computers, they are stupid sometimes, right? Humans know that all three of these are probably references to the same lot number, but the computer doesn't know that.
Where else do you see repetition? In the Location column.
This is where relational databases earn their pay. If we set up our relational database correctly, the user will only type each product description, lot number and location once. Then they'll use drop-down menus to choose those items.
Here's how I'd design this particular database:
See those lines between the tables? Those are relationships, and they make the magic happen in relational databases. They indicate how data in each table is related to data in other tables. For example, the line between tblParts and tblLots indicates that the PartNumber is shared between the two. If you look at the symbols on the line, you'll see a "1" on tblParts, and an infinity symbol on tblLots. That means that for each ONE record in tblParts, there can be an infinite number of related records in tblLots.
Let's see what the data in these tables looks like:
Although you see part 12-345 more than once in the tblLots table, you don't see "Widget" more than once. That's because any time we need to know the part description, we can take the PartNumber from tblLots and look it up in tblParts. There's no need for us to enter the description more than once.
Even though part 12-345 is in tblLots more than once, I didn't type it more than once. Because of the relationship I created between the tables, Access gives me a drop-down list of all parts in tblParts, and I simply choose one of those. No typing, and better still, no typos.
Is this more complicated than Excel?
Yes it is, but that's exactly the point. Once you outgrow Excel, you start to think about databases, and Access is a great place to start. Not that you're going to stop using Excel. In fact, Excel integrates well with Access databases, allowing you to continue to analyze your data there. See my prior blog post on this topic here.
To sum things up, if you're wondering if you need one, two, or more tables in Access, ask yourself if you have data that's being entered repeatedly. If so, you probably need more than one table.
To be honest, if you carefully consider how many tables you need, and the answer comes back "one", you're probably wrong. Almost all databases will benefit from multiple tables. Peruse some of those Google or Amazon resources on relational database concepts for further info.
Or call me. I'll set you straight.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT 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.
#northportsolutions #learningneverstops #microsoftaccess #msaccess #database #dataanalysis #smallbusiness
© 2023 North Port Solutions, LLC. All right reserved.