Access 2010 Expert Level 1 Introduction

This video introduces my Access 2010 Expert Level 1 class. For more details on this class visit http://599CD.com/XHPC2Q and for a special discount on this class just for my YouTube fans, visit http://599CD.com/XYBHS6 This class covers fixing the contact history table that we started in the last class. I intentionally showed you the wrong way to build it last time. Now we'll build it correctly. You will learn: - Relational Database Concepts - Primary & Foreign Key Fields - Types of Relationships - Focus on One-to-Many Relationships - Ad Hoc Query Joins - Left Joins to Show All Records - Relational Combo Boxes - Fixing Lead Source & Shipping Combos - Select a Customer on the Contact Form This class picks up where Beginner Level 9 left off. In B9 I intentionally showed you the wrong way to build the contact table using a flat file database system, where all of the customer and contact information was stored in one table. In this class, we'll fix that problem by building a properly relational set of tables. We'll begin by learning about relational databases, what they are, and why they're important. We'll learn about primary and foreign key fields. We'll learn about the different types of database relationships: one-to-one, one-to-many, and many-to-many. Next we'll set up our tables with the right relational fields. We'll add a CustomerID to our contact table, learn more about Foreign Keys, and fix the Lead Sources and Shipping Method tables. Next we'll learn how to join tables together using Ad Hoc Query Relationships on the fly. We'll see how Access creates automatic join lines, how to delete a relationship, and how to create one manually if Access doesn't do it for us. We'll learn about different join types, including a LEFT JOIN which is handy to show all of the records from one of the related tables even if matching records don't exist in the other tables. Finally we'll create relational combo boxes. This will allow us to pick a value from one table and store it in another. For example, we can pick a customer from our customer table and store that ID in our contact table - to set which customer the contact belongs to. We'll do something similar with our Lead Source and Shipping Method tables. You'll see how the combo box wizard behaves slightly different if you're using a query vs. a table. This is the first class in the Access Expert series. If you want to take your database skills to the next level, this is the class for you. Understanding proper relational database construction is absolutely vital to the success of your projects. Of course, if you have any questions about whether or not this class is for you, please contact me. FULL OUTLINE: 00. Intro (7:13) 01. Relational Database Concepts (17:06) What is a Relational Database Why are Relationships Important Flat File Databases Key Fields Primary Key Foreign Key Types of Relationships One-to-one Relationships One-to-many Relationships Many-to-one Relationships Many-to-many Relationships Junction Table Normalization 02. Relational Tables (14:53) Making our contact management relational Inserting a CustomerID in ContactT Removing duplicated fields Setup a Foreign Key Moving fields in table design Fixing Lead Sources Fixing Shipping Methods 03. Relational Queries (14:04) Add CustomerT to ContactQ Ad Hoc Query Relationships Automatic Join Line Delete Relationship Manually Create Relationship Asterisk from One Table Join Missing Customers Join Types Left Join / Outer Join Joined fields from both tables are equal Show All Records from CustomerT Only those records from LeadSourceT Where the joined fields are equal Fix ContactR Report 04. Relational Combo Boxes (19:34) Add Combo Box to Contact Form Combo Box Based on Table Combo Box Based on Query Wizard is Slightly Different Fix Lead Source Combo Fix Shipping Combo 05. Review (04:50)