From the course: Access 2016 Essential Training

Key database concepts

From the course: Access 2016 Essential Training

Start my 1-month free trial

Key database concepts

“

- Before we begin in Access I think it's important to outline a few key concepts about what a database is and to find some of the keywords that we'll be hearing about throughout the course. First, what exactly is an Access Database? Access is a collection of tools that allow you to efficiently process data moving it in and out of the database. Maintain the quality of the data and ensure that it remains consistently entered and valid. Gain insight into trends, areas of opportunity, and performance metrics through a series of analytical processes. Construct organized and detailed reports to help guide informed decisions, and 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 roll to play in managing your data. The first object is a table. Tables are where the data lives. They provide a structured home organized into fields and records. The next objects are queries. Queries are literally questions that you have about the data. Queries scour through the data tables to find the answers to a question, and then return the answer as a filtered or calculated list of records that look and function just like a table. Forms provide the interactive framework for your database. They hold buttons for moving task to task, text entry fields to help add data to your tables, and input areas to help define the parameters for a query or a report. Reports gather records from tables or queries and then format them for a standardized page layout ready to be printed, exported, or emailed to colleagues. Finally, macro objects are sets of programmatic instructions that you will create that tell the database how to function when you click on a button or when you interact with a form or other object. They can automate some fairly complex tasks. It is the interaction of these five main components that make the database function. For instance a form might help you enter some data into a table. A query will then filter the records to a specific subset and then a report will format that subset of records for printing. To create our objects we'll use multiple working modes called views. These can be grouped into two categories. The standard view is the one that you'll use when you are 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 the information contained 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 an Access database. Take a look at the following problematic table of contact phone numbers. In this table you'll notice we have first and last names and they're repeated over and over each time there's an additional phone number for each individual. We can structure the data a little bit differently, but that introduces a new problem. Now we have a bunch of blank cells where individuals don't have a contact number of each type. Further, this table won't grow very well. What happens if we wanted to add in 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. 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 a common field called keys. Using the exact same data, we can organize our phone numbers like this, where we have one table that's just about our people, and another table that's just about phone numbers. By linking the two tables together 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 table by adding additional columns. So, the main benefits of a relational database? One, they remove the 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 five types, hooking them together to build a relational database that will efficiently store data, return answers to your questions, perform analysis, and generate reports.

Contents