The essential procedure is to create and select a unique primary key field. Here are three examples with illustrations that clarify.
Here is a database that relates four sprinkler timers to several stations at each timer. Each timer has four to six stations (see below) making a one (timer) to many (stations) type of relationship. There are two tables for this database.
The "Timer Table" and the "Station Table" are shown below. Note that Timer is in boldface in the "Timer Table", whereas it is not so bold in the "Station Table". This is because I have selected Timer to be the primary key in the "Timer Table" but not in the "Station Table". This illustrates a very important characteristic of the primary key. The primary key must be a field where each entry is unique. In the "Timer Table", there are four entries in the Timer field: 4a; 4b; 6a; and 6b. Each of the four entries in the Timer field is unique.
In the illustration above, the "Station Table" shows that its primary key is called ID. I chose that field because I needed a primary key field with unique entries, and the Access AutoNumber feature worked just fine for this. The "Station Table" has 14 stations distributed among the four timers. Below is a table showing each timer with its station numbers. You should note that station numbers and timer codes are not unique in the "Station Table". That is why I couldn't use the Station field for my primary key.
|
ID (Unique entry for each record) |
(The Timer code is entered many times) |
Stations (Many stations for one timer) |
| 1 | 4a | 1 |
| 2 | 4a | 2 |
| 3 | 4a | 3 |
| 4 | 4a | 4 |
| 5 | 6a | 1 |
| 6 | 6a | 2 |
| 7 | 4b | 1 |
| 8 | 4b | 2 |
| 9 | 4b | 3 |
| 10 | 4b | 4 |
| 11 | 6b | 3 |
| 12 | 6b | 4 |
| 13 | 6b | 5 |
| 14 | 6b | 6 |
To create the One-to-Many relationship, I opened the "Relationship Window" in Access, placed the two tables in the window, and simply dragged my Timer field from the "Timer Table" and dropped in on the Timer field in the "Station Table". To review the procedure for setting up the relationships, you can revisit the "Recording Table" webpage. Click on your browser back button to return to this page.
For this database, I had several tables that I wanted to relate. The illustration below shows the tables and their relationships.
You can see that the table entitled "iMET Student C..." has a primary key named StudentID in boldface. That primary key contains, of course, unique entries. In other words, each student has a unique number making his or her entire record unique for that table. But notice also that StudentID is related to the StudentID field in both the "Grades Table" and the "Mini-assignments" tables. The field named "StudentID" is not unique in those related tables, because there are many courses, grades and mini-assignments, etc. to which each student is connected. This means that even though each student is an individual, his or her unique, assigned student identification number (StudentID) appears many times, once for each of the many requirements and obligations (records) contained in this database. So, in order for Access to recognize that one student will have many records, I created the primary key field called ID with AutoNumber as its data type . It allows each record to be unique no matter how many entries are created in the StudentID field for each of the two related tables. Below is a table that shows some of the messy complications of this relationship.
|
Surname (iMET Student Contact table) |
StudentID
(Grades Table) |
Courses (CourseID)
(Grades Table) |
Grades
(Grades Table) |
| Smith | 1 | 280 | A |
| 1 | 250 | A- | |
| 1 | 281 | B+ | |
| O'Connor | 2 | 280 | A |
| 2 | 250 | B+ | |
| 2 | 281 | A | |
| Prakash | 3 | 280 | B+ |
| 3 | 250 | A | |
| 3 | 281 | A- | |
| Drake | 4 | 280 | A |
| 4 | 250 | A- | |
| 4 | 281 | A- | |
| Smith | 5 | 280 | A- |
| 5 | 250 | A | |
| 5 | 281 | A |
Notice how often entries are repeated in the table above. The StudentID appears several times, once for each course taken per student. The CourseID appears three times for each student, because each student took three courses. The grades are certainly not unique, but none of this could be used as a primary key. The ID field was created for this reason in both the "Grades Table" and the "Mini-assignments" table. The unique field for the "Courses" table is the CourseID.
Below is a simpler illustration.
Here, as is explained in the "Recording Table" webpage, I needed to be able to show a relationship between an artist and his or her many recordings. The primary key in the "Artists" table is ArtistID which is also an autoNumber data type. Note that ArtistID has a related field named ArtistID which is not the primary key in the "Recordings" table. This is simply because each artist must have many entries in the "Recording" table in order to note all of his or her (or the band's) creations.
Back to the Recording Table.
With this explained, now you can proceed to the next Access web-based lesson, "Creating a Form".