i agree. it's super hard to recreate it when you don't know the exact settings he has for each table. then you get errors and don't know how to fix them and then you are just stuck not knowing what to do
Steve, for some reason there are extra tables on this video. Just wanted to point out to you that there are people that will probably follow along with you and not just watch the video. That being said, it is not a good idea to add anything to your database and not let your viewers know about the changes. The best way to learn this from you is not only to watch but to copy it and then use what you learn afterwards and practice. The way you set up your videos is for people that just want to watch and that's not the best way to learn this. I don't know if you'll take this advice, but I feel like your viewers should be aware that someone is concerned about the best way to learn this material and that the small changes makes it harder to do so for people trying to create what you create. I look forward to your response.
Jaz Isom Sorry, don't know what to tell ya. I am trying to make sure everything of importance is mentioned. Hopefully you find the later videos more valuable.
It is really hard work to keep up with your changes in databases. To reach both audiences it would have been an good idea to show the settings for each table for 5 seconds at the beginning of each video. Mentioning this technique beforehand and everybody who wants to copy your exercises has the chance to press pause and the others are not interrupted for too long.
Ii am relearning Access and am using the 2016 version. This series is the best I have seen, and believe me I have seen a lot. Mr Bishop your series is comprehensive but not overwhelming and you explain it in layman's terms. Hat's off to you sir...
AN EXCEPTIONAL LESSON. i finally get it, to create a 'many' to 'many' relationship - essentially need another table - and the concept of composite key --- many thanks Steve ..
Thanks a lot. Specially this explanation about many to many relationship helped me a lot. I heard about it before. But right now I realized that I am applying it in a wrong way for my database. Also I learned the meaning of using two primary keys on the same table just from you by now. 🙏🙏🙏🙏🙏🙏🙏🙏👍👍👍👍
im new i was following along great until all the new mystery tables and things happened. i spent so much time trying to follow whats new that i forgot almost everything that led up to this point.
While it has been said before, I am extremely frustrated at all the new tables that showed up between lessons. I too am trying to match the database being created. It is clear to me that this will not be possible. I was able to add the first two (unexplained) additions but it is clear that I should anticipate many barriers to duplicating the database on my own throughout the lessons. It would have been helpful to have a screen that lists all the stuff you created while we were not watching so we could keep up. Signing off to find another resource...
Hi Steve, I really want to thank so much for those great videos which is very useful to me and to anyone as well, im still in video 6 and i learned a lot, i will continue the rest of your videos. Thank you again
Hey again! As earlier commented, it would be really helpful if you illustrated how to populate these tables with forms. I still have no clue on how to update the connecting tables from forms.
The fact you have been prompted with ID on one side and Customer_ID in the other is because you manually dragged the "ID" from on table and dropped it over the "Customer_ID" field directly and therefore Access makes that relationship.
One point to my previous point. I'm not sure you mentioned how to key two fields. I shouldn't have to look up on Google how to do this. However, not that big of a deal. I just like to pay attention to details.
If I understand this correctly, two fields on a data entry form should be bound to the two fields in the tbl3Contacts_PhoneNumbers in order to make this relationship work.
Hi Steve. GREAT SERIES! Thank you!!!!!! I must have blinked... each of the few times I tried to go back and "see it again"... How did you assign two primary keys to the tbl3Contacts_PhoneNumbers table?
Not sure if you still need this answer but while in the table design select the first item then press and hold down the CTRL key to select the 2nd item then from the Tools group select "Primary Key"
Excellent video, you do such a great job. Question, could adding the phone numbers tbl been done with just a 1 to many relationship table instead of adding the link table? Just trying to wrap my head around this, its a mind bender. I think I need something like this link for a couple of my tables.
I'm trying to follow along and make my own database as you go. Not sure why you added all these tables without showing all the design info for them. Hard to follow along when you jump ahead too much.
Downloaded your file from Google Drive, added all of the information that you inputted into your database in a separate database to recreate this link up for Many to Many Relationships. Had an issue with when I added tbl1Contacts to my relationships not automatically linking with tbl1Customers. Could this be because I am using Microsoft Access 2010? I am a serious newbie with Access, so I need all the help I can get ;-)
Thanks for the videos. I do have one question for you regarding the structure of your tables. I totally understand having an address table separate from the customer contact table. I don't quite understand why you would create a separate table for phone numbers. Wouldn't it be easier to just create a field in the contact information for the employee since a phone number doesn't contain multiple fields like the addresses for a business?
+Thomas Freundl Because there is actually more to a phone number. A phone might be a cell phone, a home phone, a business phone, a fax.... etc. There might also be an extension. Also, one phone might belong to multiple people, and one person might have multiple phone numbers.
It's great that you put this together for people to learn, BUT, it's super hard/impossible to recreate what you've done if you don't show explicitly what you are doing. For example tbl3ContactsPhoneNumbers has 2 primary key icons. So I have to decide to guess and put two primary keys (somehow?) on this table or just watch it and see if that is important. So thanks, I appreciate the effort and it's helpful but I may try to find a different series to start with since I really can't follow along. I have learned some important things, just may have to cut my losses at this point. This video is old, so maybe you improved on this or abandoned youtube education. I'm going to check now. Thanks for the content. You are a good teacher, just could design with the idea that people want to follow along with what you are doing.
Hey Steve, so far these videos have been really helpful, but like many others I learn by doing a lot quicker than seeing. I develop muscle memory more easily than actual memory. that being said, I've re-watched this video several times trying to catch up, but a couple things are tripping me up. I guess some main questions would be, is there a keyboard shortcut for when you're adding the tables to the Relationships sheet so that they automatically know which tables should be connected to which and whether they should have arrows or instead of dots connecting them? Or are those connection created automatically because of some kind of mapping done when you created the tables to begin with? Also, Is there a way to edit the relationship type that appears automatically? Such as From Indeterminate to One-to-Many? I know this video has been out for a while, but I still hope you are able to answer so I can get a better understanding going forward in the videos. Thanks!
Hi Steve, I'm finding your videos really useful to help me learn Access. I want to create a database where there is a table of candidates who could have three job role options following an interview. These roles would come from a table of generic roles. I can see how an interface table to match many candidates having many roles can be created. The problem I'm having is understanding how a form can be used to capture the role options by name and associating the role IDs to them in the interface table? I'm obviously getting confused somehow. Can you help me please?
Steve, thanks for all your hard work. I've been playing around with Access for a while and basically understand relationships between tables. Is there an easy way to enter data into tbl3Contacts_PhoneNumbers_when you have perhaps thousands of contacts and multiple phoneNumber_Ids without jumping between different tables?
Hi Steve. I'm trying to wrap my head around the many to many relations but it seems I have ran into some issues and I don't think if I would have to look into VB to complete what I am doing or if the many to many relations will work for what I need it to do. Here is my problem. This is for a manufacturing style database. Completed Product -Parts So the completed Product is a finished product but containing many smaller parts with their own part numbers. So I will be having many Finished Products with many smaller products that can be used on any of the Finished Products. Hopefully that's understandable. Let me know what you think. Thanks.
+kong vue Nevermind. I was able to get it with a junction table. But now running into another issue. I'm created a form to add multiple parts to it's finish product but it will not do it. It will only add one part at a time, which is a time killer. Do you know if there are any other ways I would be able to add multiple parts to my junction table through a form. Thanks.
Very useful thanks. I do have a question though. How do you manage multiple many to many relationships? For example if I have a relationship where a student is enrolled on a course, the course has different options and each of the options has different sessions. How would I link the tables so that I could run a query which would provide the times of day a student is attending class? The way I have done it is to create a linking table with fields for the student id, course id, option id and session id...but is there an easier way?
There is no recognition mystery as to the foreign key! You manually indicated the relation when you dragged and dropped the fields onto each other, hence the relation. Access just obediently set it up the way you indicated by your dragging action.
Why did you add more table instead of adding more field at [tbl1Contacts] such as: *ID *Customer_ID *FirstName *LastName *Email *PhoneNUmber *PhoneType_ID then PhoneType_ID related to [PhoneType_ID's table]. So, what is your reason?
Because phone numbers are considered a separate entitiy from a contact. A contact can have an unknown number of phones associated with them. They might have a home, cell, fax, office, etc. But rather than try to create a column for each one, just use a separate table to track the phone numbers.
Hello. You explained how to create the many-to-many relationship by adding a joint table. But now, how do we use it? How do you query the phone number of Sam?
hi steve, i have a table setup already that has the customer info, addresses and phone numbers on one table. how can i split the table since i have nearly 80,000 records already setup in this format and i would hate to have to reenter the info in different tables such as address table, contact info table and tie them to the customer. i originally entered it all in through an import from an excel sheet. so any help on this would be great.
If the relationships table does not have it's own primary key by-which it is sorted, doesn't it create the potential for the database to become much slower at a large number of records? If the key itself is not the entity by-which the table is sorted, I'd expect that a binary search would become impossible and loading a person's phone number could become a cumbersome task. Is there something I'm missing?
Kaden Burgart That's why grouping the foreign keys together make up a composite key which is indexed and makes for faster retrieval. You could make a primary key for the many-to-many relationship table, and in some cases it might even be a good idea. Perhaps you want to be able to identify each relationship by it's Primary key somewhere else.
Interesting. I think I'll have to learn more about indexing in order to have a proper understanding. Thank you very much for the video and for your reply.
Steve, Great videos! They have been very helpful getting me started. I have a project I am working on where I will be pulling data into a number of fields on a form from the same table. I am not sure how to set up the relationships or even if using a separate table is the best way to go. I was hoping you would be willing to offer some guidance.
There are two ways this is done. You can either write a query (I go over queries later) that brings in all the data from all the different tables, and displays the data in the form... or you can write a different type of query which makes a temporary table and writes the data to it. Then you link the form to the temp table.
Programming I am not sure a query is what I need either. I want to have the user enter some data in the form. Then check if that data is in the other table. If is it I want to pull all the related data in from the other table. If not I want the user to enter it.
Kethri66 I think you need to consider very carefully how you're going to go about determining new data from updating. What if someone mistypes a word, do you want to end up creating a whole new record? So you need to consider procedure as well as crafting the code to deal with the data. What is your primary key?
Programming Perhaps I am going about this all wrong. I hope I can explain this. I want to have a form where I can enter information into some fields and have it go to table "a" and into other fields and have it go to table "b". The primary key for table "a" is an autonumber. The primary key for table "b" will be a "product id number". Since there are 100's of product id numbers I can't enter them all at once. I though I could enter them into table "b" via the form as they are needed. But I need an easy way to check if the product id number is already in table "b" before or preferably while it is entered. Each of the items in table "a" will be associated with multiple product id numbers and each product id number with be used in multiple items in table "a". I will also need a report to show all the product id numbers used with a particular autonumber from table "a" and a report to show all the autonumber from table "a" associated with a particular product id number.
Kethri66 Well first off the Primary Key is unique by definition. So when you go to add a new product and you try to use a product ID that is already in use, you'll get an error from Access telling you that you can't make the record. So really as long as you set it as the primary key, Access will make sure you don't have duplicates without doing any coding. However, it sounds like you're trying to enter a record into table A but making sure one of the values in your form doesn't already exist in table B. That will require some coding. You could probably figure it out once you get to the recordset portion of the videos, but it is a bit more of an advanced topic I plan on going over in my advanced course.
+Brian Galante That is really a matter of personal preference to you. In my personal opinion I prefer using a composite key since it means you don't need the extra column to hold the primary key data, plus the constraints are automatically placed on the fields. Sometimes though, if there are plans to use the database for other purposes like a website, composite keys aren't always dealt with properly so that may be something you need to consider.
Greetings, I am in the midst of creating a hospital database. I have tbl for patients (tblpatients) and a tbl for procedures (tbl procedures) that the patients may go through. One patient may go through many procedures OR a single procedure is linked to many patients. Q1: Because both fields in the bridge table are primary keys can I uncheck the "unique" box because there may come a time when a patient may have to do the same procedure just on another day. Q2: Can I create a form so that the user can access these records and see them in a text datatype? This would surely prevent them having to flip through tables and scroll constantly. For example: ID1 - went through procedure 2 and 3. Create a form that will show the patient name, and what procedure 2 and 3 were. Am I over complicating this?
+Alex Buchny As for Question 1 yes you can, but it sounds like you aren't setting up the bridge table properly. The two values TOGETHER make up a unique value. I suggest you add a 3rd value though to your bridge table that will help make it unique. Something like the day of the operation. As for Question 2, your form is going to need some VBA to make it perform the way you want it to. I suggest you keep watching the series all the way through so you can better understand how it all ties together.
Hello. Why don't use only 1 joint table? Only one table with a phones field and a names field. I mean why do we need the names table and the phones table?
This is for demonstration only. For your particular application you will need to go beyond the specifics of these videos. However, if you have a specific question there is a really good forum I recommend www.accessforums.net
Hello Steve-- How can I get a copy of the tables that you created for this series? I see something about GITHUB but is dont know how it works Regards fred fromSouth africa
Hey Steve, I was able to develop my insurance agency a slim-n-fast CRM system using your videos (and lot's of dedication - not to tap my own shoulder... :-) ) you are a real MS Access Guru! Love ya! Anyway, I encountered an issue trying to make a copy of a record in a table with its related records in the associate table (I use an append query from [tableX] to the same [tableX]) - when I copy it, the new record (with a new ID num in the PK) does not have the related records from the associate table as the original record. Is there something I can do to ensure that the related records follow the copied record? I appreciate your saged answer!
Make a select query of all the data from both tables, and be sure to include both primary keys. Then, do an insert into query on that select query instead of a table.
Thanks Steve! I'm going to give it a try! Just to make sure I understand - you mean to take the tableX and the associate table or tableX and tableY? (their real names are X= tblPolicies and Y= tblProfessions and the associate table is tblProfessionsInPolicy) which ones should I use on the select query?
+RaSaSha Ara-Aroosh I realize now, after a couple of years removed from making this video that I wished I had covered cascades and referential, but back then I was more focused on explaining the relationships themselves. At one point later on I do talk about cascading changes, but I don't recall which video.
+Programming Your videos have really helped me learn access very quickly to the point of me actually understanding it! I keep watching your videos over and over for hours and now it's getting me pretty good at access. Thanks for making these videos you've made a huge positive in my life! :)
Great Tutorials man, but what the heck did you click at 05:15 to get the dialog box with "open tabel" and after choosing a table the relationship-lines are suddenly there. It doesn't do that with me. What type of relationships are they?
I have the same question. I understand you right click and then choose the table but how does the relationship line magically appear on yours but not on mine?
Hi Steve, Thanks for the videos! I am running into an issue when I try this many to many relationship. Basically what I have is a CompanyTable, a PhoneNumberTable, and an EmployeesTable. I want some sort of many to many relationship, because sometimes I have a Compny phone but no employee, or an employee with multimple phone lines. I tried adding a 4th table with 3 "primary" keys analog to your second table with 2 keys here. but when I run a query nothing happens. I am guessing I created some weird infinite loop. Do you know how to fix this? how to arrange those tables? Thanks
Martin Villegas Without seeing your database I really wouldn't know. I suggest trying to get some more specific help from the folks at accessforums.net. They can help you out.
Your forms the users interact with will populate the data. So, for example. You can give the user a form that allows them to add an address to a contact. When the user clicks on save, you have code which adds the records to the table for them.
Hi, Please Help. Im trying to create a many to many relationship just like on your video and i keep getting this error when I go into the table "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the experssion to variables". I dont know what this means and really need this database. Thank You
Thanks.. I dont know what happened... I watched 4 and 5 and audio was working fine then watched more and none of them working for me. I tested another video from one of my subscriptions and that works fine, I cant work out why it would just be your tutorials... ? A mystery...
Hi Steve! Pretty good job your explanations! It is exactly what I needed: Many-To-Many relationship for my problem. However, I still have a problem in showing the data. Here is my problem: I have a list of title's books and a list of author. One book can have one author or many in the list and one author can be affiliated with many books. So till now everything is fine: a typical Many-To-Many problem. However, if I want a table now, that shows for each title, the authors in a concatenated (or not) way, how can I do that? I was looking for that in the queries, but did not find it. It might be great to have both solutions (separated and/or concatenated in a single row): book1 author1 author2 authors3 book2 author1 author4 Or (whith comas) book1 author1, author2, authors3 book2 author1, author4 Do you think it is possible to do something like that? I would be very thankful to you if you can help me. Greetings from Europe!
elfo2285 Well first off, NEVER EVER EVER EVER give your users access to a table directly. I don't even like displaying queries to them. Everything you show the user should be either in a Form or a Report. As for your specific question, you will want to do a query, not a table to show this. So I recommend you keep watching the series to see how queries work.
Programming Steve. Thanks for you prompt reply! Of course I want to do a query. For the queries, I did watch everything!!! Maybe I am mistaken, but I did not find a video where you handle directly many-to-many relationships in the tutorials. Isn't it? How to "concatante" the authors, as explained in my previous question, this I do not know. Do you know how to do it? I would be very thankful to you!!!
elfo2285 The basic concept is to use inner joins on the primary key and foreign key fields. Then put the fields you want to return from each table in your SELECT statement. SELECT Authors.AuthorName, Books.BookName FROM (Authors INNER JOIN Authors_Books ON Authors.ID = Authors_Books.AuthorID) INNER JOIN Books ON Authors_Books.BookID = Books.ID
what is the idea of building a DB has an Employee and EmployeeRelatives tables and it might be a relationship between one or more employee at the same table. I mean there is an Employee1 and his brother is employee2 and Employee3( employee2's wife ) I want set that Employee1 is brother of Employee2 and Employee2 is a husband for Employee3. and The MOST IMPORTANT thing I need to make my app recognize that Employee1 is relative for Employee2 and Employee3
+Hamdy El-Shahat So then a Many to Many relationship sounds like the right way to go. A third joining table that stores the relationships between the different employees. Just think of the employee table as both the table that holds the primary data and the secondary data. Then the joining table has two values, both relate to the primary key of the employee. The first foreign key is the primary employee, the second foreign key is the relative of the primary employee. If you wanted you could add a third column that explains what the relationship is, friend, brother, sister, wife, etc.
+Programming sorry to to bother you But I really need your help I created 2tables. 1st is Employees has only employye's name. 2nd is relationships. and has 3 columns ( first emp,secondemp, typeofRelationship) ------ I added these employees' names in employees table Smith,John,Peter, Sarah ------ in the 2nd table I set relationship between these employees Smith is John's father John is Peter's brother Sarah is Peter's wife --- NOTICE that I didn't set that either smith is peter's father or smith is Sarah's husband's father --- I need to create query shows that Smith is father of Peter and John Sarah is Peter's wie AND relative of John.
I love all the videos but man this stuff is a brain twister- One day I think I got it, the next day all the work in my mind has been obliterated. The most confusing part of these videos is your emphasis on the integers of ID's instead of focusing on the content that the integer represents. I want to build this database, and it has many tables, but nothing crazy, and I cant get past the relationships part to save my life, for any more than 5 minutes. No one, and I mean no one will recall what ID # means cell phone or main phone or secondary phone or home phone etc... Users remember the content, not the ID #'s. I know you are tailoring this to programmers, but it would be nice to see a little more users perspective teaching. Like I said, I love ALL your videos, and I am determined to figure this out.
+Matt Canepa Users aren't going to know ID's, or at least it's not the objective for them to know. Understand that you HAVE to make each record of a table unique in some way. You cannot have two rows contain the same identical data. When you start to break data down into it's smaller bits (through normalization), the chances of having data collision is much higher. For example, there may be more than one person named John Smith. Even if you include a middle name, there is still a chance 2 or more people might have the same first, middle, and last name. So we add an ID column so that we can ensure each record has something unique about it. Then when you go to associate say and address with that person, your address table will need to know which John Smith you're talking about. So you associate that address with the ID value of the John Smith you want. This is done PROGRAMMATICALLY through either VBA or a Query. it is NOT up to the user to do this. It is up to you, as the coder of the application to make sure that when an address is added to the database, you allow the user to select the correct person. Typically through a ComboBox of names or a listbox, or even better they should already be looking at a form of the person they want to add an address to so that there can be no confusion to the user in case there are multiples of the same name. Either way, you have to make a form that is easy for the user to interpret. Forms are built to manage the user interaction with the data. The user should NEVER EVER EVER be entering data into tables directly. And I mean NEVER. Doing otherwise is just laziness and can cause massive problems with data integrity down the road.
I actually had the same "complaint" about this video. What the above poster is asking, is why do you use Type_ID numbers, instead of just writing out "Office" "Cell" "Fax"? Knowing a little about Access myself, I understand that a lot of these beginning tutorials won't make sense until the UserForms are introduced. However, creating tables with both an ID and a Type_ID, then another related table where the Type_IDs are broken seem needlessly complicated. I understand that John Smith may have 2 different cell phone numbers, but why does "Cell Phone" need to be represented by a #2? Why can't you just classify it a "Cell"?
That is, I mean that between theoretical explanations you need to show how this works For example, I didn’t understand how to make relations between tables; I’ll have to watch another video.
