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 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in 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 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • 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 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • 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
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • 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 Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Access > Understanding Access 2013 Relationships between Tables – Part 2

Understanding Access 2013 Relationships between Tables – Part 2

There are several features in Access 2013 that make tables much interactive and versatile. Lookup wizard allows you to predefine the set of values that a field can take. The potential values appear as a drop down menu, and you can simply select the correct value for that particular record. You can further choose to allow multiple values or not. In case you allow this, the values in the drop down menu would come with checkboxes that can be used to select multiple values. Access 2013 allows you to insert values manually or by looking up another table.

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


Facebook


Linkedin


Twitter

Learn how to master Microsoft Access 2013. 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 created the first draft of a movies database and we created the movie table, put some data in it, and I left you with an exercise to do to create an actor table and put at least three actors in it. I’ve got a copy of my answer to that in front of me here. My answer was example-02 in the files that you get with the course. This is a copy of that. There are the two tables. I want to look at the actor table first. So if I want to look at the data I just double click and it opens the table in Datasheet View.

Now as we can see I’ve got three actors in here, fairly well known, although perhaps if you’re a young person you may not have heard of any of them. I don’t know. And these three actors I’ve got very basic information: first name, last name, year of birth. Now I want to add some more information about each of these so what I need to do is to add some fields to this table. And the first field that I want to add is each actor’s gender. Now I can do that either by using the space on the right here. So for instance if I thought, Well let’s just put M and F in for Male and Female. So Humphrey Bogart is M, Ingrid Bergman is F, John Wayne is M and automatically Access 2013 gives me a new field called Field 1 and then allows for another inserted field on the right here. So it’s very dynamic in terms of letting you add fields when you realize you need more information. Having added that data though if I go back into Design View, so click on Design View. I can see that it’s added my field. Now I don’t want it to be Field 1. I want to change the name to Gender. And the type I also want to change because I don’t want it to be short text. I only want there to be two possibilities here. I either want the gender to be M or F. So in the drop down at short text if I click on that, I want to change from short text to lookup. Now the idea of lookup is that I can look up potential values from a limited list of the possibilities. And in this case the limited list is just M or F. So let’s look at the Lookup Wizard.
Simon Sez IT online training for office 2013

So the first screen says, The Wizard creates a lookup field which displays a list of values you can choose from. How do you want your lookup field to get its values? You get a choice. I want the lookup field to get the values from another table or query or I will type in the values that I want. Now I’m going to type in the potential values. So I’m going to choose the second option and click on Next. What values do you want to see in your lookup field? Now if there is more than one piece of information to choose from in the sense that you need to show somebody say a number and an explanation you’d have more than one column. But for this we only need one column and the possible values, well the first value is going to be M and the second value, potential value, is going to be F. And they’re the only two values that people can choose from. So click on Next. What label would you like? Well the label of Gender would be fine. And then under that there’s a very important checkbox, Limit to list. Do you want to limit entries to the choices? Sometimes when you give people a choice of what to put in a field you say to them you could have this or this or this and in fact you could also type in your own choice if you wanted to. So you’re helping them by offering them a choice but you’re not limiting them to those choices that you’ve given them. Now in this case there are only two possible choices, an actor is M or F. So in this case we’re going to check Limit to list. The next question on that page we’re going to come back to later on. Do you want to store multiple? Well let’s just ignore that for the moment and click on Finish. Now what we get is a still a short text value but on the Lookup Tab in the grid at the bottom we’re told that the row source, the third entry here, lists the possible values that will appear as our choices. Now I’m going to save the changes we’ve made to this table and then show you how this works in Datasheet View. So let’s do a save and then let’s switch to Datasheet View.

Now watch what happens when I add another actor. Now having put in the basic information when I get to the Gender field now I get a control that is effectively what’s called a Combo box. And I have a drop down arrow to the right. If I click on the drop down arrow I see the choices M or F. I can’t type in anything other than M or F. I’m limited to this list. But I select M and that’s it and then I’m ready to move on to entering details of the next actor.

Now I want to do a similar thing to the movie table next so I’m going to close actor, save the changes, and I’m going to go into the Design of the movie table. And what I want to add here is the genre of the movie. So I’m going to add a new field called Genre and I’m going to say that it’s short text and I’m going to make it lookup as well. Now this time when I say Lookup Wizard I’m going to say I will type in the values that I want, click on Next. I’m still only going to only have one column but let me put as my genre options Comedy, Mystery, and say Thriller. Click on Next. I’m going to say Limit to list again but this time I’m going to say, Do you want to store multiple values for this lookup? And I’m going to say yes I do and then click on Finish. So save that and then go into Datasheet View to look at my data. Now let’s choose the first movie, Identity Thief, click in Genre, click on the drop down, and this time in this drop down I have my three entered options but I get checkboxes next to them and I can choose more than one option. And if I choose Comedy and Thriller and click on OK look what happens. I get Comedy and Thriller. Now one thing I should just point out, be looking at this a little bit more later on, the Datasheet View works like an Excel spreadsheet.

