Creating Tables

For the examples we are going to create a database to be used by a video shop to track video rentals.  Open up Access and create a new database.  Create a new table in design view called tblVideo.

Put the following fields on this table:

Create 2 more tables in the same database:

 

TblCustomers

Fields:        Cust_id, Cust_first, Cust_surname, Cust_title, Cust_house, Cust_house, Cust_street, Cust_town, Cust_postcode, Cust_tel Date of Birth, Date of joining

TblRentals

Fields:        Rental_no, Vid_no, Cust_id, Rental_date

These tables need to be connected using the related fields on each table.

Open the relationship screen and connect your tables:

(written by Phil James)

When you have constructed your tables, set your primary keys, and made sure your foreign keys have matching data types. You need to tell Access how each table relates to another. To do this click on Tools, then select Relationships from the drop-down list. You will then be presented with a blank window containing this dialog box:

Here you can select which tables or queries that you want to establish relationships with. In our case, we will add all of them, so select each one and click on add (or double click on the item you want). Next close the box to reveal the tables underneath. You will now a blank screen in front of you with the following boxes (you may want to resize them if you cannot see all of their contents):

Each box represents a table with the attributes/fields displayed within. To establish a relational link between a primary key in one table and a foreign key (of the same data type) in another, simply drag the field from the primary key to the foreign key. You will notice that as you do so that your curser becomes a little rectangular box. Having done this, the next thing you will be presented with is:

In this example, I have dragged the Cust_ID primary key onto the Cust_ID foreign key. This has brought up a dialog box that requires you to set a few options before the relationship is established. At the very least you must click on 'Enforce Referential Integrity' in order to establish a One-to-Many relationship.

When you have done this click on Create and you will have the following screen:

You can now see the 'join' between Cust_ID and Cust_ID, and the relational indicator (the represent many). Now just do the same process for any other relationships (in this case the project code in the project table to project code in the assignment table).

 

Activity 10

Enter at least ten records for each table.