How to Multiply in Access 2016 using SQL

Do you need to multiply in Access?

If you do, I’ll show you the full steps needed to multiply in Access 2016 using SQL.

In the next section, I’ll review a simple example to illustrate the multiplication process.

The Example

To start, let’s say that you’re the owner of a store that sells office supplies.

You created a table in Access with the following info:

 

Client IDClient First NameClient Last NameUnits OrderedProduct Price Per UnitProduct NameShipping Address
111BobLee5$500.00Desktop Computer21-Bay street Canada
111BobLee5$200.00Monitor21-Bay street Canada
222JamesFord2$150.00Telephone32-Rome street Italy
333NancySilva3$150.00Telephone22-Tokyo street Japan
444MariaGreen1$100.00Chair15-Rio street Brazil

 

Your goal is to track the Revenue.

To do that, you’ll need to multiply the field Units Ordered by the field Product Price Per Unit.

This is how your table would look like in Access (where the table name is ‘Tracking Sales’):

 

Table in Access 2016

 

You’ll now need to create the query in Access where you can apply the multiplication operation.

Creating the Query in Access

(1) To start, go to the Create tab

(2) Then, press on the Query Design icon

 

Query Design in Access 2016

 

(3) Under the Show Table menu, add your table (in our case, add the table ‘Tracking Sales’)

 

Show table in MS Access

 

(4) The ‘Tracking Sales’ table will appear on your screen as follows:

 

Table - Access 2016

 

(5) To add all the fields under the ‘Tracking Sales’ table, double-click on each field (one-by-one):

 

Add Fields in Access

Steps to Multiply in Access 2016 using SQL

Let’s look at the steps that you’ll need to take in order to multiply in Access:

(1) First, you’ll need to switch to the SQL View. To do that, press on the “View” icon

(2) Then, select the “SQL View” option from the drop-down menu as below:

 

SQL View - MS Access

 

(3) You will now see the SQL syntax that includes all the fields from the ‘Tracking Sales’ table (check the next step for a better view):

 

How to Multiply in Access 2016 using SQL

 

(4) Optionally, you may wish to reorganize the fields names in a manner that is easier to follow (here, we haven’t changed the content, only reorganized the SQL syntax):

 

How to Multiply in MS Access using SQL

 

(5) Now it’s time to write the SQL syntax to derive the Revenue.

As mentioned before, you can derive the Revenue by multiplying the field Units Ordered by the field Product Price Per Unit.

Here is the corresponding SQL syntax :

[Tracking Sales].[Units Ordered] * [Tracking Sales].[Product Price Per Unit] As Revenue,

Where:

  • [Tracking Sales].[Units Ordered]  represents the Units Ordered field from the ‘Tracking Sales’ table
  • [Tracking Sales].[Product Price Per Unit] represents the Product Price Per Unit field from the ‘Tracking Sales’ table 
  • In SQL, the symbol * is used to represent the multiplication operation
  • As Revenue, reflects the new field that you just created, called Revenue. This field will contain the result of your multiplication operation.

Don’t forget to put the comma symbol (,) after the As Revenue since there are additional fields followed by this field.

 

How to Multiply in Access 2016 using SQL

 

(6) Press on the Run icon (under the Design tab) to execute your query:

 

Multiply in Access using SQL

 

(7) This is the result that you’ll get. As you can see below, a new field was created, called Revenue.

Indeed this field contains the result of your multiplication operation of the Units Ordered by the Product Price Per Unit:

 

Field in Access

 

But wait a minute, what if you want to display your Revenue values using a currency format?

In the next section, I’ll show you how to accomplish that task.

Apply a Currency Format using SQL

(1) To apply a currency format to your Revenue field, you’ll need to go back to the SQL View

(2) Then, you’ll need to add the following components (highlighted in red) under the Revenue field:

format([Tracking Sales].[Units Ordered] * [Tracking Sales].[Product Price Per Unit],“currency”) As Revenue,

This additional syntax will ensure that a currency format will be applied to your Revenue field:

 

Currency format in Access 2016 - SQL

 

(3) Run your query. You’ll now see a $ sign associated with the values under the Revenue column:

 

Apply a Currency Format using SQL

 

Congratulation, you just applied the multiplication operation in Access using SQL. Do not forget to save your work…