You can use the calculated data type to perform calculations using fields from different tables by joining these fields in a relation. You can also perform calculations with values from fields in the same table (see Using the calculated data type in a table). The following example should make it clear to you how to perform calculations in a relation.
Example
Imagine you have two tables: one called Orders, which records the product and quantity for each order, and one called Products, which records the price of each product and gives a description of the product. Each order and product is given an ID and both tables have the Product ID field in common. It is your aim to calculate the total value for each of the orders.
Orders
Order_ID |
Product_ID |
Quantity |
Value |
ORD1 |
A003 |
5 |
|
ORD2 |
A001 |
7 |
|
ORD3 |
A003 |
4 |
|
ORD4 |
A002 |
3 |
|
ORD5 |
A002 |
2 |
|
ORD6 |
A004 |
6 |
|
Products
Product_ID |
Description |
Cost |
A001 |
File |
2.99 |
A002 |
Lamp |
25.50 |
A003 |
Pen |
1.20 |
A004 |
Fan |
18.99 |
The total value for each order can be calculated from multiplying the values from Quantity (in Orders) by the values from Cost (in Products) for each Product ID. Since Quantity and Cost are in different tables, you need to join the tables to create a relation. Before doing this you also need to add to one of the tables a field that will contain the total value. In this case I have added the field to Orders and called it Value. This field will be given the Calculated data type and formatted to return the result of multiplying the values in the Quantity and Cost fields.
Create a table, Orders, with the four fields: Order_ID, Product_ID, Quantity, and Value. Order_ID and Product_ID should be given the Character data type; Quantity the Numeric data type; and Value the Calculated data type (make sure Value is either Number or Currency). See Creating fields in a database.
Use the "Format Field" dialog to format the fields as follows (most of the formats are OK as they stand):
Make Order_ID a Primary Key (this will prevent duplicates)
Make Product_ID a non-unique index i.e. Indexed (this will make sort and search operations more efficient when you have many records)
Reduce Decimal Places in Quantity to 0 (all quantities will be whole numbers)
Increase Decimal Places in Value to 2 (to display cents or pennies properly)
Type the formula =Quantity * Cost into the Formula line on the Formula page for Value (see Calculated and Using the calculated data type in a table for an example of how to enter a function)
For more information on formatting, see Formatting database fields
Next, create a table, Products, with the three fields: Product_ID, Description, and Cost. Product_ID and Description should be given the Character data type, and Cost the Numeric data type.
Use the "Format Field" dialog to format the Product_ID field as a Primary Key (to prevent duplicates).
Next, create a new relation based on these two tables (see Creating a relation). The Join Field in both tables should be Product_ID and the Join Type should be Inner (so that only the products which are listed in both tables will be displayed)
The Relation table will look like this:
Orders. Order_ID |
Orders. Product_ID |
Orders. Quantity |
Orders. Value |
Products. Product_ID |
Products. Description |
Products. Cost |
ORD2 |
A001 |
7 |
20.93 |
A001 |
File |
2.99 |
ORD4 |
A002 |
3 |
76.50 |
A002 |
Lamp |
25.50 |
ORD5 |
A002 |
2 |
51.00 |
A002 |
Lamp |
25.50 |
ORD1 |
A003 |
5 |
6.00 |
A003 |
Pen |
1.20 |
ORD3 |
A003 |
4 |
4.80 |
A003 |
Pen |
1.20 |
ORD6 |
A004 |
6 |
113.94 |
A004 |
Fan |
18.99 |
You would undoubtedly want to sort the first column into ascending order and hide either the second or fifth column. Note that you can rearrange and hide columns, sort and filter records, just as in any other table. See Query components for more information.