is there a sort and sum query?

ariansman

Registered User.
Local time
Today, 06:06
Joined
Apr 3, 2012
Messages
157
We have the following table:

ID​
Staff​
ItemName​
Price​
Qnt​
Cost​
1​
John​
Book​
5​
3​
15​
2​
Mori​
pencil​
6​
7​
42​
3​
Tom​
Book​
7​
4​
28​
4​
Tom​
paper​
10​
6​
60​
5​
Mori​
paper​
10​
3​
30​
6​
John​
pencil​
6​
2​
12​
7​
John​
Book​
5​
1​
5​
8​
Mori​
paper​
10​
8​
80​
9​
Mori​
pencil​
6​
4​
24​
10​
Mori​
ink​
12​
3​
36​


Can I get the following result in a query?

Staff
Cost​
John
32​
Mori
212​
Tom
88​




Thank you
 
Yes, look at a Totals query grouped on Staff
 
Yes, look at a Totals query grouped on Staff
Thank you. Sorry, I am not an expert and couldn't figure it. Can you please instruct me with more details? How should I do that? Is there a code in SQL I should put?
 
Use the Query Design window to build the query. the totals button is there.

Then you can inspect the SQL to see what is created. That is how I started out, and TBH I would still do it this way, due to my lack of experience. :)
 
Use the Query Design window to build the query. the totals button is there.

Then you can inspect the SQL to see what is created. That is how I started out, and TBH I would still do it this way, due to my lack of experience. :)
i did it. it groups the staff but shows all the record for everybody and does not sum the cost. i should have missed something.
 
You group solely by Staff and Sum on Cost

Post your sql code withing code tags if you still cannot get it to work, but it is only two fields?
 
You group solely by Staff and Sum on Cost

Post your sql code withing code tags if you still cannot get it to work, but it is only two fields?
SELECT staffcost.ID, staffcost.sataffname, staffcost.itemname, staffcost.price, staffcost.Qnt, Sum(staffcost.[Qnt]*[price]) AS cost
FROM staffcost
GROUP BY staffcost.ID, staffcost.sataffname, staffcost.itemname, staffcost.price, staffcost.Qnt;
 
Also crossposted at UtterAccess.com. Good way to get ignored in future.
I hope you understand a desperate buddy. The crossdiscussion helped me look at it in a different way and find the solution. Not to condone it. will only post in one location next time. your site, your rule.
Thank you very much
 
will only post in one location next time. your site, your rule.
No need to limit yourself to just one place - just take the courtesy to mention in each place that you have posted the same question in the other and add a link. (y)
 
No need to limit yourself to just one place - just take the courtesy to mention in each place that you have posted the same question in the other and add a link. (y)
Agreed. However the same point has been made to the OP on more than one forum in the past...and he still ignored it!
 
I think some members got quite cross by the crossposts! ;)
 
i did it. it groups the staff but shows all the record for everybody and does not sum the cost. i should have missed something.
Can't include every field when aggregating data in query.
SELECT Staff, Sum(Cost) AS SumCost FROM table GROUP BY Staff ORDER BY Staff;

If you want to show all data then build a report and use its Sorting & Grouping features with aggregate calcs in textbox.

This is all basic Access functionality. Suggest you spend a solid week studying an introductory Access tutorial book.
 
@June7
OP has already been given solutions in both AF.net and UA, as well as here :(
 

Users who are viewing this thread

Back
Top Bottom