4. Microsoft Access 2016 Basics: One To Many Relationships

[Music] hello everyone and welcome to Microsoft Access 2016 basics my name is Steve Bishop and in this video we're gonna take a look at one-to-many relationships so let's say that we have a group of people now each one of them has a colored shirt a red shirt an orange shirt or a blue shirt and let's say that we wanted to categorize each one of these people according to the shirt that they're wearing so we move them into their perspective categories and we could say that one color is worn by many people so there are many people wearing a red shirt there are many people wearing an orange shirt and there are many people wearing a blue shirt this is the essence of a one-to-many relationship one color for many people but what would be a practical business application of this organization well what if instead of organizing them by the color of their shirts we took a look at their type what kind of person are they to us are they a customer a vendor or an employee and for that we could say one category for many people let's see an example of this applied to our Access database so I'm gonna go ahead and add another field here and I'm gonna call this person type and we're gonna set it to just a short text that's fine and then we can go back to the datasheet view and let's enter some values in for these person types so let's say that I am an employee and then Shane might be a customer and let's say denise is also a customer well add one more person to our list here let's say Joyce Reynolds and her date of birth let's just pick some random in time here about January 17th of last year and we'll go ahead and say that Joyce is also a customer so that way we have three customers and one employee notice that we're starting to duplicate data though if you're duplicating values across multiple records that should be an indication that you should be doing something called normalizing your database normalizing your data is essentially figuring out what pieces of data you should extract and put into a separate table let's create that table so I'm going to go up to the create tab and click table design now I'm just gonna go ahead and name this first field type name and set it to the short text data then I'm gonna right click on the table one tab and click Save now this table is going to contain all of the different people types so let's call this people types now notice that we see that there is no primary key to find now it's asking me if I want to create the primary key right now but I'm not gonna do that just yet I'm gonna click no for now and then let's switch over to the data sheet view for our people types now if you recall I had two different types I had employees I'm just gonna give it a singular of employee here and then customer now let me ask you a question if I'm gonna replace person type with a value from the peoples type table how am I supposed to relate this record here with this record here well that's where a primary key comes into play now a primary key is a field on a table that helps to uniquely identify each record so for my people table the primary key is the ID field because no two records share the same ID that's why they're automatically incremented so how then do I associate this record with this employee type name well the answer is I need a primary key on my people types table so that I can uniquely identify each one of the records inside of the people types then I can go back over here to my people table and change this from the actual text value to the unique ID of the people type that it refers to so let's go ahead and add another column to our people types I'm gonna actually put it above the type name so let's insert a row and I'm gonna change this field name to ID and set it to auto number now this doesn't inherently make this a primary key what I have to do is select on the ID column here or the ID field and click on the primary key button here let's go ahead and save that let's change over to the datasheet view you'll notice that access is already populated the ID field for us with some unique values for each of the people types now we can use those unique values of 1 & 2 to say what kind of people are in our people table so employee type we know was actually a 1 and customer was a 2 but we're gonna have a little bit of a problem here and that is the person type field if you recall is actually a text type it was a short text but the data that we're storing in here is actually a number for the ID of people types so we need to change the data type for our person type to a number furthermore I need to make sure that the field size field here of our person type data type is set to long integer and matches the same data type that I have for my ID column on people types so let's go ahead and take a look at the design view of our people types and make sure it does indeed say long integer so that way the datatype of our ID field on people types matches the data type for our person type here which is long integer as well it's going to save that and now what you're gonna see is as some data may be lost and the reason for this is because of the datatype change that we made to the person type field we went from text to numbers and because of that some of the validation rules might be violated do we want to continue anyway I'm gonna go ahead and click on yes now if we switch back over to the data sheet view we should be all set to go though all of our values are still working one and two and our people types also have the ID field here it's right click and go to datasheet view and you can see one and two now when we go to add another person to our people table we need to also add the person type ID value let's go ahead and add one more person to our people table let's go with John Smith and set the date of birth to 12 15 1985 we'll also set his salary just a little bit less now over here for person type we're gonna say that he is also an employee and if you recall the one is the value that we set for the employee people type so that's how we set up a one-to-many relationship we have one people type with many different people who belong to that type in this case we have two employees and we have three customers now the one important thing I want to point out about what we've done here is we've actually saved ourselves on memory that's the reason we do this we could obviously put customer and employee as text fields for person type but then think of how many different values we'd be putting in this person type field that's a lot more data that we would have to store in this people table whereas over here in our people types we only need the two records now and the values that we store for our person type are just numbers one final note to point out is that there's a term for this person type field now since it is a reference field to another table we call it a foreign key so this ID field which contains unique values one for each record is called the primary key while a field that contains values that point to a unique value of a different table are called foreign keys these primary keys and foreign keys are the crux of what creates a relational database without them we wouldn't be able to organize our data in this relational way [Music] you

Channel: Programming Made EZ

Views: 33,301

Keywords: Microsoft, Access, 2016, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, microsoft SQL server, SQL server, programming language, one, to, many, relation, relational, relationship, data, organize, how to create a one to many relationship in access 2016

Id: iCEMy7Khhss

Length: 10min 3sec (603 seconds)

Published: Sun Apr 08 2018