Relationships And Subdatasheets

Introduction:

The concept of relational database insures data reliability on the foundation of data moving from one source to another. There are many goals behind this concept. Data in your resources needs to be as much accurate as it can be. Provided your database is made of various components, mainly tables, you should avoid any redundancy possible, in other words, data from one source should be unique and almost never duplicate.

To accomplish these goals, you interrelate the various components of your database, namely tables (remember, and as you will see later, data in your database depends on, or is originating from, your tables).

Relationships:

Open the Music Collection 2 database.

When creating various tables in your database, if your first goal is to get necessary data, the second would be to provide accurate data. To succeed in that, you build relationships between and among tables so that they will collaborate and provide necessary data to one another.

Building tables relationships is one thing, controlling these relationships is another. This is where your efficiency in avoiding redundancy is revealed. For one table to provide data to another, each one of them should have unique data that the another table needs.

When you are creating a Lookup field, you are telling one table that the value entered in this particular field will come from another table, and you specify the originating table. The originating table is the parent table, the target table is the child table.

The reason you established Primary Keys in your tables is because these are the fields used to build relationships between tables. They are used to verify the uniqueness of data. Also, they avoid that data in relationship get mixed. You can build a reliable relationship only between data of the same kind.

1. Click the Relationship button on the toolbar. The Show Table property sheet comes up. From here, you will specify what tables (or queries) will be used when building your relationship (s). Click the tblMusicCategories once and click the Add button. Double-click the tblMusicAlbums; finally include the tblMusicTracks. Close the Show Table property sheet. Arrange the layout of your relationship window so that the tblMusicAlbums table be in the middle of the tblMusicCategories table on its left and the tblMusicTracks table on its right.
   
   The originating table uses its Primary Key and associates it to the field you choose in the target table. The target field is referred to as the Foreign Key.
2. Drag the MusicCategoryID field from the tblMusicCategory table to the MusicCategoryID field in the tblMusicAlbums table. The Edit Relationship dialog comes up. This allows you to confirm creating a relationship. Click the Create button to create the relationship. Now you have a line relating these two tables.
3. Drag the AlbumID field from the tblMusicAlbums table to the AlbumID in the tblMusicTracks. When manipulating data that is in a relationship, it is very important to make sure that data keeps its accuracy from one table or source to the other, to succeed in that purpose, you need to Enforce Referential Integrity so that when you change data in the parent table, data corresponding in the child table be changed/updated also; this is done through Cascade Update Related Fields. On the other hand, the Cascade Delete Related Records helps to delete data in the child field when the corresponding data has been deleted in the parent field. The relationship you establish between two tables creates a Subdatasheet which is a child table related to the parent table.
4. Click the Enforce Referential Integrity check. Now, the database would like to know how you will handle data updating and deletion. Check all the three check boxes, and click the Create button. Now you have a 1 on the parent field and the infinity sign on the child field. The 1 (One side relationship) means that the originating field, the parent, supplies the data that changes the value in the target. The infinity symbol (¥) means that many fields in the target table are affected by the data coming from the parent table.
5. Right click the relationship line between the tblMusicCategories table and the tblMusicAlbums table, then choose Edit Relationship... from the popup menu.
6. Click all the three check boxes. Click the Join Type button.
7. The Join Properties dialog allows you to specify the direction of the relationship. Click the second radio button that will allow the parent field in the tblMusicCategories to control the corresponding and related fields in the tblMusicAlbums. Click OK twice. Now you have not only the one-to-many sign, you also have an arrow reminding that data in the MusicCategory field of the tblMusicAlbum will come from the MusicCategory field of the tblMusicCategories table.
   
8. When you are finished with the Relationship Window, save and close it.

If you are using Microsoft Access 97, skip this Subdatasheet section.

Subdatasheets:

A subdatasheet is a sheet of data that you include in a parent table to allow you to view related data in order to have it handy.

You can insert another table or a query to an existing table as a subdatasheet as long as these two can exchange data through a relationship. For example, in our music collection database, when viewing the tblMusicAlbums table in Datasheet View, it would be helpful for the user to view the tracks that are part of an album. To make it happen, you can include a child table as a whole table or create a query that isolates necessary data and then insert the query as a subdatasheet.

