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.
Leave a Reply