So if a column is not wide enough you can just drag it a bit wider, there we are. Now we can see that the genre for Identity Thief is Comedy, comma, Thriller. So I’ve got two values. Now if you know much about database design you’ll know that this is pretty bad design for a database and I’m going to change this a little bit later on. So if you’re looking at that and perhaps feeling a little bit horrified don’t worry too much about it because I am going to fix that later. But it does demonstrate something that can be useful, which is the ability to get multiple values in a field like the Genre field. And it’s a very straightforward way of doing it as well, as you can see. Now the number of possible values for the genre of a movie, it’s quite a long list but it’s a fine art list, maybe 10, 15, 20 different options, something like that. But supposing that I also wanted to put into my movie table a list of the actors in the movie.

Microsoft Excel 2013 training course

Now let’s go back to the design of movie and let’s suppose I was going to put in here a list, Actors. How would I put the list of actors in there? Would I do it like Genre? Would I have first actor, comma, second actor, comma, third actor, comma, fourth actor? The answer to that is definitely No although many years ago some people would have designed databases like that. If you have a long list and particularly if you have a very variable list such as the actors in a movie or the crew in a movie or what about the special effects technicians? There are sometimes hundreds of them. You wouldn’t make a long list of them with commas in between their names. We need a different way of putting long lists of things together. The other alternative is to say have an Actor 1 field and an Actor 2 field and maybe an Actor 3 field. And with all of those you could put in the actor, the main actor, the headline, the second actor, so on. How many of those would you allow for? How many actors is the most that you might need? You can see that it’s not that easy to design a table where you’re not quite sure how many of something you need. Now what we’ve got so far in the movie table is very straightforward. We’ve only got one title and we’ve only got one year of release and the genre although there could be one or two or three or four of them there won’t be that many. But when it comes to actors or crew then you’re in a different situation altogether and as I say that’s what we’re going to look at in the next section.

Now in readiness for looking at relationships between tables in the next section I want to do one more thing in this section and that is to create a new table and the new table is going to be a genre table. Now we just setup a lookup using three possible genre values that I typed in. In reality it would be more efficient and more straightforward to have a separate table with all of the available genre values in it. And bear in mind that the genre values may change over time as well. If we have a separate table we can add new genre, remove ones that are no longer used, and so on. So it’s a pretty straightforward case as before with one interesting question. So I go to Create. I go to Create table. I can say create a new blank table or I can say create a new table design. Let’s go straight to table design this time and this time instead of getting the default ID I’m going to say well maybe I don’t actually need an ID; maybe I don’t need to number these. Maybe the genre values themselves could be the primary keys. I said earlier on that you’ll almost always use an ID but it’s only almost used. There are occasions when you don’t really need one. So I’m going to actually put in here Genre as the name of the field, the data type is going to be short text, and that’s all I’m going to have in that particular table. This will be the primary key. So click on that, click on there, it becomes the primary key. As such it must be unique.

Now I’m going to put in the genre values that I want. I know that when I switch to Datasheet View it will ask me to save what I’ve done. So click on Datasheet View. You must save the table. Click on Yes. Now I’m tempted to call the table Genre, but I’ve called the field Genre as well and what I’m going to put for the table name is going to look a little bit strange. Not everybody does this. I do but a lot of people that do quite a bit of database design do this. They prefix the names of the objects in their databases with a system of codes that remind you what something is. So the genre table I would call T-B-L-Genre, tblGenre. That reminds me whenever I see it that it is a table. And in fact I would normally use that same approach for those other two tables as well.

Okay so let me start putting in the genre values. First one is Action, the next one is Adult, the next one. I’ll carry on typing these, joining me again in just a moment.

So that’s my Genre table setup. I’m now going to close that table. I’m going to close the movie table. Save changes of course. And then I’m going to right click on Movie and click on Rename and that is going to begin with T-B-L as well and then I’m going to right click on Actor and do the same to that. And from now on I’m going to stick to this naming convention. When we come to things like forms and reports they’ll have similar prefixes to help me to identify things later on. It’s a very good thing to bear in mind if you are going to try and follow this naming convention that you don’t want to wait until much later on to set these names to these prefixed values. Use those names from the outset.

So that’s it for this section. I’ll see you in the next one.
Learn MS Project 2013 online

 

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!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • 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

START LEARNING Access 2013 Relationships between Tables - Part 2 ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

Jira Slack Integration – An Ultimate Guide in 2022

Using Burndown Reports with Microsoft Project 2013

How to setup rules in Microsoft Outlook 2013 – Part 1

How to Change Orientation of One Page in Word? 3 Easy Ways

How to Switch Between Sheets in Excel? 6 Useful Methods

How to Use the Mini Toolbar and Contextual Menu in Microsoft Excel 2016

Course Categories

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

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

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