Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
There may be times when you don't have a field that makes a good primary key and you don't want to create a meaningless ID field. A third option is to combine two fields and make them a dual primary key combination. In other words, I can combine two fields that individually may contain duplicates, but the unique combination can only happen once. For example, if I'm offering a series of Access Classes, I need one table to store information about the Classes and the second table to store information about the Students.
The third table Attendance contains the tracking for their participation and that's where the multi-field primary key comes in, right-click on Classes to close it and right-click on Students to close it. In the Attendance table, we're going to set the first two fields with lookups, so they pull IDs from the other two tables, I go into Design View and under Student, I'll change the data type to Lookup Wizard. We want to Lookup field to get the values from another table, so we will click Next, we wanted to get the information from the Students table and I'll click Next.
We want to use the ID field, the Last Name and the First Name to identify the student, I'll click Next. Let's sort them by Last Name and I'll click Next and you have the option of hiding the key column, we'll go ahead and keep that hidden and click Next. We'll leave the field name as Student and except the rest of the defaults and click Finish, it wants to save the table, we'll say Yes.
Now let's look up the Classes from our Class table, in the Class data type, I'll change it to Lookup Wizard and we'll go through the same steps, we want to get the values from another table, so I'll click Next. We're going to Lookup our Classes and I'll click Next. Again, we're going to use the ID number, the Class ID and Class Name and say Next, this time we're not going to sort the Classes, let's just say Next looks up this default as well and we'll leave the field name as Class, click Finish.
Again, I want to save the table and we'll Yes. Now when that's done, click on the box next to Student named, Hold our down and drag down to Class, so that both are highlighted, up on the ribbon go to the Primary Key button on the upper left and click on it. Now we have two keys on both of these two fields indicating that together, this student in that Class is the unique and currents, each student can only take the class one time.
Changeover to Datasheet View, we'll Save the table and let's add some records, our first student will put in the first class on a particular date, we'll add another student, a different student to that same class on the same date and now we'll add the first student to a different class on a later date and now here when the dual key comes in.
If I tried to add Chaney back to that same intermediate Access Class, it will give me an error message; the changes I requested to the table were not successful, because they would create a duplicate value in the primary key, or relationship. So it's telling me that that student has already been registered for that class, I'll click OK, so I'll go back into the Student and add them to a different class and it accepts that change.
So you can say that class can be listed several times and the student can be listed several times, but the Class and the Student together create the unique occurrence.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74465 Viewers
80 Video lessons · 129695 Viewers
52 Video lessons · 63939 Viewers
59 Video lessons · 49720 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.