is there a sort and sum query? (1 Viewer)

[ariansman]

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

 

[Gasman]

Yes, look at a Totals query grouped on Staff

 

[ariansman]

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?

 

[Gasman]

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.

 

[June7]

Google is your friend. https://www.tutorialspoint.com/ms_access/ms_access_grouping_data.htm

 

[ariansman]

Google is your friend. https://www.tutorialspoint.com/ms_access/ms_access_grouping_data.htm

thank you man but it did not address what i was looking for. i need to group the "staff" and then add all the related number for each staff in one record for each staff.

 

[ariansman]

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.

 

[Gasman]

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?

 

[Gasman]

Now crossposting https://www.accessforums.net/showthread.php?t=84528 so that is me out of this thread.

 

[ariansman]

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;

 

[ariansman]

Now crossposting https://www.accessforums.net/showthread.php?t=84528 so that is me out of this thread.

 

[isladogs]

Also crossposted at UtterAccess.com. Good way to get ignored in future.

 

[ariansman]

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

 

[cheekybuddha]

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.

 

[isladogs]

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.

Agreed. However the same point has been made to the OP on more than one forum in the past...and he still ignored it!

 

[cheekybuddha]

...and he still ignored it!

I see.

I did think you all were being slightly over-aggressive about a cross-post

 

[isladogs]

I think some members got quite cross by the crossposts!

 

[cheekybuddha]

I like what you did there!

 

[June7]

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.

 

[Gasman]

@June7
OP has already been given solutions in both AF.net and UA, as well as here