Designing Your First Database: 3

Data Entry Form
If you enter the wrong department code, or say, an incorrect phone number, you will be prompted to re-enter the data. You can if you wish, type in your own message in Validation text, in field properties.

We are now going to design our second table, which will contain personnel information.

  1. Go to the Table tab and then click New then Design View, and enter the fields below:

  2. You will notice that the field Emp_Number appears again in this table. This is so that we can link up the two tables with a common field. Your table should look like the one below:

  3. Enter the data below into the table except for the Emp_Number field.

  4. Now close the table and open Staff Departments in Datasheet View. Highlight the Emp_Number field with all the data, and click the Edit menu and select Copy. Then close the table and open the Personnel table. Highlight the Emp_Number column and go to the Edit menu and select Paste. The Emp_Number column will be pasted into the table.
  5. So that all data added in this table is only accepted when it is linked to the main table, Staff Departments, we will need to link the common table Emp_Number and enforce "referential integrity". It's a bit of a mouthful, but what it basically means is that you could not for example, in the Personnel Table, add a new employee without first creating an Employee Number in the Staff Departments table. To do this we need to go to the Tools menu and select Relationships.

  6. When the above box appears click on Emp_Number in either table, and drag and drop the field into Emp_Number in the other table.
  7. When the Relationships dialogue box comes up, make sure Enforce Referential Integrity is ticked and then click the Create button.
  8. You will see now that a link has been established between the tables via Emp_Number.

  9. Print out copies of the data in both your tables.

The benefit of having data in an organised format - a database - is that information of a specific nature can be extracted from it. This is called a Query. On the following page we are going to design our own from the given criteria.

Continue