Calculating Totals in Access

jsawicki
jsawicki used Ask the Experts™
on
I have a spreadsheet that lists names in one column and a number in the second column.  In the name's column, some names are listed multiple times.  What i am trying to do in my query is list the name once and then do a sum for all numbers in the second column associated with that name.  I thought i could use the Distinct function, but it continues to list the names multiple times.  There are other columns in the spreadsheet and the data is different for each similar name so unsure if that is not allowing me to combine them even though i am not pulling those fields into the query.  

I have attached a sample database with mock info.  Below is what i am trying to obtain.
Test1 - 2
Test2 - 4
Test3 - 6
etc

I know it is simple, but i am just stuck.
example.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this:

SELECT DISTINCT Sheet1.Name, Sum(Sheet1.Number) AS SumOfNumber
FROM Sheet1
GROUP BY Sheet1.Name;
AshokSr. Software Engineer

Commented:
try

select Name, Sum(aNum)
from myTable
Group by Name

HTH
Ashok
Top Expert 2016
Commented:
SELECT Sheet1.Name, Sum(Sheet1.Number) AS SumOfNumber
FROM Sheet1
GROUP BY Sheet1.Name;
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

To add a comment on your question regarding,
" There are other columns in the spreadsheet and the data is different for each similar name so unsure if that is not allowing me to combine them even though i am not pulling those fields into the query. " ...

Yes, the other columns create a unique row, therefore they will not be 'grouped' together.

Hope that helps.

Author

Commented:
I feel stupid based on the simplicity so i figured i ask one more dumb question.  Some names are in lower case and some upper case.  What code do i need to add to combine these naming conventions since they are populating on different lines because of the capitilization?
You can also use PivotTable view of your table
Not sure why an identical solution posted after my suggestion received more points, but that's your decision.
Appreciate the points you did assign.

Author

Commented:
DoveTrails:  Capricorn1 has helped out a lot, plus the DISTINCT function was not required for these totals so I rewarded more for having cleaner code.  You made a good point though which i will take note in the future.  Sorry.....i prob should have made it equal.
jsawicki .... No worries, I didn't mean to come across cold. Your point on the 'distinct' makes sense.  Appreciate the response.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial