Perform calculations in a “Query”

One of the features of a “Query” is to calculate a value.
In this example, I have 3 tables, the “Customers” table , “Orders” table and the “Products” table.
In the “Customers” table are my customers, quite logical.
In the “Orders” table, I have placed all orders.
And in the “Products” table, I have all the products with their price.
The purpose of this example is to calculate the total value of each order.

If any tables are open, close them all.
Click the “Query Design” button in the “Create” tab.
In the “Show Table” dialog, add these three tables to the “Query”.

The “Relationships” between the tables is automatically placed by “Access”.
Add the “Name” from the “Customers” table, “orderID “and “quantity” from the “Orders” table and the “price” field from the “Products” table, to the grid.
When you have done all this, this is what you will have.

First, we save the “Query”. Click on the “Save” button in the “Quick Access” toolbar.
Enter an appropriate name for your “Query”, such as “TotalOrders” and click OK.

To see the total amount of an order, I must multiply the quantity by the price of the article.

Right-click above the empty field next to the “Price” column and select “Build” from the drop-down menu.

This opens the “Expression Builder” dialog box .
1. Double-click “Quantity” in the center pane of the dialog.
2. Click on ‘*’ in the toolbar of the dialog.
3. Double-click “Price” in the center pane of the dialog.
4. Finally click the OK button.

This formula appears in the expression.
If you know how, you can also directly type it in this box.
Please make sure you type them correctly, otherwise it will not work.

The formula is displayed in the field.
Alternatively you can enter some text followed by a colon and then enter the formula.
This will be displayed as a column title in the new column that you just created.

Click the “Run” button in the “Ribbon”.

If the data in the new column “Total” does not appear as currency, which is more than likely the case, then return to the “Design View”
Select the newly created column.
Click the “Property Sheet” button in the “Ribbon”.
And in the panel, choose “currency” for the “Format”.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats