Introduction

Welcome! Normalize Tables & Set Relationships

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Expert Level 2. In this course we will start by learning about database normalization to ensure your tables and relationships are set up correctly. We will discuss defining explicit relationships, setting up referential integrity, and handling cascade updates and deletes. I will show you how to set values across multiple forms, such as opening a contact form from a customer form and setting the customer ID automatically. This course builds on concepts from Level 1, and is designed for Access 2010 but is also suitable for Access 2007 and 2013 users.

Navigation

Prev: X02
Next: Lesson 01

Keywords

Access Expert, database normalization, referential integrity, cascade update, cascade delete, relationships window, explicit relationships, subdatasheets, building relational tables, forms value transfer, combo box default value, macros, automation, Visua

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access 2010 Expert Level 2, brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

In today's class, we are going to start out by learning more about database normalization. This is the process of making sure your tables and the relationships between them are set up properly.

Next, you will learn how to define explicit relationships in your database, and we will learn how to set up something called referential integrity. We will learn about cascade updates and deletes. Cascade deletions are handy if you want to delete a parent record and have all the children deleted automatically.

We will learn how to set values across multiple forms. For example, we will learn how to open up the contact form from the customer form and set the customer ID on the contact form automatically, so you do not have to pick the customer from a list.

This class follows Microsoft Access 2010 Expert Level 1. If you have not taken Level 1 yet, I strongly recommend you take it before continuing with this class. Level 1 covers all the foundations of relational database concepts. There is a lot of information in that class about key fields, ad hoc relationships in your queries, setting up relational combo boxes, and lots more. If you start today's class without having Level 1, you will be missing a lot of information that is required for a proper understanding of the material in today's class.

This course is designed to be used with Access 2010. If you are using 2007, you should not have any problems following along as the two versions are very similar. From what I have seen, Access 2013 is also almost exactly the same. This lesson should work fine with Access 2013 as well.

If you are using Access 2003 or earlier, you want to go to my website and download AC202. That is the older version of this class. However, there is a lot of new material covered in today's class that I did not cover in Access 2010. For example, the whole first lesson on normalization is about 20 minutes worth of material that was not in my old Access 2003 classes.

Now, I strongly encourage you to build the database that I build in class by following along with me, watching the video, and building the database yourself. However, if you are lazy like me and just want to download it, you can go to accesslearningzone.com/databases and you will find a copy of today's database up there.

My courses are broken up into four groups - Beginner, Expert, Advanced, and Developer. My beginner courses are for novice users who have little or no experience with Microsoft Access. They are designed to give you an overview of the basic features of Access and cover just what you need to know to be productive.

The expert classes are designed for more experienced users who are already comfortable with Access. Expert classes go into a lot more depth about each topic than the beginner classes do and will cover more functions, features, tips, and techniques for power users.

Once you have mastered the expert classes, move up to the Advanced lessons. You will learn how to work with macros, automation, and many more advanced features that really add enhanced functionality and professionalism to your databases.

Finally, my developer level courses are designed to teach you how to program in Visual Basic for Microsoft Access. This will allow you to create the most advanced databases possible and unlock the true potential of Microsoft Access.

Each series is broken down into different numbered levels, starting with Level 1. Each subsequent level teaches you new and different topics in Microsoft Access, building on the lessons learned in the previous classes. Once you have finished all the beginner classes, move up to the expert series, then the advanced, and finally the developer lessons.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection. Here you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students may have made. I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well.

If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from this class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum. Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now, let's take a closer look at exactly what is covered in today's class.

In lesson one, we are going to learn about proper table normalization, which is a key factor in building good databases.

In lesson two, we are going to learn how to define explicit database relationships and set up referential integrity.

In lesson three, we are going to learn about cascade, update, and delete. We will do a little more work with the Relationships window and sub-data sheets.

In lesson four, we are going to make a button to open up our contact form and show just the contacts for the current customer and learn how to get values from different forms. So the customer combo box can default to the current open customer when you enter a new record.

