• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

SQL QUERY | Sum Values by Group in Access 2010

June 13, 2013 by Ryan Leave a Comment

I have been working with Access a lot lately. In my last project I was working with a table that contained 1.4 million records with count values by US States. I needed run a graph based on the sum of values for each state.

My original table looked like this:

Table: States  
State Value
MN 54
MN 62
IL 76
MN 34
CA 48
MN 62
IL 4

And so on… All of the way through 1.4 million records. For my graph data, I needed to turn the above table into:

State Sum of State
MN 212
IL 80
CA 48

I am a big fan of using the SQL design view in Access as I find it to be more powerful. If you are more comfortable with the Access Query builder GUI, you can swap back after writing your query.

SUM Values by Group in Access 2010 | SQL Query

To sum values by state, here is how I structured my query:

SELECT State AS State, SUM(Value) AS 'SUM of State'
FROM states
GROUP BY State
ORDER BY State

Walkthrough

I SELECTed the column ‘State’ and set label as State, I summed the column ‘Value’ and labeled AS ‘SUM of State’
FROM ‘states’ table
GROUP BY ‘State’ column – this part is key. This is how you group your summed data by state.
ORDER BY ‘State’ column – this part is optional as you can sort any way you choose.

As always comment with questions or suggestions on how to improve.

Filed Under: Microsoft Access Tagged With: MS SQL, Sum Values by Group in Access 2010

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  1. KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  2. jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  3. Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  4. mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  5. Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Copyright © 2025 · Magazine Pro on Genesis Framework · WordPress · Log in