How to Multiply in Access using SQL

Do you need to multiply in Access?

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

The Example

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

You then gathered the following info:

Product NameUnits OrderedProduct Price Per Unit
Desktop Computer5800
Monitor5300
Desk2450
Chair4150

Your goal is to calculate the Revenue.

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

Let’s look at the steps to accomplish this goal in Access.

Steps to Multiply in Access using SQL

Step 1: Create the Table in Access

To start, create the table in Access.

This is how the table would look like in Access, where the table name is ‘Tracking_Revenue‘:

Table in Access 2016

Step 2: Open the Query Design

To open the Query Design in Access:

(1) First, go to the Create tab

(2) Then, press on the Query Design icon

Query Design

Step 3: Add the Table and Fields

Under the Show Table box, add your table. In our case, add the ‘Tracking_Revenue’ table:

Show table menu

Once you’re done adding the table, press ‘Close.’

Next, add all the fields under the ‘Tracking_Revenue’ table by double-clicking on each field (one-by-one):

Add Fields

You’ll then see all the added fields at the bottom of your screen:

Fields added in MS Access

Step 4: Switch to the SQL View

To switch to the SQL View:

(1) First, press on the ‘View’ icon

(2) Then, select the ‘SQL View’ option from the drop-down menu

SQL View - MS Access

You’ll now see the SQL syntax that includes all the fields from the ‘Tracking_Revenue’ table:

How to Multiply in Access 2016 using SQL

Step 5: Multiply in Access using SQL

Now it’s time to add the SQL syntax to calculate the Revenue.

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

Here is the corresponding SQL syntax that you’ll need to add to the query:

Tracking_Revenue.[Units Ordered] * Tracking_Revenue.[Product Price Per Unit] As Revenue

Where:

  • Tracking_Revenue.[Units Ordered] reflects the Units Ordered field from the ‘Tracking_Revenue’ table
  • Tracking_Revenue.[Product Price Per Unit] represents the Product Price Per Unit field
  • In SQL, the symbol * is used for multiplication operations
  • As Revenue reflects the new field to be created (called Revenue). This field will contain the result of your multiplication operation

Here is the complete query for our example:

SELECT Tracking_Revenue.ID, Tracking_Revenue.[Product Name], Tracking_Revenue.[Units Ordered], Tracking_Revenue.[Product Price Per Unit] ,
Tracking_Revenue.[Units Ordered] * Tracking_Revenue.[Product Price Per Unit] As Revenue
FROM Tracking_Revenue;

And this is how the query would look like in Access (for our example, make sure that a comma is placed before the multiplication operation):

How to Multiply in Access 2016 using SQL

Step 6: Run the Query in Access

Click on the Run icon to execute the query in Access:

Multiply in Access using SQL

You’ll then see a new Revenue field with the results of the multiplication:

Multiply 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

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

SQL View - MS Access

Then, add the following components (highlighted in yellow) to the Revenue field:

Currency format

So your full query would look like this:

SELECT Tracking_Revenue.ID, Tracking_Revenue.[Product Name], Tracking_Revenue.[Units Ordered], Tracking_Revenue.[Product Price Per Unit] ,
format(Tracking_Revenue.[Units Ordered] * Tracking_Revenue.[Product Price Per Unit],"currency") As Revenue
FROM Tracking_Revenue;

Run the query, and you’ll now see a $ sign associated with the values under the Revenue column:

Apply a Currency Format

You may also want to check the following source that explains how to sum values in Access using SQL.