Microsoft Access 2021: Organizing Records

Name: Microsoft Access 2021: Organizing Records
Author: John Varan

Learn how to split and organize records into multiple tables.

Preview the first lesson free — get full access to all 4 lessons.

Course: On-Demand

Hello, welcome to Knowledge City's course on Microsoft Access 2021, Intermediate. My name is John Varan, and in these lessons, you will learn about organizing records in Microsoft Access. In this lesson, I will show you about splitting records into tables. When designing a database, often you will be faced with having to split an existing table into one or more smaller tables. By far, the most common reason for this is during the normalization process, as most of the normalization forms require some sort of splitting tables. Aside from normalization, however, there are several other reasons why you might need to split a table. The first is that the table has simply gotten too big. When we say too big, we're referring to the number of rows in the table. If a table grows and grows, it will eventually end up affecting performance. Large tables can slow down queries and reports. One way to address this is to split the table into two or more tables. For example, a large table of customers could be split into two tables based on the first letter of the customer's last name, A through N in one table and M through Z in another table. Or if the table is truly large enough, you could even go to having a table for each letter. Keep in mind that doing so, while improving performance of queries and reports, can present a maintenance headache. Any form, query or report that works with those tables will have to be modified in order to account for the existence of multiple tables, and will have to determine which of the tables it needs to look at. You will have to decide if this increased cost is worth it or not. A second reason you may need to split a table is for archival purposes. If you have data that you need to retain but don't plan on accessing except on very rare occasions, this is the best approach. A good example is a table that stores financial transactions. You may regularly only work with the most recent few years worth of financial records, but you still need to retain old records just in case. In this case, you would split data off to a new table based on the transaction date. This requires much less rework than the previous case, because all the forms, queries and reports can continue to use the normal table just as they had been. The archive table will still be there for occasional use through manual queries or one-off reports. A third reason for splitting a table is for the purpose of creating an audit trail. This situation is actually less splitting a table as it is creating a new table. An audit table is used to track activity in its corresponding main table. Whenever the main table has a change to a row, a new row would be inserted into the audit table stating that a row was added, updated or deleted along with a timestamp of when this occurred and a log of which user performed the action. This would create a complete audit trail of all actions performed on the database. This scenario requires the most rework, because you will need to add code to update the audit table for each and every main table and create a new audit table for every main table. This will effectively double the number of tables in your database. Creating an audit trail can be a lot of work, so it is not recommended unless you have a compelling business reason to do so. A final situation that might require a split in your data is one that doesn't actually split an individual table, but rather splits your entire database into two databases. The main reason to do something like this is for security. It works like this. You would create a second database and move some of the tables into that one. The two databases can be thought of as your front end and back end databases. The front end database is the one you share with all your users and the one they will have access to make changes to. The back end database would be the one with more restricted access which you would normally not share. It would contain data that the end user will see and use but cannot make changes to. For example, a student database could keep the list of students and their information such as names of parents and addresses in the back end database. All the current courses and grades are kept in the front end database. This way teachers could make changes to classes and grades, but would be unable to add new students or delete students. That task would be reserved for admin staff using the back end database. Thanks for watching. Stay tuned for the next lesson where I will show you about partitions.

Intermediate Provider : John Varan 4 Lessons · 24m in English

Course Description

Microsoft Access 2021 allows you to store huge amounts of data. Your database may contain many years’ worth of information, compiling recent data for everyday use as well as old records for future reference. When storing all of this information in the same database, how do you keep it all organized? Implementing the right design tools at the start can allow you to filter and search your data later, so you always know where to find the information you need.

In this module, you will explore various techniques for organizing data. We will go over how to design efficient tables, group and visualize data, and other organizational methods. You will learn when to split tables, and how to organize them to facilitate better filtering and searching. By learning how to create row numbers, you will be better able to decide how to order and split your data. You will also learn about partitioning tables, which allows you to group and visualize your data in interesting ways. All of these techniques will help you determine how to shape your data for your own organization needs.

What You'll Learn

Design tables and databases for versatile filtering and searching
Create and work with row numbers to order and split your data
Use partitions effectively to group and visualize your data
Split records into multiple tables to keep data organized
Apply organizational methods such as designing efficient tables and grouping data

Key Takeaways

Microsoft Access 2021 lets you store large amounts of data, including recent data for everyday use and old records for future reference.
Implementing the right design tools at the start allows you to filter and search your data later so you always know where to find the information you need.
Creating row numbers helps you decide how to order and split your data.
Partitioning tables allows you to group and visualize your data in interesting ways.
These organizing techniques help you shape your data to fit your own organization's needs.

Frequently Asked Questions

What does this Microsoft Access 2021 module cover?

It covers techniques for organizing data in Access, including designing efficient tables, grouping and visualizing data, creating row numbers, splitting records into multiple tables, partitioning, and versatile filtering.

What will I be able to do after this module?

You will be able to design for versatile filtering, create and work with row numbers, use partitions effectively, and split records into multiple tables.

Which lessons are included in this module?

The lessons are Splitting Records Into Tables, Partition, Row Numbers, and Versatile Filtering.

What skills does this module help build?

It builds skills in information organization, organizational skills, data access, data management, Microsoft Access, and Microsoft Data Access Components.

When should I split tables according to this module?

The module teaches you when to split tables and how to organize them to facilitate better filtering and searching, using row numbers to help decide how to order and split your data.