Article Index
Microsoft Access Tutorial
Queries
Creating A Database
Tables
Creating Tables
Queries
Forms In Microsoft Access
Reports In Microsoft Access
Data Access Pages
Controlling The User's Input
Subdatasheets
Designing Forms
Exploring And Analyzing Data
Tables Looks
Design And Exploration
Forms Design Propertie
Controlling Data Output
Microsoft Access Controls
Design And Improvements
Importing And Exporting Data
Macros
Switchboards

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.

From the main menu, click Insert -> Query.

From the New Query dialog, double-click Simple Query Wizard.

From the first page of the wizard, in the Tables/Queries combo box, choose the tblMusicTracks table.

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)

Click Next -> Next. Name the query qryMusicTracks. And click Finish.

Close the new query.

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

From the Database Window, double-click the tblMusicAlbums table to open it in Datasheet View.

From the main menu, click Insert -> Subdatasheet...

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.

Save, then close the tblMusicAlbums table.

Open the tblMusicCategories table in Datasheet View and insert a Subdatasheet from the tblMusicAlbums object.

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?

From the Database Window, double-click the tblMusicCategories table to open it in Datasheet View.

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.

Then close the tblMusicCategories table.

Double-click the tblMusicAlbums table object to open it in Datasheet view.

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.

Delete the entry you typed for the Music Category and switch to Design View and click the MusicCategoryID field.

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.

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.

Since you are asked to build a query in Design View, from the Show Table property sheet, click tblMusicCategories. Click Add and click Close.

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.

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.

Then save the tblMusicAlbums table and switch back to Datasheet View.

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.

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.

Open the Videos3 database, and from the Database Window, double-click Create Table In Design View.

In the first field, type VideoJunctionID. Set it as the Primary Key formatted in AutoNumber and set its caption as Video Jct ID.

Save the table as tblVideoJunctions.

In the second field, type VideoID, press Tab, type L and press F6. This will call the Lookup Wizard.

In the first page of the Lookup Wizard, accept the first radio button and click Enter.

In the second page, choose tblVideos and press Enter.

In the third page, double-click VideoTitle and click Next.

In the fourth page, accept to Hide Key Column and click Next.

In the fifth page, label the column caption as Video Title and click Finish.

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.

For the third field of the table, type ActorID, press Tab, type L and press F6.

Accept the first radio button of the wizard and click Enter.

Choose tblActors and click Next.

Click the >> button, and click Next.

In the fourth page, accept to Hide Key Column and click Next.

For the label the column, type Actor/Actress, and click Finish.

Accept to save the table. Delete the default value of 0.

Switch to Datasheet View.

In the Video Title, type be, press Tab. In the Actors/Actress field, type ed and press Tab twice.

For the second Video Title, type be, press Tab. In the Actors/Actress field, type he and press Tab twice.

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.

Save and close everything.

If we agree that tables are where you will spend time designing the functionality of your database, forms are where you will spend a lot of time, especially in the beginning, to design the look of your database. As a small note, keep in mind that many companies or bosses will not just pay you for being able to create a database, how it looks sometimes count tremendously.

Microsoft Access comes equipped with a lot of features to suit your tastes, but it is up to you to be aesthetically creative.

Open the Music Collection3 database.

From the main menu, click Insert -> Form.

In the New Form dialog, click Design View. In the combo box, choose the tblMusicAlbums table, and click OK. You get a raw form, a toolbox, and the table's field list.

To perform your design, a form has two rulers, one horizontal on the top of the form, one vertical on the left, to help you be more precise with dimensions.

Then you have a toolbox equipped with buttons, edit boxes and other items you can use to give a lot of flexibility to your application. Usually, you will have the toolbar whenever you are working in the Design View.

You also have the originating table, the field list, on your screen in case you want to add an item that you didn't include in the beginning. When you don't need any or both of these two items, you can hide them by clicking their buttons on the toolbar.

There are different ways you can retrieve an item from the list and place it on your form. For example, you can drag one from the list and place it on the form. You can also double-click on the title bar of the field list to select all items, then drag all of them to the list.

Double-click on the field list's title bar to select all items from the list, then click in the blue list and drag everything to the form. At first, you might get something that looks messed up, but that's why you will design and customize it.

Save the form as frmAlbums, then close it.

Now open the frmAlbums1 form in Design View.

To manipulate an item on the form, you click on it to select it. To select more that one item, you can click the first one, then you hold down the Shift key and click the other different items that you need. If all the items you want to select are on a certain portion or angle of your form, you can drag/draw an empty rectangle that covers all the items that you want to select. If the items that you want to select are aligned, you place your mouse in the ruler that corresponds to the alignment until it becomes an arrow, then you click.

The main area you work on is called the Details area. But a form can be divided in different sections or areas. You can have a Header section where you would write a title or anything you want to keep on top of your form. In the same way, you can have a Footer section to display some data, for example the date and/or time.

In Design View, a form has a small square on the intersection of both rulers in the top left area. This area (normally, it's a button) can be used to access some of the properties of a form.