Further Links Contact Us Search Help

Syllabus | Exams | Assessment | Resources | Site reviews | Careers

Information Processes and Technology

Home > Information Processes and Technology > Information Systems and Databases > Enrolment database - practical task

Adding another table and defining a relationship

1. Open the Database yr 6 into 7.mdb and Click on the tables button on the left.
   
   
2. Click on the yr6 into 7 table. Click the design view icon.  Add a new field called ID Code replacing Autonumber.
   
   

   Data type	Field Size	Description	Description
   text	6	First three letters of surname + 3 digit number	E.g. ARC001

   
   
   
   Close the design view and save the changes to the table.
   
   
3. Now put in ID codes for all of the people in your yr 6 into 7 table. Make sure they follow the pattern above.
   
   
   
   
4. Create a second table called “bus” according to the following data dictionary.
   
   

   Field Name	Type	Field Size	Description
   ID Code	Text	6	From yr 6 into 7 table.
   Route Nunmber	Number	3	e.g. 501, 473

   
   
5. Enter the following information.
   
   
   
   These are the same ID codes from your yr 6 into 7 table. You can simply copy the whole column from one table and paste it into the new table.
   
   
6. Go to Tools/Relationships. Create a relationship between yr 6 into 7 and bus as follows:
   The screen at right will be showing. Click Add to add Bus Route then click “Yr 6 into 7” and click Add and then click “close”
   
   
   
   
   
   Your screen will now look like this.
   
   
   
   
7. Now click on ID Code in the Bus Route table and drag it onto IDCODE in the Yr 6 into 7 table. You will see this new screen.
   
   
   
   
8. Click Create and a line will show the linking of the two tables.
   
   
   
   
9. Now close the relationships window. You will be asked if you want to save. Say Yes.
   
   The next thing you will learn is how to create a query which links the two tables, so you can see information from both tables at the same time.
   
   We are going to create a new query that will combine the information from both of the tables in your database. We will show information from the yr 6 into 7 table and from the Bus Route table
   
   
10. Open your database and Click on Queries on the left-hand side. Double click create query in design view.
    
    
    
    The window below will show.
    
    
    
    
11. Click Bus Route and click Add, now click Yr 6 into 7 and click Add, click Close. Your new screen will look like this:
    
    
    
    
12. In the first column, choose yr 6 into 7.surname and have it sorted in ascending order.
    
    
    
    
13. In the second column, choose choose yr 6 into 7.firstname.
    
    
14. In the third column, choose choose yr 6 into 7.phonehome.
    
    
15. In the fourth colum, choose yr 6 into 7.bus and type “YES”
    
    
16. In the fourth column, choose Bus Route.Bus Route.
    
    
17. All of these columns will have ticks in them.
    
    Completed Query design
    
    
    
    
18. Close the window (click the X). When asked if you want to save the query, say yes call it Bus numbers.
    
    
19. Double click your Bus Numbers query, it will show the lastname, firstname, phone number of all the people in your yr 6 into 7 table who catch a bus and the route number.
    
    
    

Adding a Photo to the Database

Now we are going to add a new field to the bus table and create a form and add some pictures to the form.

1. Click on tables on the left, click on yr 6 into 7 and click the design view icon.
   In design view, add a new field called photo. Make its data type OLE object.
   
   
   
   Close and save the table.
   
   
2. Open the Table Bus Route. The easiest way to get a photo into this field is to paste a bitmap picture directly into the field from MS Paint. i.e. Open a Picture and copy it to the Clipboard and simply paste it into the picture field as shown below.
   
   
   
   
3. Click on the Query button on the left-hand side. Click on Bus Route and Click the design icon.
   
   
   
   
4. Add Picture to the Bus Numbers query. 
   
   
   
   
5. Click on Forms on the left hand side and Double click create form by using wizard. This is what your new screen will look like. Make sure you choose the Bus Numbers query table. Now click the double right arrow  to move all of the fields into the right hand box.
   
   
   
   
6. Click next three times and then click Finish to complete making your form. This is what your form will look like. Hopefully there will be the picture that you chose showing in the box.