Now that we have created necessary relationships in our database and eliminate redundancy, let's implement the concept of subdatasheet. To do that, we will include the tblMusicTracks table into the tblMusicAlbums. Since the tblMusicTracks table has the TrackID and the Notes fields that we don't need to display in our new table, we will isolate them (we will still need them in their own table). Thus, let's create a small and quick query.

1. From the main menu, click Insert -> Query.
2. From the New Query dialog, double-click Simple Query Wizard.
3. From the first page of the wizard, in the Tables/Queries combo box, choose the tblMusicTracks table.
4. Click the >> button to select all fields. Then click the < button (remove button) for the Notes field to remove it. (Now we have the TrackID, AlbumID, TrackNumber, TrackTitle, and Length fields only)
5. Click Next -> Next. Name the query qryMusicTracks. And click Finish.
6. Close the new query.

Now we will insert the qryMusicTracks query in the tblMusicAlbums as a subdatasheet.

1. From the Database Window, double-click the tblMusicAlbums table to open it in Datasheet View.
2. From the main menu, click Insert -> Subdatasheet...
3. The Insert Subdatasheet property sheet allows you to choose which object to include in your table. That object can be a table or a query. Click the Queries tab, then click qryMusicTracks and click OK.
4. Save, then close the tblMusicAlbums table.
5. Open the tblMusicCategories table in Datasheet View and insert a Subdatasheet from the tblMusicAlbums object.
6. Save, then close the tblMusicCategories table.

Exploring Relationships and Subdatasheets:

Now that the relationships have been established, isn't it time we see whether they work?

1. From the Database Window, double-click the tblMusicCategories table to open it in Datasheet View.
2. Type the first music category as New Age, the second will be Salsa, then Rock, and finally Soukous. In MS Access 2000, As you type a category, there is a new column on the table, filled with a + sign that shows you that the current record can display child data related to that record by the established relationship.
3. Then close the tblMusicCategories table.
4. Double-click the tblMusicAlbums table object to open it in Datasheet view.
5. For the Album Title, type Tribute and press Tab. In the Artist field, type Yanni and press Tab. When you get to the Music Category field, type New Age and press Tab. You receive an error because in fact Microsoft Access considers the field as a numbered item, and indeed it is a numbered item. For example, delete New Age, then type 1 and press Tab. It works... This would be perfect if on one end you had just a few albums to record (then may be you would remember the order of entry of each album or at least you would refer to the tblMusicCategories table). Remember, we are trying to eliminate redundancy. In other words, if you decide to get rid of the relationship (through a divorce court ?!?) and type your values here, you might end up with a nasty situation. The only solution is to continue and get it right. It is not difficult. I know you are thinking, "Why not use the Lookup Wizard and configure the field?", and I say why not fix it with a trick?" What we mostly need is a list of the items that we are supposed to choose from, as a matter of fact, we will use a combo box to display the appropriate list.
6. Delete the entry you typed for the Music Category and switch to Design View and click the MusicCategoryID field.
7. In the lower part of the Design View, click the Lookup tab. It shows us that the Display control is a Text Box. Click the Display Control's combo box and choose Combo Box from the list. Now we can choose between the originating table and a query to display something.
8. Click the Row Source, an ellipsis button appears. Click it to build a query that will be used to select what to display in the Music Category's field.
9. Since you are asked to build a query in Design View, from the Show Table property sheet, click tblMusicCategories. Click Add and click Close.
10. From the tblMusicCategories table, double-click MusicCategoryID, then double-click MusicCategory to select both items.
    
    Then, save and close the query. If you are asked to save, click Yes
