Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

< Previous: Access Beginner 9

Next: Access Expert 2 >

Access Expert Level 1

Expert Microsoft Access Tutorial - 1 Hour, 18 Minutes
 
 
This Microsoft Access video tutorial picks up where Beginner Level 9 left off. 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

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access 2010 Expert Level 1
Description: Access 2010 Expert Level 1
Versions: Recorded with Microsoft Access 2010
Works fine with Access 2013 and 2007
Pre-Requisites: Access 2010 Beginner Level 9
Running Time: 1 Hour, 18 Minutes
Cost: $19.99


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.
 

 

Complete Outline - Access 2010 Expert Level 1

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)

 


 
Keywords: Relational Database Concepts, Foreign Keys, Query Joins, Combo Boxes, microsoft access tutorial, microsoft access 2010 tutorial, microsoft access 2010 training, flat file, key fields, primary key, one-to-one, one-to-many, many-to-one, many-to-many, relationships, junction table, normalization, ad hoc, automatic join lines, join types, left join, outer join, show all records, where the joined fields are equal, combo box, wizard
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP