Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

grouping values in a group by query

Posted on 2015-02-02
3
Medium Priority
?
183 Views
Last Modified: 2015-02-05
I have a table and am doing a "totals" query in query design, using 2 fields "number" and "cost", I want to group on number, and sum the cost.

However, rather than getting an itemised list of sums per each unique number, I want to group all numbers (but exclude one number from the count, perhaps this could be put in the where clause) and get a sum for all numbers in one total figure, and do a total sum for it. Any pointers if this can be done?
0
Comment
Question by:pma111
3 Comments
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 668 total points
ID: 40583730
You can do the first grouping and summing in a normal query, then use this as source for a form you view in datasheetview. Here you can set a bottom line to show the second level of grouping, a Total on all rows of the query.

/gustav
0
 
LVL 43

Accepted Solution

by:
pcelba earned 668 total points
ID: 40583737
I would recommend to switch the query designer into SQL view and everything is in your hands then :-)

All numbers summed except one (you don't need GROUP BY):

SELECT SUM(cost) AS costSUM
  FROM YourTable
WHERE number <> 'Excluded number'

The WHERE clause expression must follow the number column data type rules so if the column is numeric then simply write:

WHERE number <> 555

The result grouped by number:

SELECT number, SUM(cost) AS costSUM
  FROM YourTable
WHERE number <> 'Excluded number'
GROUP BY number

And now you may display above queries in the Query designer.
0
 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 664 total points
ID: 40584056
You can't have two levels of grouping in the same query if that is what you are asking for.  To do that, you would need separate queries to group at each level.  Then a union query to bring it all together.  However, in most cases, it simply doesn't make sense to do this.  Do one level of grouping in the query and use a report to do the second level.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question