11. After you have chosen the fields, you still have to tell Microsoft Access how you want to display them. We will bound only one column from the tblMusicCategories table to ours, so, set the Bound Column to 1. From the tblMusicCategories table, we will consider using two columns, the MusicCategoryID column to identify the originating table (remember, that column is holding the Primary Key) and the MusicCategory column. So, set the Column Count to 2. We don't want to display the column's title in the Combo box (we already have it in the column heading). So, keep the Column Heads to No. When we display the Music Category combo box, it is not important to display the Primary Key in this example because it is an AutoNumber (set by Microsoft Access) and doesn't mean much to us. There are situations where we might want to display it, for example when employee numbers or banks accounts customers are used as primary keys. For this example, we will hide the Primary Key. On the other hand, we will display the Music Category only. To accomplish this, we will set each column's width appropriately. So, set the Primary Key's column width to 0 and the other to a number greater than 0. In the Column Widths field, type 0";1". Depending on your intentions, you can control how many items the (Music Category) combo box can display at a time when it is dropped down; the suggested number is 8, let's keep it there. Keep the List Rows to 8. When the combo box drops down, sometimes you have narrow items, sometimes, you have wide ones. You can control how much space (width) is necessary by setting the List Width accordingly. For this example, set the List Width to 1. Finally, you can impose a number of items from the list to the data entry personnel or allow her to add items by typing new ones that are not in the list. This time, you should really decide how you are planning to do this. Here is an example. If you impose a list to the user, she cannot make a mistake in typing since she will only choose from the list. But if you allow a new entry in box, she might type Neo Age by mistake when New Age is already in the list; the database will allow it and your database will have two entries for Yanni and for the same album. I can hear you saying, "So what, that's not a big deal". now consider that this is a bank and at the end of the month, you find out that a certain customer has three bank accounts in your bank and nobody knows where those accounts are coming from... For this exercise, set the Limit To List to Yes.
12. Then save the tblMusicAlbums table and switch back to Datasheet View.
13. To continue our entry, type n in the Category for Yanni and press TAB. Microsoft Access will complete the Category entry with New Age. For the label, type Virgin. The year will be 1997. In the Format field, type CD. For the number of tracks, type 11. For the Notes, type Live Concert.
14. Now build a combo box field for the Album Title field in the tblMusicTracks table.

Junction Tables And Relationships:

We know that the main idea behind the relationships is to avoid redundancy of data by allowing some tables to provide data to others. Another kind of relationship, very important, is by creating a middle table whose purpose is to join other tables; actually, you don't type any data in it, you use different columns to choose existing data from other tables.

By now you have realized that wizards are very useful when a job seems easy, for example, they can help you create a relationship very quick and without any worries. To implement our new relationship, we will use the wizard to create a junction table.

1. Open the Videos3 database, and from the Database Window, double-click Create Table In Design View.
2. In the first field, type VideoJunctionID. Set it as the Primary Key formatted in AutoNumber and set its caption as Video Jct ID.
3. Save the table as tblVideoJunctions .
4. In the second field, type VideoID, press Tab, type L and press F6. This will call the Lookup Wizard.
5. In the first page of the Lookup Wizard, accept the first radio button and click Enter.
6. In the second page, choose tblVideos and press Enter.
7. In the third page, double-click VideoTitle and click Next.
8. In the fourth page, accept to Hide Key Column and click Next.
9. In the fifth page, label the column caption as Video Title and click Finish.
10. Accept to save the table. You should be in the lower part of the Design View because you pressed F6 before starting the wizard. Delete the default value of 0.
11. For the third field of the table, type ActorID, press Tab, type L and press F6.
12. Accept the first radio button of the wizard and click Enter.
13. Choose tblActors and click Next.
14. Click the >> button, and click Next.
15. In the fourth page, accept to Hide Key Column and click Next.
16. For the label the column, type Actor/Actress, and click Finish.
17. Accept to save the table. Delete the default value of 0.
18. Switch to Datasheet View.
19. In the Video Title, type be, press Tab. In the Actors/Actress field, type ed and press Tab twice.
20. For the second Video Title, type be, press Tab. In the Actors/Actress field, type he and press Tab twice.
21. Now you can join/connect different different actors to the movies the starred in. When you finish, close the tblVideoJunction table and close the Videos3 database.
22. Save and close everything.

Help In Microsoft Access:

1. Click the Office Assistant and type: What is a relationship?
2. Click Create Or Modify Relationships.
3. Click Learn About Relationships In A Database.