Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Courses > Access > Seminars > Relationship >
Access Relationship Seminar

Learn All About Microsoft Access Relationships


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark Buy Now

Welcome

In this seminar, you will learn just about everything there is to know about relationships in Microsoft Access. Setting up proper relationships between your tables is absolutely crucial to building a successful database. Bad table design, and bad relationships between those tables, is disasterous, and will cause you nothing but trouble down the line.

This seminar covers just about all of the different possible relationship scenarios that you will ever come across. We will build eight (8) different database projects so you can see how to set up good relationships between your tables.

Resources

Relationships Covered

  • No relationships
  • One-to-Many
  • One-to-One
  • Many-to-Many
  • Self-Join One-to-Many
  • Self-Join Many-to-Many
  • Reverse Relationships
  • Multiple Relationships

Databases Built

  • Children to Parents with their relation (one-to-many)
  • Which parents receive mail on their child's behalf
  • Student data with a secondary optional detail table (one-to-one)
  • Vendors to Products and vice versa (many-to-many)
  • Employees to Supervisors (self-join, one-to-many)
  • Complete family history with relationships (self-join, many-to-many)
  • Organizations to Members (whether companies, families, charities, etc.)

Topics Covered

We will begin by looking at in table that has no relationships. You'll learn how NOT to build a database. This is basically one table with all kinds of information in it that shouldn't be there.

 

Next we'll build a database to track students and parents. This is a one-to-many relationship where one student can have many parents. We'll also keep track of which parents will receive mail for each student.

 

We will learn about one-to-one relationships. This is handy if you have a lot of data but you only have some information on most of your records. For example, if you have a student database but only have names for most of your students. You need to be able to track additional information such as address, phone number, and so on, however you only have that information for a small group of students. A 1-to-1 relationship saves a lot of space in your database in this instance. Plus you can also put sensitive information in a separate table, such as financial data.

 

Next we will learn how to create many-to-many relationships. These are quite popular but difficult to implement because they involve a third table. You need a junction table to link two other tables in a many-to-many relationship. For example ,we will learn how to track vendors and the products they sell, however each product may also be offered by multiple vendors. This is the basis for a many to many relationship.

 

Next we'll create a self-join table. This is where a table relates to itself. We'll create an employee form that allows us to choose a supervisor for each employee. However, the supervisor is just an employee, so the table relates to itself.

 

You'll also learn how to create a grouped report showing each supervisor in the employees he supervises. This requires building a query using the same table twice.

 

Next you'll create a another self-join table. We will track people and their relatives again, but this time we will set up a many-to-many relationship so each person can relate to each other person in the same table. This allows you to set up complete family trees (great for genealogy databases).

 

Next we will set up something that I like to call a reverse relationship. We'll specify two people, tell the database that one of them is (for example) the other person's uncle, and the database will figure out that the reverse relationship is niece or nephew.

This is one of the more advanced lessons in this seminar. It will use a little VBA programming and some SQL. I will show you everything you need to know to get the database working, but taking my SQL Seminar and advanced Access lessons first would be a great help. But don't worry, I'll show you how to get the database is working. Just follow along a step by step.

 

Finally will make a database to track organizations and members. Now in organization can be a family, company, charity, church, or anything else you want it to be. We'll track members of that organization in a many to many relationship. These can be family members, employees, donors, etc.

We will track multiple addresses for each organization and for each member. We will also build a contact management table to keep track of correspondence with each organization or member. We will build one big form so that we can see all of the information for each organization. We'll do the same thing for each member.

 

So as you can see we cover just about everything there is to know about relationships in Microsoft Access. From the very basic to the advanced, you will definitely know how to properly setup your tables and relationships after watching this seminar.. Of course, if you have any questions about whether or not this seminar is for you, please contact me.

NOTE: There is a NEW lesson on database table normalization found in Access Expert Level 2 that wasn't included in this seminar. It's not necessary for you to learn, but it covers a lot of theory that isn't discussed in this seminar. It was recorded after this seminar was finished.

Pre-Requisites

This is a Developer-Level Seminar. There will be a lot of VBA. It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video. My SQL Seminar Part 1 is recommended, but not required.

Version

I am using Access 2010 in this seminar, however the lessons are perfectly valid for all versions of Access from 2003 and later. It's currently 2022 and I just recently verified that everything in this seminar still works with Access 2019 and Office 365.

Enroll Today

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Questions?

Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.

Keywords

microsoft access relationships, One-To-Many, One-To-One, Many-To-Many, Self-Join, flat file, child, parents, mailing, students, parents, school, global relationships, inner join, cascade delete, referential integrity, security, products, vendors, junction table, cross-reference table, employees, supervisors, report grouping, genealogy, family, dlookup, vba, sql, insert into, append query, organization, members, membership, charity, charities, churches, church, fund raising, donors, multiple addresses

 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

Check out these other pages that may be of interest to you:

9/27/2022Report Border
9/26/2022First Day of Month
9/23/2022Before Update
9/22/2022Synchronize Subforms
9/21/2022InputBox
9/20/2022Link Master Fields
9/19/2022DateSerial
9/18/2022Sequential Annual Coding
9/16/2022Scope
9/15/2022Resize Forms
 
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
amicron@gmail.com
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access seminar microsoft access relationships, One-To-Many, One-To-One, Many-To-Many, Self-Join, flat file, child, parents, mailing, students, parents, school, global relationships, inner join, cascade delete, referential integrity, security, products, ve  Page Tag: whatsnew  PermaLink  Microsoft Access Relationship Seminar