Information Technology in a Global Society

Databases: Part 4

Relating Tables

1. Now that you have your table and form created for registration you need to add the fields at the bottom concerning the participant's interests.  First you need to change the table design to hold these fields.  You could do that by changing the participant table like this:
    

   militaryRecruitment	yes/no
   curriculumInnovation	yes/no
   socialJustice	yes/no
   nclb	yes/no
   renaissance2010	yes/no
   CPSissues	yes/no

   but this may not be the best way to do this.  Why?  Well, it makes it hard to modify the design of the form and database.  Let's say that next year there are a completely different list of issues.  The designer of the database would have to go into the table and change it and then change the forms and reports to fit the new set of issues.  This could be very time consuming.  Instead, we are going to make a more dynamic design.
    

2. Whenever you see a list of things inside your table you really should create other tables to handle the list.  For instance, for a participant, there is a list of issues (military recruitment, curriculum innovation, etc...).  Let's say you had a student table.  You might have fields like class1, class2, class3, etc..   It would be better if those entries where stored in a separate table.  But how do you do that?  I will show you.
    
3. Open Microsoft Access and open the database you were working with in the last class.
    
4. Click on the table tab.  Create a new table (in design view) that will have only one field.  Call that field interest as char(60).  Make sure to make that field the primary key and then save and close the table.  Call the table areaOfInterest. 
    
5. Double-click on the table areaOfInterest so that you can enter data in that table.  Enter the values:
   Military Recruitment
   Curriculum Innovation
   Social Justice
   No Child Left Behind
   Renaissance 2010
   CPS Citywide Issues
   
   and close the table.
    
6. Now create a table called interest with the following fields:
    

   attendee	number
   interest	char(60)

   Highlight both fields and make them the primary key.
    

7. Now that you have your two new tables you have to create a relationship between them. You can do that by selecting Relationships... from the Tools menu.
    
8. Make sure to Add all three tables to the relationship window, then close the dialog box.  You should see this:
   
    
9. Make the relationships between the tables. Click on registrationNumber in attendee and drag to attendee in interest. Make sure the appropriate fields are linked and you have enforced referential integrity. 
    
10. Click on the interest in areaOfInterest and drag to interest in interest table.  Make sure the appropriate fields are linked and you have enforced referential integrity.  Your window should now look like this:
    
     
11. Now you have your tables set up to add information to your registration form.  But now you need a form for the interest table.  Using the Create Form Wizard, create a form for the interest table (make sure it is in Tabular style). You are going to have to do something special with this form so don't close it yet.
     
12. I want you to delete the text box interest from this form (the one in the detail section).
     
13. Now,  select combo box from your tool menu and click and drag it into place where the interest text box was.  A dialog box will open.
     
14. You will be using the areaOfInterest table to populate this combo box.  So hit next and then select the areaOfInterest table.
     
15. Hit next and select the only field available, interest.  Hit next.  Sorting is optional, then hit next again, and next again until you get to the step that asks "Store that value in this field".
     
16. You want to store the value in the field interest then go to finish.  When you go to View--Form View your form should look something like this (when selecting the dropdown):
    
     
17. Now you are ready to add this form as a child from in the attendee form.  Close all forms and then open the form you created in the last class (attendee or person or whatever you called it).  We are going to add this form to it so go to the design view.
     
18. In the tool box select Subform/Subreport.  Click and drag it into onto the form where you want to see it.  Make sure not try to install the wizard.
     
19. Right-click on the new form, select properties, and then click on the data tab.  You want to select the interest from so that your properties look like this:
    
     
20. Now you can select any number of interests for any of the attendees.  There is no limit and the list of interests can be changed at any time by adding or deleting records from the AreaOfInterest table.  Fix up your form so that it looks good, print it out and turn it in at the end of class.