Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop
      • Python
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Access > Understanding Access 2013 Relationships between Tables – Part 1

Understanding Access 2013 Relationships between Tables – Part 1

The most important goal of any database design is to ensure that there is absolutely no data redundancy. The best way to achieve this is by creating relationships between different tables, which have some common fields. Access 2013 offers you an easy way of doing this. All you have to do is select tables, drag fields, and enforce referential integrity (if required). The type of relationships and the overall structure of the database including the connections are diagrammatically illustrated in relationships design view. All you have to do is to make sure that the data types of foreign keys match.

Watch the free video here, transcripts for the entire video follow:

Need effective Access 2013 training? Get our 9-hour of Microsoft Access 2013 training – click here.

Video transcripts:

Hello again and welcome back to our course on Access 2013.  In the previous section we looked at setting up relationships between tables in an Access 2013 database and in particular we looked at recording the genre for a movie in our movie database.  In this section we’re going to look at setting up the relationship between a movie and the actors in it.

Now to some extent this is a similar exercise to the one where we setup the genre but there are a couple of important differences.  And one of them is that when we define the actors that appear in a movie we define some other information, notably for example their role in the movie.  This is not the actors name but the name of the character played by the actor.  So some of this is pretty much the same procedure.  We’re going to create a new table.  So we can click on Create.  We’ll go into table design.  We’ll start off with an ID field.  Note that when you go into table design you don’t get one of these by default but we are going to have one.  So you can define it yourself if it’s not all ready there.  ID defined as AutoNumber.  The next thing we’re going to want will be the movie.  So we put in here movie.  Now we know that the movie is the foreign key pointing to the primary key in the movie table so we know that this needs to be a long integer.  So type is number, check we get long integer down there which we do by default, that’s fine.  And the next thing is the actor.  Basically for the actor table the actor ID is also an AutoNumber field.  So again this will need to be a number and a long integer so that’s fine.  But now I’m going to add an additional field for this one and this field for the moment we’re just going to put in the role, the role of the actor in the movie.  And this will be short text, normally the name of the character.  So I’m just about ready to save that.  Note that if I’ve manually added an AutoNumbered ID field to act as the unique key I need to mark it as the primary key.  So let me just click there, click on primary key, and then in order to save it with the name that I want if I right click on the tab, table 1 in this case, and click on Save.  Type in my table name MovieActor, click on OK, and then I’m going to close that.  And let’s now setup the relationship.
Simon Sez IT courses
So we go to database tools, click on relationships, that’s the diagram that we drew before.  Now we need to show another table so we click on Show table.  We want to show actor and we want to show MovieActor.  Close again.  Now you can arrange these boxes representing these tables in any way you like.  You just drag them round by their headers.  I normally try to arrange them in such a way that they’re easy to read through the relationships.  You could put, for instance, the movie table on here twice if you wanted to.  There’s nothing to stop you having the same table several times.  But while this database is not yet particularly complicated let’s just stick with one movie table and what we really need to do now is to do the link from movie to MovieActor which will be movie ID to movie, referential integrity yes, click on Create.  And then the link from actor, actor ID to actor there, enforce referential integrity, Create.  So we’ve now got our next relationship defined.

Now we’re going to enter some data now relating to this new relationship, but let me just point something out to you about this now, one way of reading this diagram that I think helps.  If you look at the actor table down here and the line to MovieActor then you see that for one actor, so for each actor there are many movie actor records.  So an actor can be in many of these MovieActor records.  And for each MovieActor record for that actor it represents one of the movies that they’re in.  So an actor has well infinity movies.  What it really means is that an actor has many movies.  But this end when you look at it from the movie table says a movie table has many actors.  So one movie many actors, one actor many movies and that’s how it works.

So let’s close the relationships diagram, save the changes, and let’s go to the actor table.  Let’s just open it up, find an actor, Will Smith is actor number six, and let’s setup a MovieActor record.  Open it up, movie number we had before was six for Independence Day, actor number by coincidence is actor number six and the role played was Captain, I’ll just make that column a little bit wider so you can see it all, and there we have the first actor in a movie and the role that they played in that movie.  So let me just do one more example of that.  Actor, actor Ingrid Bergman, that’s actor number four.  Let’s go back into movies, Casablanca’s number five, MovieActor, movie number five, actor number four, and the role is Ilsa Lund.  That’s the role that Ingrid Bergman played in Casablanca.
Excel 2013 training courseby Simon Sez IT
Now hopefully from that you get the general idea of how these relationships work, but you’re probably also looking at this and thinking that’s actually quite difficult to keep track of isn’t it?  Because you’ve got to worry about all those numbers.  Well in this basic form that’s true but as we will find out in a little while and particularly when we start to look at forms in detail there are various ways to make this whole process much easier.  But the important thing to know is to know what’s going on under the hood or as you say in the U.K. under the bonnet.  It is important to understand how this linkage is working but there are ways to make it all an awful lot easier to use and that’s what we’re going to look at a bit later on in the course.

So now it’s time for the next piece of work for you to do.  I want you to take this which is currently example-03 in the supplied files and I want you to make sure that we have data, at least one actor for every movie and at least one movie for every actor.  So every actor should be in at least one movie and every movie should have at least one actor in it.  You may have to add an actor.  You may need to add a movie.  I’ll leave that to your discretion.  And the answer to that will be example-04, but in addition I would like you to add genre values for each of the movies that is in this version of the database.  Now most of them you can get three or four genre values probably by looking at IMDB.  It doesn’t particularly matter that they’re accurate.  It’s more important that you understand how the MovieGenre mechanism works.

So that’s it for this section.  I’ll see you in the next one.
MS Project 2013 online training by Simon Sez IT

 

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

START LEARNING HOW TO Access 2013 Relationships between Tables ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

How to Delete a Page in Word?

How to Use Items in QuickBooks 2019 – Part 1 and 2

Create Purchase Orders in QuickBooks 2019

5 Cool Features in Adobe Photoshop CS6

How to Enter Tasks in Microsoft Project 2016

Using the Junk Email Options in Outlook 2013

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)