Introduction

Welcome! Databases: Keys & Relationships

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

Welcome to Microsoft Access Expert Level 1. In this course we will cover relational database concepts, including what relational databases are, why they are important, and how to set up tables with primary and foreign keys. We will discuss defining relationships between tables, different join types in queries, and creating relational combo boxes for selecting values from one table to store in another. This course builds on Beginner Level 9 and is intended for those familiar with basic Access features. We will also review the structure of my Access courses and how to get the most out of them.

Navigation

Prev: X01
Next: Lesson 01

Keywords

Access Expert, relational database, primary key, foreign key, table relationships, relational tables, queries, join types, combo box, customer to contact relationship, lead source table, shipping method table, power user techniques, forums, macros, Visual

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

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

Today's lessons are going to focus on relational databases. We are going to learn about relational database concepts, what a relational database is, and why they are important. We will learn how to set up relational tables by adding key fields, primary keys, and foreign keys to our tables. We will learn how to relate our tables together using relationships inside of queries. We will talk about different join types in queries, and we will learn how to set up relational combo boxes where you can pick a value from one table to store in a second table; for example, picking a customer to store that in the contact table.

This class follows Microsoft Access 2010 Beginner Level 9. I do consider Beginner Level 9 to be a prerequisite for today's class because in my beginner series I focused on non-relational database techniques. The database that we built in the beginner series did not relate multiple tables together like you should. In Beginner Level 9, I showed you bad database techniques. I showed you how to set up the contact management system wrong using flat file tables where the contact and customer information was together in the same table.

In today's class, we fix the database that we built in Beginner Level 9. So if you have not watched Beginner Level 9 yet, I strongly recommend you watch that one first before taking this class, and of course the entire beginner series if you have not already.

This class uses Microsoft Access 2010, but all the techniques shown today should work just fine in Access 2007. Everything also is pretty much the same in Access 2013. I have been using the beta version which is available right now on Microsoft's website and so far, everything looks pretty similar. If you are using Access 2003 and earlier, the lessons in today's class are roughly the same, but the combo box wizards are a little different. This class is roughly equivalent to my old Access 201 class for Access 2003. You would be better off watching that lesson than this one.

I strongly encourage you to build the database that I build in today's class, but if you have not taken the previous classes, you can download a copy of my database. You will find it on the website accesslearningzone.com/databases.

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 One. 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 master 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 real 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 the basics of relational databases, what relational databases are, why they are important, and the different types of relationships between tables.

In Lesson Two, we are going to learn how to add the proper key fields to several different tables to make them relational. We will set up a customer-to-contact relationship. We will also fix our lead source table and our shipping method table.

In Lesson Three, we are going to set up relational queries to relate the information in the different tables together, and we will talk about some different join types.

In Lesson Four, we are going to learn how to make relational combo boxes where we can pick a value from one table and store that in a field on a second table. For example, pick a customer for each contact.

Intro

Quiz

Q1. What is the main focus of the Microsoft Access 2010 Expert Level 1 course?
A. Relational databases
B. Creating reports
C. Macros and automation
D. Programming in Visual Basic

Q2. What are primary keys and foreign keys used for in Access databases?
A. Validating user input
B. Linking relational tables together
C. Generating random numbers
D. Formatting form layouts

Q3. In relational databases, what does a foreign key typically represent?
A. A unique identifier for each table
B. A field in one table that links to a primary key in another table
C. A field used exclusively for auto-incrementing numbers
D. The name of a report in Access

Q4. Why does the instructor recommend watching Beginner Level 9 before this class?
A. Because it covers essential non-relational database concepts to be fixed in this class
B. Because it teaches advanced SQL
C. Because it is the course on programming macros
D. Because it focuses on data entry forms

Q5. What database design problem is fixed in this Expert Level 1 class?
A. Combining customer and contact information into a flat file table
B. Creating duplicate tables for each customer
C. Building non-relational forms
D. Securing databases with passwords

Q6. Which Microsoft Access versions are fully compatible with the techniques shown in this course?
A. Only Access 2010
B. Access 2007, 2010, and 2013
C. Only Access 2003 and earlier
D. Access XP and older

Q7. What is a relational combo box used for?
A. Selecting a value from one table to store in a field on a second table
B. Formatting forms with colors
C. Calculating totals in a query
D. Creating database backups

Q8. What is the purpose of adding key fields to different tables?
A. To make the tables relational and establish relationships
B. To create user login screens
C. To add formatting options
D. To generate printable reports

Q9. Which join type topic is covered in the class?
A. Different types of joins in queries
B. Password-protected joins
C. Macro-based joins
D. Join tables for report printing only

Q10. What series should students move to after completing all beginner classes?
A. Expert series
B. Advanced series
C. Developer series
D. Seminar series

Q11. What should students do before applying new concepts to their own projects?
A. Practice building the sample database from the class
B. Immediately implement on production databases
C. Write a research paper
D. Skip to developer level courses

Q12. Which of the following is NOT mentioned as a part of the lesson content for today's class?
A. Building web-based databases
B. Setting up relational tables with key fields
C. Creating relational queries
D. Setting up relational combo boxes

Q13. Where can students post questions about the course material?
A. The student forums on accesslearningzone.com
B. Microsoft's official support only
C. In the comments section of YouTube
D. By email to Microsoft

Q14. What is the recommended approach for watching each lesson?
A. Watch it once fully, then replay and follow along step by step
B. Skim to the parts that look interesting
C. Only read the transcript
D. Skip the sample database

Q15. Which skill area is covered in the Developer level courses?
A. Programming in Visual Basic for Access
B. Report design only
C. Table normalization
D. Form design tips

Answers: 1-A; 2-B; 3-B; 4-A; 5-A; 6-B; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A

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 Access Learning Zone focuses on Microsoft Access 2010 at the Expert Level 1. I'm Richard Rost, your instructor. In this set of lessons, we will be taking an in-depth look at relational databases. We'll cover what a relational database is, why using relationships is important, and how to set them up properly in your tables. You'll learn about key fields, setting primary keys and foreign keys, and how to use relationships within queries. I will also cover the different kinds of joins you can use in queries, and how to create combo boxes that let you select a value from one table to store in another, such as choosing a customer to store in a contact record.

This course is designed to follow Microsoft Access 2010 Beginner Level 9. I consider Beginner Level 9 an important prerequisite, because in that beginner series, I showed how a database can be built using non-relational techniques. Specifically, the sample database in the beginner lessons kept all contact and customer data in a single flat-table, which is not the right way to structure your data for the long term. In Beginner Level 9, I intentionally illustrated bad database design, so you could see the problems that can arise.

Now, in this course, we're going to take that poorly designed database and fix it, transforming it into a correctly structured relational database. If you have not already taken Beginner Level 9, I strongly recommend that you complete that class first, along with the rest of the beginner series if you're new to Access.

Everything in this class uses Microsoft Access 2010, but the techniques work just as well in Access 2007 and are nearly identical in Access 2013. I've been trying out the 2013 beta version, and all of the features look pretty much the same. If you're still on Access 2003 or an earlier version, you can still follow along with most of today's lessons, though features like the combo box wizards will look a little different. If you are using Access 2003, you'd be better off using my old Access 201 course tailored specifically for that version.

To help you follow along, I recommend that you build the database alongside me as I demonstrate the steps in today's class. But if you have not taken the earlier courses, you can also download a copy of the database from accesslearningzone.com/databases.

I have organized my lessons into four primary levels: Beginner, Expert, Advanced, and Developer. Beginner courses are designed for those completely new to Access, introducing the essential features you need to start being productive. My Expert series is for those who already feel comfortable with the basics. These lessons cover each topic in more detail and provide more advanced functions and practical techniques for power users. Once you have completed the expert courses, you can move on to the Advanced series, where you will learn to use macros, automation, and other powerful features to make your databases even more professional and capable. The final step is the Developer series, where I teach you how to program using Visual Basic for Applications (VBA) in Access. By learning VBA, you can build highly advanced databases and really unlock the full potential of the software.

