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.
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 ID||Client First Name||Client Last Name||Units Ordered||Product Price Per Unit||Product Name||Shipping Address|
|111||Bob||Lee||5||$500.00||Desktop Computer||21-Bay street Canada|
|111||Bob||Lee||5||$200.00||Monitor||21-Bay street Canada|
|222||James||Ford||2||$150.00||Telephone||32-Rome street Italy|
|333||Nancy||Silva||3||$150.00||Telephone||22-Tokyo street Japan|
|444||Maria||Green||1||$100.00||Chair||15-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’):
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
(3) Under the Show Table menu, add your table (in our case, add the table ‘Tracking Sales’)
(4) The ‘Tracking Sales’ table will appear on your screen as follows:
(5) To add all the fields under the ‘Tracking Sales’ table, double-click on each field (one-by-one):
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:
(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):
(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):
(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,
- [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.
(6) Press on the Run icon (under the Design tab) to execute your query:
(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:
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:
(3) Run your query. You’ll now see a $ sign associated with the values under the Revenue column:
Congratulation, you just applied the multiplication operation in Access using SQL. Do not forget to save your work…