Microsoft Access can help users organize their data in ways that a flat file spreadsheet simply can’t match. In this video, Adam Wilbert introduces the components of an Access database and describes how they work together to make relational databases accessible.
- [Instructor] Before we begin in Access, I think it's important to outline a few key concepts, about what a database is. and define some of the keywords that we'll be hearing throughout the course. First, what exactly is an Access database? An Access database is not just a place to store your data, it provides the mechanisms for creating a custom application that wraps the entire interaction between you and the data. This helps streamline data entry and retrieval tasks. Access is a collection of tools that allows you to efficiently process data, moving it in or out of the database. It allows you to maintain the quality of the data and ensure that it remains consistently entered and valid.
With Access, you can also gain insights into trends, areas of opportunity and performance metrics, through a series of analytical processes, and construct organized and detailed reports to help guide and form decisions. Finally, Access allows you to automate tasks, to help prevent processing errors and speed up workflows. Simply put, a well-crafted Access database can give you way more than what you put into it. To do all of that, Access databases use five main components. These components are collectively called Database Objects. And each has a unique role to play in managing your data.
The first object is a Table. Tables are where the data lives. They provide a structured home, where individual data records are organized into rows and descriptive attributes, or fields, are made up of columns. The next objects are Queries. Queries are literally questions that you have about the data. Queries scour through your data tables, and find the answers to a question, and then return the answer as a filter or calculated list of records, that looks and functions, just like a table. Forms provide the main user interface for your database, and are highly customizable to your specific needs.
They hold buttons from moving from task to task, text entry areas to help users add data into the correct location into the tables, and can collect input for customized queries or reports. Reports gather records from a table or query, and then format them into and organized page layout, ready to be printed, exported, or emailed to colleagues. Finally, Macro Objects are sets of programmatic instructions, that you can create, that'll tell the database how to function when you click on a button, or interact with a form, or other object. It can help automate some very complex tasks. It's the interaction of these five components, that make the database function.
For instance, a form might help you enter data into a table. And then a query will then filter the records in the table into a certain subset, and then a report will format that subset of records for printing. To create our objects, we'll use multiple working modes, called Object Views. These can be grouped into two different categories. The Standard View is the one that you'll use when you're working with your data. Depending on the object, we'll use Datasheet, Form, Report, or Print Preview views, which will display our objects when we're working with information stored within the database. The other views are used when creating or modifying the structure of the objects.
The Design, Layout, and SQL views all allow you to define how the database functions. Finally, it's important to understand the relational structure of and Access database. Let's take a look at the following table of contact phone numbers. In this table, you'll notice that the first and last names are repeated over and over, each time there's an additional contact phone number. Data redundancy is a primary issue that databases are designed to efficiently handle, so there's room for improvement on how we store this information. We can structure this data table differently. But, this approach introduces a new problem.
Now we have loads of blank cells, where individuals don't have a contact number of each type. Further, this table isn't going to grow very well. What happens is we want to add a fax number for one individual? Or a second office number? We would have to alter the structure of the table, and add additional columns, to accommodate those types of changes. Users of a database should be able to input data, even in fringe cases, without having to alter the design. A relational database solves both of these problems. Unlike a flat file database, such as an Excel spreadsheet, Access organizes data into multiple tables that connect to each other through common attribute fields called Keys.
Using the exact same data, we can organize our phone numbers like this, where we have one table that's just about people, and another table that's just about phone numbers. By linking the two tables together with a key, in this case, the Employee ID number, we can accommodate growth, and prevent inefficiencies in our storage. We can easily add new people, or additional types of contact numbers, without altering the structure of the tables, by requiring additional columns. So the main benefits of a relational database? One, they remove redundant information, and two, they eliminate the need to restructure the data tables, in order to accommodate growth.
We'll expand on all of these concepts throughout this course. And we'll be creating several objects, in each of the four main types: Tables, Queries, Forms, and Reports. We'll hook all of them together, to build a relational database, that'll efficiently store our data, return answers to our questions, perform analysis, and generate reports.
- Navigating the interface
- Creating tables
- Defining data types
- Defining table relationships
- Sorting and filtering table data
- Editing forms
- Creating queries and reports
- Maintaining a database