How to Sum Values in Access using SQL

Need to sum values in Access?

If so, I’ll show you the steps to sum values in Access using SQL.

Steps to Sum Values in Access using SQL

Step 1: Create a Table

To start, create a table in Access.

For example, I created a table (called ‘supplies‘) in Access:

Table in MS Access

The ultimate goal is to get the sum of all the values under the ‘Cost‘ column.

Step 2: Write the SQL Query

In order to write the SQL query, you’ll need to go to the Create tab, and then click on the Query Design:

Query Design

Next, press on the Add button to add the supplies table (and then click on Close):

Show table in MS Access

You’ll now see the following table:

How to Sum Values in Access using SQL

Switch to the SQL View in order to type your SQL query.

To do that, click on the View icon, and then select the SQL View option from the drop-down menu:

SQL View in MS Access

In the SQL View, you’ll see the following:

SQL Select - MS Access

You’ll then need to add this syntax, right after the SELECT statement:

sum(supplies.cost) as Total_Cost

So the full SQL query would look like this in Access:

How to Sum Values in Access using SQL

Here is a brief explanation about the syntax used in the highlighted frame:

  • sum is the operation to be performed
  • supplies represents the table name
  • cost represents the field name
  • Total_Cost represents the new field name that will be created. It will contain the sum of all values under the Cost field

Finally, click on the Run icon to run the query:

Sum Values in Access using SQL

You would then get the sum of 2150:

But what if you’d like to represent this total value in a currency format?

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

Step 3: Change the Format

To display your total cost 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(supplies.cost),"currency") as Total_Cost
FROM supplies

This is how the SQL query would look like in Access:

Sum Values in Access using SQL

Run the query, and you’ll get the total cost with a currency format (with a ‘$’ sign):

Totals in MS Access

Using Group By to Sum Values in Access

Suppose that you bought additional supplies, so your new table would look like this:

Table MS Access

Let’s say that your goal is to get the total cost by product. You can accomplish this task using group by.

The complete SQL query would then look like this:

SELECT supplies.[Product], format(sum(supplies.cost),"currency") as Total_Cost
FROM supplies
group by supplies.[Product]

Here is how the code would look like in Access:

How to Sum Values in Access using SQL

As you can see, the Product field was added in two locations:

  • The SELECT statement, in order to display that field
  • The GROUP BY statement, in order to group the items by the Product

Run the query, and you’ll get the total cost per product:

Query results in MS Access

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