Each course level is divided into numbered lessons, starting with Level One. Each new lesson builds on what you have learned in previous ones. After you finish the beginner courses, move up to the Expert level, and continue on through the Advanced and Developer series as you progress.

In addition to the regular courses, I offer special seminars focusing on specific topics. These include building web-based databases, making forms and reports that look like calendars, securing your databases, working with images and attachments, managing accounts payable, learning SQL, and many more. Full details for all seminars and courses can be found on my website at accesslearningzone.com.

If you have any questions about the lessons, I'm happy to help. You can post your questions in the student forums. If you're using my custom video player or the online theater, you'll see the student forum displayed alongside the video as long as you have an internet connection. There, you can browse questions asked by other students, my answers, and additional comments from the learning community. I encourage you to check these questions and answers before each lesson, and please post your own questions if you get stuck. You can also visit the forums later at accesslearningzone.com/forums, even if you are not watching the course videos online.

For the best learning experience, I suggest watching each lesson all the way through first without doing anything else. Then, go back to the start, replay the lesson, and follow along with me step by step on your own computer, building the same database I demonstrate in the video. I recommend not trying to apply what you are learning to your own projects until you've fully mastered the class database. If you hit a roadblock or do not understand something, rewatch the video or let me know what's giving you trouble in the forums.

Above all, keep an open mind as you work through these lessons. Microsoft Access can seem overwhelming at first, but once you get comfortable, you'll find it is straightforward and very powerful.

Today, here is what we're going to cover in detail:

Lesson One explores the basics of relational databases. I'll explain what they are, why you want to use them, and the different types of relationships you can create between tables.

In Lesson Two, we will add the right key fields to several tables to make them work together relationally. For example, you will see how to set up a customer-to-contact relationship, as well as fixes for the lead source and shipping method tables.

Lesson Three will focus on setting up queries that pull related information from multiple tables, and I'll talk about the various types of joins you can work with.

Finally, in Lesson Four, I will show you how to create relational combo boxes, allowing you to pick a value from one table and store it in another, such as selecting a customer for each contact record.

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

Relational database concepts
What is a relational database
Importance of relational databases
Setting up relational tables with key fields
Adding primary keys to tables
Adding foreign keys to tables
Relating tables together using relationships
Setting up table relationships in queries
Different join types in queries
Creating relational combo boxes
Picking a value from one table to store in another
Customer to contact table relationships
Fixing lead source and shipping method tables
Building the sample relational database
How to follow along with sample database

Article

Welcome to this Microsoft Access tutorial focused on relational databases. In this article, we will cover the essential concepts behind relational databases, explore their importance, and walk through practical steps for designing a relational structure in Microsoft Access. You will learn how to set up tables using key fields, establish relationships between those tables, and create relational queries and combo boxes to link your data effectively.

A relational database organizes data into multiple tables that are related to each other based on common fields. This contrasts with a flat file database, where all information is stored in one large table. Relational databases allow you to avoid duplication, reduce errors, and make updating information much easier. For example, instead of repeating a customer's address on every order record, you can store the customer details in one table and relate those details to the orders in another table using a unique customer ID. This design makes maintenance and reporting much more powerful and flexible.

To build a relational database, the first thing you need to understand is the use of primary keys and foreign keys. A primary key is a field or a combination of fields that uniquely identifies each record in a table. For instance, your customers table might have a CustomerID field set as the primary key. Every customer will then have a unique CustomerID. When you want to relate this to another table, such as a contacts table, you add a CustomerID field to the contacts table as well. This serves as a foreign key, linking each contact record to the correct customer. Primary keys ensure that each record is unique, while foreign keys create connections between tables.

Let us consider an example. Suppose you have a customer table and a contacts table. The customer table would include fields like CustomerID, CustomerName, Address, and so forth. The contacts table would contain fields like ContactID, ContactName, Phone, Email, and also a CustomerID. By connecting the CustomerID from the contacts table to the CustomerID in the customer table, you create a relationship. Now, you can store multiple contacts for each customer, but the customer data only exists in one place. This setup eliminates duplicate data and makes your database easier to manage.

