Creating the Tables
Next will we create four tables to store information about the customers, products and rentals. The three tables will be called:
- tblCustDetails: Information about the customers
- tblDVDDetails: Information on the products which are DVDs
- tblVGDetails: Information on the products which are Video Games
- tblRentals: Information on the products which have been rented out to customers
Notice that all the tables have been prefixed with “tbl”. This is so that when we refer to “tblCustDetails” for example, we know that it is a table, as opposed to just “CustDetails” which could be a form, query or report. There are also no spaces in table names, or field names, this is to save space.
Creating tblCustDetails
1.) To start creating the tables select the Create table in Design View option () from the tables tab in the database main window (Figure 1.2).
2.) Access will now load up a new window in which you will input the fields and fields’ type of the table.
3.) In the Field Name Column enter in the name of each field as shown below, for each field select the related field type (Figure 2.1.1):
Once you have done this, set the field CustomerID as the primary key. To do this, select the field, then click on “Primary Key” from the main toolbar at the top ().
4.) Now select the DOB Field and add the following validation rule exactly as shown below:
>#01/01/1900#
This will check to make sure that there is no person born before the year 1900. Now you need to enter a validation text so that the user is notified if they do enter a date before the year 1900. With the DOB Field still selected, add the following text in the in validation text box: “You can not enter a date of birth before the year 1900”.
5.) For the TelephoneNo field, you need to enter the following input mask: 99999/000/000;
Creating tblDVDDetails
1.) To start creating the tables select the Create table in Design View option () from the tables tab in the database main window (Figure 1.2).
2.) Access will now load up a new window in which you will input the fields and fields’ type of the table.
3.) In the Field Name Column enter in the name of each field as shown below, for each field select the related field type (Figure 2.1.1):
Once you have done this, set the field CustomerID as the primary key. To do this, select the field, then click on “Primary Key” from the main toolbar at the top ().
4.) Now select the DOB Field and add the following validation rule exactly as shown below:
>#01/01/1900#
This will check to make sure that there is no person born before the year 1900. Now you need to enter a validation text so that the user is notified if they do enter a date before the year 1900. With the DOB Field still selected, add the following text in the in validation text box: “You can not enter a date of birth before the year 1900”.
5.) For the TelephoneNo field, you need to enter the following input mask: 99999/000/000;
