I have to agree with the others, this is a good explanation. I've never heard of a junction table before, let alone seen one. I built a students database back around 2003 for adding historical records from around 1900 and it worked well. But I upgraded to a new database version and my data corrupted. I tried a couple of times to rebuild it and I thought I had it working until I noticed corruption again. Records I was entering were being written to another students information. I decided to see if anyone built a students database I can adapt and found yours. I have created a portion of your database and adapted it. Instead of Clubs I have schools. It's not quite how I want it, so I will make more changes and see the results. Thanks for showing me something new and the easy to understand tutorial.
This is a good explanation. Particularly how data flows through the joins is well explained and limitations made clear through the use of a query. Well done !
Nice explanation, but this is only half of the story. You normally need a form to perform such activity. Rarely someone is writing directly to the table. So how does one link multiple records in subform to a record in a main form. lets say via tick box.
First off a many to many relationship needs a junction table. If it's a junction table between two tables it must have two foreign keys. These foreign keys can only relate to a single key in another table. Generally these tables will have a single primary key. But there is an exception. You can have a duplicate primary key. These are two fields that on their own do not provide uniqueness for every record in the table. But if you look at both keys together the values of both fields are not repeated anywhere else in the table and therefore provide uniqueness. Hence you define both keys together as the primary key. But it's still described as a single primary key. Therefore the short version to answer your question is no, you can't have two primary keys in one table.
Watched all of your videos, thanks. How would design a "ReportsTo table" relation to an "employee table", where employees can report to each other for a certain period of time?
This is interesting. You should create a table names reportsto with two foreign keys. Both of these should relate to the primary key of the employees table. You probably want a start date and end date column for recording all the different instances of reporting. This will allow multiple report relationships to be recorded.
Many Thanks that worked... sweating already. My "ReportsTo" table has 5 columns: ID, RoleID, ReportsToID, ReportFrom, ReportsTo. Now I´m stuck with the following: How can I add a column automatically displaying the manager´s name based on ReportsToID? It should be picked up from the employee´s table, but how...
Flo Rian Don't add a column to the table. Use a query to combine the reports to table and the employee table and pull the managers name from the employee table via the reportsto ID foreign key. Base your form to edit this on the query
I have to agree with the others, this is a good explanation. I've never heard of a junction table before, let alone seen one. I built a students database back around 2003 for adding historical records from around 1900 and it worked well. But I upgraded to a new database version and my data corrupted. I tried a couple of times to rebuild it and I thought I had it working until I noticed corruption again. Records I was entering were being written to another students information. I decided to see if anyone built a students database I can adapt and found yours. I have created a portion of your database and adapted it. Instead of Clubs I have schools. It's not quite how I want it, so I will make more changes and see the results. Thanks for showing me something new and the easy to understand tutorial.
Thanks. That's an interesting project 🤔.
This series of tutorials on data relationships has been very informative and made easy to understand. Thanks.
One of the best explanations of Many-to-Many I've seen! This makes it much more understandable. Thanks
This is a good explanation. Particularly how data flows through the joins is well explained and limitations made clear through the use of a query. Well done !
You made my day!!! thanks for making it so easy
Very good and understandable explanation
It has helped me greatly, Thank you!
You are the best
Thank you, been trying to figure out how to do this. You explained how to do it and why to do it how you are showing. Made sense.
Thank u so much
Nice explanation, but this is only half of the story. You normally need a form to perform such activity. Rarely someone is writing directly to the table. So how does one link multiple records in subform to a record in a main form. lets say via tick box.
+joebox9999 the form would be another tutorial. A simple form would not suffice. It would depend on the context and the ux desired.
@@QVisible Have you done this form tutorial yet? Please I need it so bad :)
Sorry. I don't have tutorial on this.
thank you sir!
awesome explaination... but sir I have a question that can one table have 2 primary keys in many to many relationships???
First off a many to many relationship needs a junction table. If it's a junction table between two tables it must have two foreign keys. These foreign keys can only relate to a single key in another table.
Generally these tables will have a single primary key. But there is an exception. You can have a duplicate primary key. These are two fields that on their own do not provide uniqueness for every record in the table. But if you look at both keys together the values of both fields are not repeated anywhere else in the table and therefore provide uniqueness. Hence you define both keys together as the primary key. But it's still described as a single primary key.
Therefore the short version to answer your question is no, you can't have two primary keys in one table.
thank you very much sir.... I made many to many relationship as u answer .... :-)
Watched all of your videos, thanks. How would design a "ReportsTo table" relation to an "employee table", where employees can report to each other for a certain period of time?
This is interesting. You should create a table names reportsto with two foreign keys. Both of these should relate to the primary key of the employees table. You probably want a start date and end date column for recording all the different instances of reporting. This will allow multiple report relationships to be recorded.
Many Thanks that worked... sweating already.
My "ReportsTo" table has 5 columns: ID, RoleID, ReportsToID, ReportFrom, ReportsTo.
Now I´m stuck with the following: How can I add a column automatically displaying the manager´s name based on ReportsToID? It should be picked up from the employee´s table, but how...
Flo Rian Don't add a column to the table. Use a query to combine the reports to table and the employee table and pull the managers name from the employee table via the reportsto ID foreign key. Base your form to edit this on the query
idk why when i make relations theres no arrow that connects them
Just as you drag and drop the relationship, In the edit relationships box, make sure you check on 'enforce referential integrity' checkbox.
very helpful thank you!
Sir very nice but i have a question does the clubs means(classes)
Clubs that students join as part of the college/university experience. Clubs and societies after class time.