Calculating on Records
In previous lessons, we have used the Records of a Table. However, there will be times in which we will be interested in getting information, not from the records in a table, but from only a group of them. For example, we might want to see the total "Products" we have sold but grouped by our "Clients". To achieve this, we will create another Table. But this time, we will do it differently. Just for now, we will do it in the regular way.
Let's see; we want to know the total of "Products" we have sold, grouped by our "Clients". So, it is evident we need both the "Products" and "Clients" tables.
And in the grid, we add the fields we will use. From the "Clients" table, we will use the "First Name" and "Last Name" fields. From the "Products" table, we will use the "In Stock" field.
Why the "In Stock" field if we want to know the total amount acquired by a Client? Wouldn't it be more logical to add a new field called "Units Sold"? Well, one of the first rules we learned about Data Base Relationships in the first lessons is NEVER duplicate information, and Access allows us to use its full power to our benefit.
Let's see this in detail: We already have a field which shows the product in stock. We can perform a Query to know the total amount in stock of a Product we have. But, what would happen if we relate this field with the data of another Table? Let's say, the "Clients" table? What result would we get? Well, we would get the total products purchased by that Client.
If we wanted to do this in a temporary plane, we wouldn't need to create a new field. We just need to relate the products In Stock with a Date field in the proper Table.
The same "In Stock" field from the Products Table can grant us diverse information if we relate it to the Client Table ("Product Sol"), or with the Table Supplier Table ("Product Purchased"); all of this just by having introduced ONCE the data in the "In Stock" field of the "Products" Table
As you can see, it is very important the way we name our fields. This will help us understand the data type it contains as well as understanding how it can affect them after being related to other Tables. Let's add the fields "In Stock" from the "Products" table and "Client's First Name" and "Client's Last Name" form the "Clients" table.
However, BE CAREFUL. Both fields belong to different Tables and, in the upper portion of the Design View, you can see that there is no common field between both Tables. There is no Relationship between them.
If we want to relate 2 different Tables, we need to have some sort of reference between them, In this case, there is none. However, we can have one if we add the "Orders" Table to the Query. We can simply add this table using the "Show Table" icon and selecting the "Orders" table and clicking on "Add" and then "Close".
