From the course: Access 2019 Essential Training
Database concepts
- [Narrator] Before we begin in Access, I think it's important to outline a few key concepts about what a database is and define some the keywords that we'll be hearing throughout the course. First, what exactly is and Access database. An Access database is not just a place to store your data, it provides 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 collection of tools that allow you to efficiently process data, moving it in or out of the database, maintain the quality of the data, 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 work-flows. Simply put, a well-crafted Access database can give you way more that 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 has a unique role to play in managing your data. The first object is a table. Tables are where the data lives. They provide the structured home where individual records are organized into rows and descriptive attributes or fields 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 to find answers to a question and they return the answers as a filtered or calculated list of records that look and function just like a table. Forms provide that interactive framework for your database. They hold buttons for moving from task to task, text entry areas to help add data to the correct locations in the tables and can collect parameters that provide input for customized queries and reports. Reports gather records from a table or query and then format them to a standardized page layout ready to be printed, exported or emailed to colleagues. Finally, Macro objects are a set of programmatic instructions that you'll create that will 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 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, a query will then filter the records in that table to a specific subset and the 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 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 all of our objects when we're working with the 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 an Access database. Let's take a look at the following problematic 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 is an additional contact phone number for each person. Data redundancy is a primary issue that databases are designed to efficiently handle. So there's room for improvement on how we are storing this information. We can structure this data table differently but this approach introduces a new problem. Now we have lots 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 if we want to add a fax number to one individual or a second office number? We would have to alter the structure of the table and add additional columns to accommodate those type 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 through 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 an employee ID, 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 benefit of a relational database, is one, they remove redundant information, and two, they eliminate the need to restructure the data tables in order to accommodate growth.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.