MS Access 2013 Query Grouping

Hi all,

I've a (probably) silly question re Access 2013 queries. I've not done Access in years and even then only at a very low level.

I've got a query that returns a Date, a Client, A Unit and a Price. Some units have many clients in; some clients are in more than one unit.


13/4/15      Client1       Unit01     £3000
13/4/15      Client 2      Unit01     £2500
13/4/15      Client1       Unit02     £1800
13/4/15      Client2       Unit02     £600
I'm trying to display the query with just the total price per unit against each as below

13/4/15     Unit01       £5500
13/4/15     Unit02       £2400

What do I need to do to the query to make it show in this format? Or do I need another intermediate query to get that information out?


Bob SampsonHead Of ITAsked:
Who is Participating?
Richard DanekeTrainerCommented:
Looks like a job for a crosstab query.  In SQL it is a transform statement.
Bob SampsonHead Of ITAuthor Commented:
I've looked at crosstabs, and whilst they appear to work, I am not sure they will give me what I need as I want to take the results of the query and run some further maths against them (basically, if I can get a measure of income against unit, I can populate further fields with the cost of the unit and get the margin against each. Is there a way you know of?
Richard DanekeTrainerCommented:
You need a group by clause.  Sample:

SELECT Clients.DtActivity, Clients.Unit, Sum(Clients.CurPrice) AS SumOfCurPrice
FROM Clients
GROUP BY Clients.DtActivity, Clients.Unit;
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As Richard said, a "GROUP BY" (or totals query - the "E" button in the query designer).   Group By on the date and sum the price and simply ignore the customer.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.