Top 10 for every type

I have a table.
A            B            C                                   
One            Paul            10
One            Paul            21
One            James            45
One            Tom            32
One            Smith            35
One            Maddox            22
One            Mark            23
One            Mathew            12
One            Thomas            13
One            Henry            2
Two            Paul            43
Two            Paul            44
Two            James            23
Two            Tom            34
Two            Smith            34
Two            Maddox            45
Two            Mark            56
Two            Mathew            34
Two            Thomas            12
Two            Henry            45

I have a query
SELECT DISTINCTROW A, B, Sum(C) AS C
FROM Table
GROUP BY A, B;

 I want the top 10 for every A .
i.e "one" should have top ten records, two, etc.


i.e A
should have
Paul            31
rishpatAsked:
Who is Participating?
 
frankyteeConnect With a Mentor Commented:
backup your db first!!!
copy this function into a standard module and execute it, replace any table names accordingly

Function fnLoop() As Boolean

    Dim sql As String, d As DAO.Database,  rA As DAO.Recordset
    Dim s As String
       
    Set d = CurrentDb
    s = "testTable" 'whatever table that gets deleted and recreated

    'delete and create a new table
    On Error Resume Next
    DoCmd.DeleteObject acTable, s

    On Error GoTo errH
     sql = "SELECT a, b, sum(c) as SumC into " & s & "  FROM Table where 1 = 0 GROUP BY a, B "
     d.Execute sql
     sql = "select distinct a from table"  'note you called your table "table"
     Set rA = d.OpenRecordset(sql)
     If rA.RecordCount = 0 Then
        Beep
        MsgBox "you have no records!", vbExclamation
        Set d = Nothing
        Exit Function
     End If
     
     rA.MoveFirst
     
     Do Until rA.EOF
        sql = "insert into " & s
        sql = sql & " SELECT top 10 a, b, sum(c) as SumC FROM Table where a ='" & rA!a _
            & "' GROUP BY a, B  ORDER by sum(c) desc "
        d.Execute sql
        rA.MoveNext
     Loop
     'your table "testTable" is now populated with top 10 for each "A"
     
comExit:
        Set d = Nothing
 Exit Function
 
errH:

Beep
MsgBox Err & " " & Err.Description
Resume comExit

End Function
0
 
frankyteeCommented:
top 10 based on what criteria, sum of c?
0
 
jindalankushCommented:
for this you need looping but in ms-access its not possible
for taking top 10 on all A. you can use this
SELECT top 10  A, B, Sum(C) AS C
FROM Table
GROUP BY A, B
for taking top 10 on particular one categry then
SELECT top 10   B, Sum(C) AS C
FROM Table
where A= "One"
GROUP BY  B
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
frankyteeCommented:
jinda is correct, you'll have to create a recordset and loop through and insert results into a table.
0
 
rishpatAuthor Commented:
ankush,
 
SELECT top 10  A, B, Sum(C) AS C
FROM Table
GROUP BY A, B

selects only the first 10 records... sorted by A
Top 10 should be based on sum(c)
0
 
rishpatAuthor Commented:
ankush,
 
SELECT top 10  A, B, Sum(C) AS C
FROM Table
GROUP BY A, B

selects only the first 10 records... sorted by A
Top 10 should be based on sum(c)
0
 
frankyteeCommented:
SELECT top 10   B, Sum(C) AS C
FROM Table
where A= "One"
GROUP BY  B
order by sum(c) desc

to get top 10 A
0
 
rishpatAuthor Commented:
that still give the top 10 for only "One", how do I get all?
0
 
frankyteeCommented:
you'll have to write a loop
0
 
rishpatAuthor Commented:
and how do you do that?
0
 
KhoiNqqCommented:
Just Union 2 Select queries

SELECT Top 10 A, B, Sum(C) AS C
FROM Table
where A= "One"
GROUP BY  B
order by sum(c) desc

UNION

SELECT A, B, Sum(C) as C
FROM Table
WHERE A="Two"
GROUP BY  B
order by sum(c) desc
0
 
KhoiNqqCommented:
Sorry, I miss something

SELECT Top 10 A, B, Sum(C) AS C
FROM Table
where A= "One"
GROUP BY  B
order by sum(c) desc

UNION

SELECT Top 10 A, B, Sum(C) as C
FROM Table
WHERE A="Two"
GROUP BY  B
order by sum(c) desc
0
 
frankyteeCommented:
i'm assuming that there will be more than two "a"s otherwise if there are say 100 a's you'll need 100 union statements
0
 
rishpatAuthor Commented:
Yes frankytee I have about 75 a's  cannot use Khoinggs solution!

0
 
frankyteeCommented:
rishpat, did you try my function?
i'm about to log off so let me know how it went
0
 
frankyteeCommented:
rishpat, have you tried it yet?
0
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0
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.