7. Many To Many Relationships (Programming in Microsoft Access 2013) 🎓

hi everybody and welcome again to programming in Access 2013 where can you see we're continuing our database section and today we're going to be going over a bit more about relationships in our Access database so let's get let's get started all right this is where we left off last where we have our three different tables all joined together and we have this situation here this relationship which is called a one-to-many between our customers and our addresses and this is a relationship of the ID field between our customers ID field and customer ID field on the table one addresses okay so these two tables are linked together in a one-to-many relationship which essentially just means for every one customer I may have many addresses okay now one quick little correction I want to make about my past video I did make a little faux pas there I said that because of the naming convention where I've given a customer underscore ID that access automatically understood this relationship I was trying to create and then it's actually not accurate that happens with some other frameworks like in PHP and some other different styles of frameworks where it actually recognizes and identifies these relationships based upon the way you name them but unfortunately access doesn't do it that way I believe this relationship that exes understood was because I made this a primary key and this field is not a primary key in the table on addresses table and therefore access identified that since this is a primary key and this is not currently a primary key that these two things must be related by having this as the primary and this is the foreign what we call the foreign key okay so foreign keys exist on the table that you're trying to tie in two primary keys are obviously the unique identifier for that particular row of data in that table which I'm going to get into in just a little bit here so you may have already realized that there's going to be some situations where your data is not always going to be organized and this one too many okay and the way I'm going to set this up as an example is let's say in my customers which are customers for this particular instance are just basically businesses but I'm going to have lots of contacts within a company I may have you know four or five different people that I talk to within that particular company you know I may not just have one person that I talk to I may have multiple people so I'm going to create a contacts table and I've already populated some data in here and I'm also going to need a way to talk to these people so I'm going to have a phone numbers table and I've already made up these tables I've already defined their definitions their data types etc and I put them in here in case you don't know how to do that I know I didn't really go over too well if you go and click on the create tab here this is where you're basically going to go to create anything your tables your queries your forms all of this and also your reports are going to be designed by going up to the create tab then click on either table design query design form design or report design I personally don't even mess with any of the other options the table here or the wizards I avoid the wizards like the plague they can really cause you problems they can they can end up causing you more costing more time taking out the things that access automatically puts in it's just better to start with a blank and using the designer each time so anyway let's get back on track here so how is this relationship going to work between phone numbers and contacts because there may be situations and we're not dealing with just one particular customer we have lots of different companies that we're planning on working with and each different company is going to have a very different arrangement of phone numbers to contacts because let's say for example the whole company has one business line they just have one phone number okay that you can reach all of those people by using that one phone number because that's the only one there may not be extensions or maybe if there are extensions you don't really keep track of that all you want is the main phone number to call so there can be an instance where multiple contacts will have one phone number but at the same time let's say those same people that work there have their own individual cell phones so now you have a contact which can be reached at the main phone number but also has his his or her own cell phone number so how do you handle this relationship how do you handle there's multiple contacts there's many contacts there are many phone numbers and any arrangement of these two could happen how you solve that well this is what's commonly called a many-to-many relationship there are many phone numbers that relate to many different contacts and the way you handle this is through a third table okay and for condemning convention reasons I like to put a little underscore between you know what I'm making up this third table I use the convention of the primary table that's going to hold the you know the the primary information of that particular contact underscore and then phone numbers here which is the other bit of information that I'm going to be tying back into the contact so I'm separating out the table names with an underscore and you'll notice that the ID field here if I follow the line tracks up to phone number underscore ID and then under Table one contacts tracks up to contact ID so here we have this relationship of ID to phone number ID and table one contacts ID to contact ID now in this third table I'm going to have say contact ID number one has phone number ID number one because we're going to hit keep track of our phone numbers in here and I'll go ahead and open up the table you'll see that ID four phone number one is for you know here's the phone number and the ID for that phone number is number one and then phone number two phone number zero zero zero two has an ID of two and four number zero zero zero three as an idea of three okay now you may be wondering what's this phone type ID this is similar to how I had the addresses I have a phone types here so that we can keep track of what type of phone this is so we got office cell and I'll just go ahead and make up facts you know because some people have effects and I'm going to give a short order of three and we'll see how that sort order plays in a little bit later but just it's a good idea to know that this you kind of want to do this so that you have some sort of organization to which one of these should show first in your displaying when we get into the forms section this will make a lot more sense but that will cover this a little a little bit more detail when we get to the forms I'm going to go ahead and exit out of that go back into our phone numbers okay so we've got ID one phone number zero zero one is a office number so there's our two as a cell phone number zero zero three is also a cell phone number with one two three as their IDs okay so let's go back out of this now let's open up that table that I created here I've already put some data in here and I'm going to show you here or the contacts customer our contact ID of one is me okay I don't have an email and either to Sam here but Sam is my coworker and I don't know if you if you can see this customer ID belongs to the customers table we both work for Metro properties just so you know what that relationship is both work for Metro properties Sam and I I have ID number one contact ID one Sam has contact ID number two so when we look at the phone the contacts underscore phone numbers table contact ID number one which is me I have phone number one right which if I open this up means you can reach me at 5,000 and then if I go back into here you'll see contact ID one which is me again can be reached at phone number ID number two which is ID number two phone number is five five five five five five zero zero - which is my cell phone number you see how that's starting to form together now we've got Sam Sam is contact ID number to his phone number he can be reached at is also number one notice here's my contact for my numbers a number you can reach me at he has the same phone number that he can be reached at so you can reach Sam at ID one which is zero zero zero zero one or you can also reach Sam at phone number ID number three which we look at the phone numbers is here's ID number three zero zero zero zero three so here we have the arrangement of who can you reach at what number you can reach me on phone number one or you can reach me on phone number two you can reach Sam on phone number one or you can reach them on phone number three here's his cell phone here's my cell phone here's both of us have the same number at the office see how unique that is see how great that is you can tie in all that information together just using these three little tables now the last thing I'm going to go over here about this situation is something called primary key or composite key I'm going to pop this open and design view here and I just want to show you real quick that we have these two fields both have the key next to them now this is what we call a composite key because I could certainly have put an ID gave it an auto number and you know made that my key I certainly could have done that but that would allow for the possibility that a contact with the same phone number could be entered in twice and how often is that going to happen or should it even happen I don't want that to happen so I don't really want a unique number to automatically be given to every single row in my table so I'm going to take this out and instead what I'm going to say is I know that there will never be a time where a contact should have the same phone number twice in this table it just should never happen so if I if I basically I just won't go back over that rope we're quick again what I did here there we go okay so I move the mouse up over here drag until I get until I'm highlighting both rows here click on primary key and that basically makes what we call a composite key that's basically saying this and this together will always be unique always okay and that's what that's what a key really needs to have everything needs to be unique about a primary key so I know that I will never have the same contact with the same fill number twice in this table if I do I want access to kick out an error okay it should never happen so that's what a composite key is is comprised of multiple multiple different fields to make up a unique way something that we know is going to be unique about that particular piece of information all right

Channel: Programming Made EZ

Views: 183,259

Rating: 4.6589146 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, beginner, advanced, software, microsoft access, table, query, form, code, coding, development, Computer Programming (Professional Field), Web Development (Project)

Id: d5mQYTVaq7c

Length: 12min 6sec (726 seconds)

Published: Sun Dec 22 2013