How to Sum Values in Access using SQL

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.

 

MS Access - example of a table

 

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:

 

Query Design

 

(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:

 

Show table in MS Access

 

(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:

 

Query1 in MS Access

 

(5) Now, select the “Revenue” field by double-clicking on the field’s name “Revenue”

 

MS Access Field

 

(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:

 

SQL View in MS Access

 

(7) In the SQL View you’ll see the basic SQL syntax to populate the Revenue field (from the Revenue table):

 

SQL Select - MS Access

 

(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

FROM 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:

How to Sum Values in Access using SQL

 

Once you modified your SQL syntax, it is time to run your query by pressing the Run button:

 

How to Sum Values in Access using SQL

 

(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:

 

Total Sum in MS Access

 

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

FROM 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:

 

Run a query in Access

 

Now you will see the total revenue displayed with a ‘$’ sign next to it:

 

Totals in MS Access

 

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

FROM 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:

 

Group by Access

 

Here are the results of displaying your revenue by the product name:

 

Query results in MS Access

 

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…