jsawicki
asked on
Calculating Totals in Access
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
" 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.
ASKER
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.
Appreciate the points you did assign.
ASKER
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.
select Name, Sum(aNum)
from myTable
Group by Name
HTH
Ashok