Group by : Sub totals

[SeanITFC]

Hi all,

I have a Video rentals table which lists all transactions. Using a 6 month querry & group by video number querry I have achieved trhe following list:

Video number.....Title........Cost(£)

6.....................Bond..........2
6.....................Bond..........2
7.....................Powers.......1

How do I get the totals for each video number, So I can compile a top 10 video ? ....eg

Video number........Title.........Cost(£)

6.......................Bond...........4
7.......................Powers........1


Is there a refinement to the 'Group by' function or do I need to import to VB for a 'If previous video number is the same ,n=n+t' type of program?

Many thanks

SeanITFC

 

[Liv Manto]

Create a crosstab query

Video number........Title.........Cost(£)
Groupby...............Groupby....SUM