Intro

Quiz

Q1. What is database normalization?
A. The process of ensuring your tables and their relationships are set up properly
B. The act of adding security features to your database
C. Creating attractive forms and reports
D. The process of converting a database from one version to another

Q2. What does referential integrity ensure in a database?
A. That all data is stored in one large table
B. That relationships between tables remain consistent
C. That only one user can access the database at a time
D. That your forms and reports are automatically updated

Q3. What is a cascade delete?
A. Deleting only a single parent record at a time
B. Deleting child records manually after deleting a parent
C. Automatically deleting all related child records when a parent record is deleted
D. Archiving data instead of deleting it

Q4. How can you open a contact form from a customer form and set the customer ID automatically?
A. By using macros only
B. By manually entering the customer ID each time
C. By setting values across multiple forms
D. By exporting to Excel

Q5. Which course level should you complete before taking the Expert Level 2 class?
A. Beginner Level 2
B. Advanced Level 1
C. Expert Level 1
D. Developer Level 1

Q6. Which versions of Access are compatible with this course?
A. Only Access 2010
B. Access 2007 and 2010 (and 2013 is also almost the same)
C. Only Access 2013
D. Only Access 2003

Q7. What topics are covered in the first lesson of this class?
A. Creating advanced reports
B. Proper table normalization
C. Writing SQL statements
D. Designing database security

Q8. What is a suggested way to learn the material from the videos?
A. Apply to other projects immediately
B. Only read the course transcript
C. Watch the lesson completely, then follow along by building the sample database
D. Skip to advanced lessons first

Q9. What feature lets you see questions and answers from other students?
A. Database Relationships window
B. Built-in Access Help feature
C. Student forums on the website or next to the class video
D. Print preview mode

Q10. What do advanced Access courses cover according to the video?
A. Basic database concepts
B. Intermediate forms and queries
C. Macros, automation, and enhanced functionality
D. Typing speed skills

Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-B; 7-B; 8-C; 9-C; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from the Access Learning Zone is part of my Microsoft Access 2010 Expert Level 2 course. I'm Richard Rost, your instructor.

In this lesson, we start by getting into database normalization. This is a critical process to ensure your tables and their relationships are designed correctly. I will show you how to structure your data so that everything is organized and efficient, preventing redundancy and making updates easier in the future.

Then, we move on to establishing explicit relationships within your database. I will walk you through setting up referential integrity, which is a safeguard to make sure your data remains consistent. You'll also learn about cascade updates and deletes. For example, if you delete a parent record, cascade deletes will automatically remove all related child records. These features help maintain your database's accuracy and save you a lot of manual work.

Next, I will cover how to pass values across multiple forms in your database. For instance, you'll learn how to open up a contact form right from a customer form and automatically set the customer ID, so there is no need to pick the customer from a drop-down list each time.

Before jumping into this Expert Level 2 course, it is highly recommended that you complete Expert Level 1 if you haven't already. That first level lays a solid foundation of relational database concepts. It covers essential topics like key fields, ad hoc relationships in queries, setting up relational combo boxes, and more. Without this groundwork, many of the concepts we cover today may be harder to grasp.

This course is built for Access 2010, but you should have no trouble following along if you use Access 2007 or 2013 since the layout and functions are nearly identical in these versions. If you are still using Access 2003 or earlier, you'll want to look for my AC202 course which matches that version, though I do cover quite a bit of new content in the 2010 course. For example, the entire normalization lesson is unique to the newer version and runs about 20 minutes long.

I always encourage students to build the same database I build during the lessons. The best approach is to watch the video and create your own database right alongside me. But if you prefer, you can download a completed copy from accesslearningzone.com/databases.

My courses are grouped into four main tracks: Beginner, Expert, Advanced, and Developer. The Beginner track introduces novices to the basic functions of Access to get you productive right away. The Expert series, like this course, is for users who already know their way around Access and want to go deeper with more sophisticated features, tips, and techniques.

After mastering the Expert series, you can move up to the Advanced lessons, where we cover things like macros and automation to help you add polished, professional functionality to your databases. For those looking to go even further, my Developer courses teach you how to program in Visual Basic for Microsoft Access, unlocking its full potential.

Each track is divided into numbered levels that build on one another. Start with Level 1 in any series and continue upward as you complete each lesson. When you finish all the Beginner classes, move to Expert, then Advanced, and finally Developer.

Beyond the standard courses, I offer specialized seminars on topics like creating web-enabled databases, building forms and reports that look like calendars, enhancing database security, managing images and attachments, generating work orders, handling accounts payable, learning SQL, and much more. Details on these seminars are available at accesslearningzone.com.

If you have questions about today's material, you can post them in my student forums. If you're using my video player or the online classroom, a forum window should appear next to the lesson, provided you're connected to the internet. There, you can see questions from other students, my answers, and community comments. It's always a good idea to read through these before and after each lesson and to post your own questions as needed.

Anyone not using the online player can still access the forums later by visiting accesslearningzone.com/forums.

To make the most of this course, I suggest watching each lesson all the way through once before trying anything on your own. After you finish the video, go back and follow along with the steps, building the sample database as you learn. Focus on mastering these lessons first before applying the ideas to your own projects. If you run into trouble or something does not make sense, simply watch the lesson again or ask for help on the forum. The most important thing is to keep an open mind. Access may seem challenging at first, but it becomes much easier once you're familiar with it.

Here is a quick outline of what you'll learn in this class:

In lesson one, we focus on table normalization, which is key to designing stable and efficient databases.

Lesson two is about defining explicit database relationships and setting up referential integrity.

In lesson three, we cover cascade updates and deletes, working more with the Relationships window and sub-data sheets.

Lesson four shows you how to make a button that opens the contact form and filters it to show only the contacts for the currently selected customer. You will also learn to pass values between forms, so the customer combo box defaults to the current customer when adding new records.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.

Live long and prosper, my friends.

Topic List

Database normalization concepts
Setting up proper table relationships
Defining explicit relationships in Access
Establishing referential integrity
Using cascade update and delete options
Working with the Relationships window
Managing sub-data sheets
Creating a button to open the contact form
Filtering contacts by current customer
Passing values between multiple forms
Defaulting combo boxes to current values

Article

Welcome to this tutorial on Microsoft Access 2010 at the expert level, where we will focus on taking your database design and functionality skills to the next stage. Whether you have already completed a beginner or introductory course or have some familiarity with Access, this guide aims to help you better understand key concepts that lead to robust and reliable databases.

One of the most important concepts in database design is normalization. Database normalization is the process of organizing your tables and their relationships to ensure data is stored efficiently, avoid unnecessary duplication, and maintain consistency. The goal is to break your data down into logical, related tables, each dedicated to a specific subject or entity, such as customers, orders, or products.

For example, imagine you are building a database to track orders for a small business. Rather than storing all your customer and order information in a single table, you would want a Customers table with all the customer details and an Orders table to store each order made by those customers. This approach helps prevent data anomalies, makes updates easier, and helps your database perform better as it grows.

Once you have your tables set up, it is important to define explicit relationships between them. This means telling Access exactly how tables are connected, usually through key fields. A good example is linking a CustomerID field in the Customers table to the same CustomerID field in the Orders table. This relationship ensures that every order is associated with the correct customer.

In Access, you can define these relationships and enforce something called referential integrity. Referential integrity ensures that your data remains consistent, meaning you cannot have an order referencing a customer who no longer exists in the Customers table. When you set up referential integrity, Access will prevent you from performing actions that would leave your data in an inconsistent state.

