Creating the Table
We will create the tables and work on them in the "Design View"
We will create the "Client" Table first. We will introduce the fields we designed in the previous lesson. On the description column, we will type in a short description to help us define the function of each field.
You might think it strange for the last 3 fields to be defined as a Data Type of "Text" when we could have defined them more logically as "Number". The reason is quite simple; we will only define them as "Number" when we are planning to perform mathematical operations on them.
Evidently, we won't be adding up "Telephones", nor subtracting "ZIP codes" and of course, we won't be multiplying "Account numbers". Thus, we will define these fields as "Text" type.
We will asign the "Autonumber" type to the "Client's Code" field to make sure we don't gat a repeated Client. Access will automatically assign a value for each new Record we create.
In the "Orders" Table, different than that of the "Clients" table, we find several "Number" fields. We will perform some operations on them. For example, we will be multiplying the "Quantity" field by the "Unit Price" field to calculate the total price of the Order. However, there are other fields, such as the "Client's Code" and "Product Code" which won't be used for mathematical operations.
Why do we define them as "Number" type? The answer is simple. These fields will be used to relate the Order with the "Client" and "Product" tables. THE ONLY WAY FOR US TO RELATE 2 TABLES IS FOR THESE 2 TABLES TO CONTAIN A COMMON FIELD THEY CAN USE AS REFERENCE. If in the "Clients" table we have the "Client's Code" field and its Data Type is "Autonumber", in the "Orders" table we also need to have a "Client's Code" field, and it will need to be defined as Number.
But, are the types Number and Autonumber the same? Yes. An Autonumber type is a Number field to which Access automatically assigns a unique and sequential value. This makes sure there won't be any duplicate Records which might ruin our Data Base.
When we are done creating our Table, we need to save it. We can do so by clicking on the "save" icon in the Quick Access Toolbar.
The first time we save a Table, we will be promted to define a Primary Key. The window will tell us this is not necessary but advisable. In our case, since we will be working with related Tables, it is essential for us to answer "yes" to this dialog because each Table will be used as a reference by the rest through this primary keyl.
The last Table we will define will be the "Products" Table.
All the elements on this Table have been studied and won't pose any problem. We have defined the "Product Code" field as the primary key. We will notice the small key icon next to the field.