Need to sum values in Access? If so, In this post, I’ll show you how to sum values in Access using SQL.
To illustrate how to sum values in Access, we will review a simple example, where:
- You are the business owner of a store that sells office supplies; and
- You track your products, and the associated sales of those products, in a table that you created in Access
Your table should look as below. In our example, the name of the table is Revenue.
Notice how each product sold has a Revenue entry associated with that product.
But what if you’d like to sum all the individual revenues items in order to derive the total revenue for your business?
Let’s see how you can accomplish this goal.
Steps to sum values in Access using SQL
Now, let’s review the steps you’ll need to perform in Access in order to sum your revenue values:
(1) First, go to the Create tab
(2) Then, press the Query Design icon as below:
(3) You’ll now see the Show Table menu, where you’ll be able to add your Revenue table.
Simply press the Add button to add your Revenue table:
(4) This is how your screen would look like. Notice that the list of all the fields from the Revenue table can now be selected.
You can select each field that you desire by double-clicking on the name of that field:
(5) Now, select the “Revenue” field by double-clicking on the field’s name “Revenue”
(6) Now you’ll need to switch to the SQL View in order to type your SQL syntax to sum all the revenue items.
To do that, press the View button, and then select the SQL View option from the drop-down menu:
(7) In the SQL View you’ll see the basic SQL syntax to populate the Revenue field (from the Revenue table):
(8) To sum all the individual revenue items, you’ll need to modify the SQL syntax to the following:
SELECT sum(Revenue.Revenue) AS Revenue
I highlighted in green the additional portions that you’ll need to add in order to apply the sum operation.
Notice that the word Revenue appears in 4 different places in the SQL code:
Once you modified your SQL syntax, it is time to run your query by pressing the Run button:
(9) You’ll will now see the total revenue of 4350. This is the total sum of all the individual revenue items in your Revenue table:
But what if you’d like to represent this total revenue in a currency format?
In the next section, I’ll show you how to do that.
Change to a Currency format
To display your total revenue in a currency format, you’ll need to return back to the SQL View.
Then modify the SQL syntax to the following:
SELECT format(sum(Revenue.Revenue),”currency”) AS Revenue
In the query above, I highlighted in green the additional components that you’ll need to add in order to display your total revenue in a currency format.
Once you modified your query, press the Run button:
Now you will see the total revenue displayed with a ‘$’ sign next to it:
In the next section, I will show you how to display your revenue by the Product Name
Display revenue by product name
To display your revenue by the product name, you’ll need to modify your query to the following:
SELECT Revenue.[Product Name], format(sum(Revenue.Revenue),”currency”) AS Revenue
GROUP BY Revenue.[Product Name]
As before, I highlighted the additional components in green that you’ll need to add.
In short, the field Product Name was added under both:
• The SELECT statement, in order to display that field
• The GROUP BY statement, in order to group the revenue items by the Product Name
Once you completed modifying your query, press the Run button to run your query:
Here are the results of displaying your revenue by the product name:
We have seen how you can sum values in Access using SQL. You can definitely perform other arithmetic operations in Access, such as multiplying values…