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 the 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.

Close all the open tables.
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” from the “Products” table, to the grid.

When you have done this, then go to the next one.

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

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

Right-click (Control+Click on a Mac) above the empty field next to the “Price” column and select “Build” from the drop-down menu.

In the dialog that appears, double-click the “Quantity” (1) field.
You type the multiplication sign on your keyboard (*). (2)
Double-click the “Price” (3) field.
And you click the OK button (4).This formula appears in the expression. You can directly type 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 this formula after entering your text and a colon. This will be displayed as a column title in the new column you just created. A column header with the value “expr1” does not say much over a column header with the value “Total”.

Click the “Run” button in the “Ribbon” to generate a column with totals.

Featured Courses

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