When building a relational database in Access, you will often start by designing the tables. For each table, make sure you define a primary key by selecting the relevant field and marking it as the primary key in table design view. For foreign keys, simply add a field to your table that will store the key value from another table. For instance, the contacts table should have a CustomerID field that matches the type and size of the CustomerID field in the customer table.

Once your tables are set up, the next step is to establish actual relationships between them in Access. Open the Relationships window from the Database Tools tab. Add the tables you want to relate to the view, and then drag the primary key field from one table to the corresponding foreign key in the other table. Access will prompt you to confirm the relationship type. The most common relationship is a one-to-many relationship, where one customer can have many contacts. For more advanced setups, such as many-to-many relationships, you may need to create an additional junction table, but that is beyond the basics.

With your tables related, you can now build queries that pull data from multiple tables at once. For example, you can create a query that shows customer names along with the contact names and phone numbers for each customer. When you design a query, add the related tables, and Access will automatically draw join lines between them based on the relationships you have established. You can adjust the join properties by right-clicking the join line and choosing the join type. The default is an inner join, which only shows records with matching entries in both tables. You can also choose left or right joins if you want to see all records from one table and only matching records from the other.

To enhance the usability of your data entry forms, you can create relational combo boxes. For example, when adding a new contact, you might want to select the customer from a dropdown list, rather than typing in a CustomerID manually. To do this, add a combo box control to your contact form and set its Row Source to the customers table. The combo box can display the customer name to make selection easier, but store the CustomerID invisibly. This approach reduces errors and ensures users select valid, existing customers for each contact. In the combo box properties, make sure the bound column matches the CustomerID field and that the column widths are set so only the customer name appears in the dropdown, if you wish.

Sometimes you will need to correct flat file tables created in earlier databases. For example, you may have built an initial contact management system where customer and contact details were combined into a single table. This is not ideal for relational design. The process to correct this involves splitting the data into separate tables and adding key fields to establish proper relationships. Move customer-specific information to the customer table, create a CustomerID primary key, and then link existing contacts to the relevant customer records by adding the appropriate CustomerID as a foreign key in the contacts table.

You may also need additional lookup tables for information such as lead sources or shipping methods. These tables usually have their own primary key and a description field. When you relate these tables to your main tables, you ensure that only valid values can be selected for certain fields, which enforces data integrity throughout your database.

As you become more comfortable with these concepts, you can move on to more advanced features such as automation with macros, writing VBA code to build custom functions, and even securing your database for multi-user access. For example, you can write VBA code in Access by opening the code editor and entering custom routines. Here is a simple example that displays a message box when you open a form:

Private Sub Form_Open(Cancel As Integer)
MsgBox "Welcome to the Contacts Form!"
End Sub

This code would go in the form module and pop up a message when the form is opened.

No matter your level of experience, it is helpful to build a sample database alongside learning these concepts. Work through setting up tables, relating them, designing queries, and adding relational combo boxes to your forms. Do not try to apply these techniques to your own workplace database until you fully understand how relational design works with this sample project. If you encounter problems or need more clarification, reread the relevant sections or seek help from Access-related forums and communities.

At first, Access can feel overwhelming due to its many features and the new concepts of relational design. However, once you get the basics and work with them hands-on, you will find Access is approachable and extremely useful for creating powerful database solutions.

In summary, relational databases are crucial for organizing complex data effectively in Access. By using primary keys and foreign keys, setting up correct relationships, designing relational queries, and enhancing forms with relational combo boxes, you will build databases that are easier to use, maintain, and expand in the future. Practice these steps, and you will soon be comfortable with expert-level Access database design.

Primary Topics

relational databases, table relationships, primary keys, foreign keys, relational queries, combo boxes, join types

Secondary Topics

database design best practices, course series structure, upgrade advice