In this movie, Adam explains the role of an Access database and how it can often be a better choice for your data storage needs than a simple Excel spreadsheet. By leveraging the organized structure of a relational database, you ensure that your data is consistent and reliable, and that the information that you retrieve out of it is actionable.
- [Voiceover] Many people are content working with their data in Excel and don't realize the benefits that a move into Microsoft Access can bring about. Before we get into seeing how and Access database works we should explore the role that Access plays in helping you organize your data and why it's often a superior choice over the classic Excel spreadsheet. So what is Access? Access is a relational database program that will allow you to build a fully customized wrapper that surrounds your data and controls your interaction with it. This means that Access provides a system that not only stores your data, but helps you collect and input your data in consistent and predictable ways.
Further, it allows you to retrieve that data in a variety of useful formats. It's the fine control that you gain over input and output that often makes a database a better choice over storing your data in a simple spreadsheet format such as Excel. An Access database moves beyond a spreadsheet in a few other key ways. First, Access provides the tools to create your own interface, which can clarify and organize the tasks that your database's users will need to accomplish. If a user of the database needs to input a client before they can log the services that the client purchased, the database interface will provide guidance and the proper sequence in which the data should be entered.
In doing so, Access essentially is a program that allows you to create a custom tailored application. This is useful since no two users will have the same requirements of their data. Access is used by a diverse range of groups that run the gamut from scientists and non-profits, to small and medium sized businesses. It can help track everything from inventory to scientific measurements, employees, tasks, or donations. Access provides a flexible framework that can be customized to your data's unique needs. Second, Access' structural design helps protect your data throughout the collection, storage, and retrieval life cycle.
In the collection phase, you'll want to make sure your data is trustworthy and clean, and consistently and completely entered. An Access database can ensure that your data meets certain validation requirements in order to be saved. With those rules in place you'll know that the data that's present can be trusted. In the storage phase, Access uses the concept of many small, related tables to provide a home for your data. This concept is what puts the word relational in a relational database. By using the time tested structures of relational databases, Access streamlines data entry and minimizes the potential for data discrepancies by referencing previously entered information.
For instance, if a regular customer saves their credit card number with us, we can simply refer to that number for all future invoices rather than have to re-type it in for each individual order. Not only does this save us time entering the invoice details, it removes the possibility of mistyping the credit card number each time. Finally, Access has built-in components that will aid in the data retrieval process and make it much more efficient. Since the entire point of storing data is so that it can inform decision making and provide insights into trends, you'll want to be able to easily sort through, filter, and summarize your data into useful reports.
Once these reports are created, you can re-run them at any point to get up to the minute snapshots of the current state of your organization. This all leads to explaining why a database is often a better choice over an Excel spreadsheet. The process of collecting, storing, and retrieving data is inherently messy, so having a well structured system in place will ensure that you're able to trust the data going in and gain insight and information from the data coming back out. Access provides the tools necessary to create such a system around your data. And it's a great choice, even for people with no prior training in database design or theory.
If you need a deeper dive into the topics discussed, check out Adam's Access 2016 Essential Training course.
- Loading a premade template database
- Exploring the interface
- Defining data types
- Creating tables
- Defining table relationships
- Editing forms
- Creating queries and reports
- Editing reports in Layout view