Eleven years later and I’ll say thanks for this video: it’s a really clear explanation.

I have struggled with this concept for such a long time, I should say I am a novice, and a self taught one at that, but your explanation, more than most others I have listened to or read previously has made something click and I feel a bit more confident about what a relationship is and what i need to do to make it work, thank you for a very simple explanation.

@Jonathan Cutting What you are looking at is called the Foreign Key which is the primary key it the originating table. There is one instance where you do see the same primary keys in two tables, thats called a Join Table and that is something different in regards to creating tables with data.

I love how clear u speak!

Superb

very helpful

I now understand thank you

This Video is Timeless!

The downloadable zipfile is fantastic. Thank you!

never before got it so clear Thanks a lot

HOLY CRAP!!!! I took this course last semester, and I needed a refresh of everything and as soon as you said think of it as parent and child, it all made sense!!! Thank you so much!

Nice and simplistic to understand...thanks!

But in general, if you see duplicated information in any other field of a table, you probably have an opportunity to improve the database by making a one-to-many relationship. The table with the duplicate values is always on the "many" or child side of the relationship. Pretty hard to clarify this well without a concrete example though.

Perfectly explained! Thanks for sharing

@Ryan&@Jonathan-- in the Order Details table the OrderID and ProductID fields individually are foreign key fields that help establish one-to-many relationships. The key symbol by BOTH means that the values in the two fields taken together must be unique, a composite primary key situation. Individually, OrderID and ProductID in the Order Details table are not primary key fields. The issue of a combination key is separate from the issue of a join table. Orders is also a join table but it has a single field primary key, OrderID.

This was so helpful, thank you

This is good and simple, but how do you create the actual relationship in Access...? Do you drag from primary key to foreign or the other way around, how do you reverse the relationship, etc? For me it's easy to just write the DDL but maintaining the runtime of Oracle or SQL Server would be way overkill. So I'm trying to go waaaaaaaaaaay back and figure out this Access tool, where you have a table, relationships, forms, reports, etc. all in one file...weird, but very cool for small and simple.

To get the data files -- search for the ISBN number of the book at the publisher's web site, cengage.com. The data files are a free download.

I wish you would show the end result so I can see what this actually accomplishes. It would help me out a lot with the project I am doing.

Thanks, but when speaking of the one-to-many relationships, the OrderID and ProductID fields in the Order Details table function as foreign key fields, not as primary key fields. The primary key field is always on the one side of a one-to-many relationship, never on the many side. The key symbols beside those fields in the Order Details table indicate that together, they create a combination key for that table. The values in both fields considered together must be unique for every record.

I am new to access , working on supplier and inventory model is there any links that I can auto transfer data in table 1 and table 2.

make it easy to understand . thanks for this good idea.

You said the primary key field is never on the "many side" of a relationship. I see two such instances in the table "Order Details." In fact, BOTH of those key fields are on the "many side" of a relationship, and neither of them are on the "one side."

hi im hoping you can help me. ive created 2 tables on my database on ms access. one is 'customer details' etc and one is 'completed' sales/deals. what i want to do is keep all records of customers for cars in the main big 'customer details' table and any completed deals from that table automatically added to the 'completed' table. i want to have a button or option like completed on the end of the table maybe like a yes or no column. by which i could click and it would automatically add the relevant fields i want to the completed table. i would like to know how i can go about that.

helps alot. Thanks

You're describing a classic many-to-many relationship. One inmate can be related to many families. But one family can be related to many inmates too. The solution to this is a junction table that sits between the Inmates and Families tables and is on the "many" side of a one-to-many relationship back to your two original tables (Inmates and Families). You might call it the Connections table (for lack of a better name). In the Connections table you place the TDCJ number as a foreign key field to the Inmates table. So one inmate now has many Connections. You also place the FamilyID field as a foreign key field in the Conections table (or whatever field you've designated as the primary key field in the Families table). So one Family can also have many connections which solves your original problem. Now one inmate can be related to many families and one family can be related to many inmates. Post again if you have questions on this. PS: The Order Items table is a sample in Northwind. It is the junction table between Orders and Products given Orders and Products have a many-to-many relationship.

Hi Lisa, great video.. Now what if the unique ID or the primary key has duplicates in one table and duplicates in multiple columns not all of the fields, what do you recommend to do?

Thank you :)

is there a way i can make something like a "conditional relationship"? .... i mean, if one field says "A" then the external key y linked to tableA and if it says "B" the external key y linked to tableB Thanks!!

"if the unique ID or the primary key has duplicates in one table" -- this is not possible. By definition, a primary key field may not have duplicate values in multiple records.

what version of msaccess do you use at this video? i am very interested to this interface. it is cool

Do you have this file for download and practice?

Please somebody help me how to create a relationship between the following..... Project Province Employee Thanks please help me i am very in need.

your voice is so nice!

I imagine Jodie Foster talking.