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:
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:
Next, press on the Add button to add the supplies table (and then click on Close):
You’ll now see the following table:
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:
In the SQL View, you’ll see the following:
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:
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:
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:
Run the query, and you’ll get the total cost with a currency format (with a ‘$’ sign):
Using Group By to Sum Values in Access
Suppose that you bought additional supplies, so your new table would look like this:
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:
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:
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.