MS Access: How to group by first column- summing some columns while keeping others the same

Question

I have Data that looks like:

ID  |   Year    |   State   |   Cost
----+-----------+-----------+-----------
1   |   2012    |   CA      |   10
2   |   2009    |   FL      |   90  
3   |   2005    |   MA      |   50
2   |   2009    |   FL      |   75
1   |   2012    |   CA      |   110

I need it to look like:

ID  |   Year    |   State   |   Cost
----+-----------+-----------+-----------
1   |   2012    |   CA      |   120
2   |   2009    |   FL      |   165  
3   |   2005    |   MA      |   50

So I need the year to remain the same, the state to remain the same, but the cost to be summed for each ID.

I know how to do the summing, but I don't know how to make the year and state stay the same.

Answer 1

You use a GROUP BY or TOTALS Query. Something like,

SELECT 
    ID, 
    [Year], 
    State, 
    Sum(Cost) As TotalCost
FROM 
    yourTable
GROUP BY
    ID,
    [Year],
    State;

A Group By clause GROUPS the records based on the common information. The Sum adds up the column specified to give you the right information.