From the course: Access Essential Training (Office 365/Microsoft)

Building Access databases - Microsoft Access Tutorial

From the course: Access Essential Training (Office 365/Microsoft)

Start my 1-month free trial Buy for my team

Building Access databases

“

- The process of working in access is a lot like playing with building blocks. There's a number of different elements that you can select and customize and hook together in order to create something new. Today, we are building a home for our valuable data. Let's start by asking what exactly is an access database. An access database is not just a place to store your data, access provides mechanisms for creating an entirely customized application that is specifically designed to meet the unique qualities of your data. You can think of it like visiting a classic tailor shop to get a sharp new custom suit made. The tailor will take your measurements and allow you to pick fabrics and styles and then put it all together into a suit that is specifically fitted just for you and your tastes. An access database is essentially a custom-tailored suit made just for your data. With access, you get to make a custom fit tool that organizes, validates, and stores your data in ways that help protect it, and it allows you to work with that data in new flexible ways that allow you to perform statistical and observational analysis and share your findings in detailed reports. Access provides the tools that allow you to do a number of useful things. First, it can efficiently process data and help you move data in or out of the database. It'll actively monitor and maintain the quality of the data and ensure that it remains consistently entered and valid. Analysis tools will help you gain insights into trends, areas of opportunity, and performance metrics through its powerful querying capabilities. You can collect information and construct organized and detailed reports to help guide informed-decisions. And finally, task automation will 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, an access database uses five main components. These components are collectively called database objects, and each object 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 permanent structured home for your information. Tables organized data about the different entities that you're storing information about in rows and descriptive attributes or fields are defined by each column. The next object are queries. Queries are literally questions that you have about the data. Queries, scour through your data tables to find answers to a question, and then return the answer as a filtered or a calculated list of records that look and function just like a table. These results can be used as the basis for a report or for further processing. Forms provide the main user interface for your database and are highly-customizable to your specific needs. They hold buttons for moving from task-to-task, text entry areas to help users add data to the correct location in the tables, and they can collect information for customized queries or reports. You've probably used lots of forms on the internet for filling-in shipping information when you order items online or when you fill-in a product survey. Forms and access work the same way and will help walk users through the process of data entry and other database tasks. Reports gather records from a table or query, and then format them into an organized page layout that creates consistent presentations that are easy to read. Reports are ideal for printing or exporting the results of an analysis to share with colleagues, such as a quarterly sales performance report or an end-of-year financial review. Finally, macro objects are set of programmatic instructions that you'll create that tell the database how to function when you click on a button or interact with a form or other object. They can help automate some fairly complex or routine tasks, so that they're always executed in exactly the same way each and every time. It is the interaction of these five components that make the database function. For instance, a form might help you enter data into a table. A query will then filter the records in the table to a specific subset and pass that filtered result to a report. The report will format that data for printing, and finally, a macro will take that report layout, output it to a PDF file, and automatically email it to the executive team in time for their weekly review meeting every Monday morning. And that's just one example of a data workflow and access that passes through each one of these access objects. So these are the five main components of an access database. Each one has a specific role to play in how your data is managed. Throughout this course, we're going to take a look at each component one at a time, so that you can understand how to create them and learn how to hook them all together into a complete custom-tailored application for your data.

Contents