You will also encounter options like cascade updates and cascade deletes when setting up relationships. Cascade update means if you change a value in the primary key field of the parent table (for example, the CustomerID in the Customers table), Access will automatically update all related records in the child table (such as Orders) to match this change. Cascade delete is useful if you want to delete a record from the parent table and automatically remove all associated records in the child table. For example, if you delete a customer, all their orders will be deleted as well. This can make data management easier but should be used carefully to avoid accidental loss of important data.

As you build more interactive databases, you may want to set values across multiple forms to streamline your workflow. For instance, if you have a Customer form open and you wish to enter a new Contact for that customer, you can open the Contact form with the CustomerID already filled in. This saves time and ensures accuracy, since you do not have to select the customer again.

To accomplish this in Access, you can use VBA code to open one form from another and pass values between them. For example, if you have a Customer form open and want to open the Contact form with the current CustomerID defaulted, you might add a button with the following code:

DoCmd.OpenForm "ContactForm", , , , acFormAdd
Forms!ContactForm!CustomerID = Me!CustomerID

This code opens the Contact form as a new record and sets the CustomerID field in the Contact form to match the currently open customer. This technique ensures new contacts are automatically associated with the correct customer without manual intervention.

It is essential to have a solid understanding of fundamental relational database concepts before moving into these more advanced features. If you are unfamiliar with terms like key fields, queries, and relational combo boxes, I highly recommend reviewing those basics first, as they are the foundation upon which these advanced skills are built.

Microsoft Access 2010 is very similar to Access 2007 and 2013, so most of what is covered here will work in those versions. However, if you are using Access 2003 or earlier, some features described may not be available, and you may need to consult materials specifically designed for those earlier versions.

As you learn, I encourage you to actually build the same sample databases described in this tutorial. Following along by creating the tables, forms, and relationships will help reinforce the concepts and give you hands-on experience. Do not be tempted to simply read through the tutorial and apply the concepts to your own projects right away. Practice first with the same examples so you are confident with the process and can troubleshoot any issues more effectively.

Access training is typically broken down into different skill levels. Beginner lessons introduce you to Access and cover essential features so you can quickly become productive. Expert topics, like those in this guide, dig deeper into sophisticated features, best practices, and techniques for users who are already comfortable with Access basics. Advanced lessons move on to automation, macros, and other complex tools that let you extend your databases even further, adding functionality and a professional touch. Finally, developer-level topics introduce Visual Basic programming for Access, which unlocks even more power and flexibility by allowing you to automate processes, create custom controls, and build advanced solutions that go far beyond what is possible with Access alone.

For those interested in learning about specific topics, there are also seminars available. Some examples include creating web-based databases, building calendar-style forms and reports, securing your database, working with attachments and images, tracking work orders, managing accounts payable, and writing SQL code for advanced queries.

As you go through this material, you will likely have questions. It is a good idea to read through forums and discussions by other students, as many common questions are answered there, often with valuable insights and solutions. If you get stuck, do not hesitate to ask questions in these forums or reach out for help.

To get the most out of your learning, start by reading through each lesson or section without immediately jumping into Access. Absorb the content first, then carefully follow along by replicating the exercises in your own database, step by step. If you encounter challenges or confusion, revisit the explanations, and try working through the steps again. Approach each topic with an open mind. Access may seem a bit daunting at first, but as you build experience, its power and ease of use will become clear.

To recap, this tutorial has walked you through the essentials of database normalization: breaking down your data into related tables, establishing explicit relationships, and enforcing referential integrity to maintain data consistency. You have learned how to use cascade updates and deletes to manage related records and how to streamline workflow by passing values between forms using VBA code. By building sample databases as you learn and continually practicing the methods described, you will gain the skills needed to design efficient, reliable Access databases that are easy to use and maintain.

Primary Topics

normalization, table design, relationships, referential integrity, cascade updates, cascade deletes, form interaction, value passing

Secondary Topics

relational combo boxes, subdatasheets, course structure, accessing forums