# 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            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            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
###### Who is Participating?

Commented:
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

Commented:
top 10 based on what criteria, sum of c?
0

Commented:
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

Commented:
jinda is correct, you'll have to create a recordset and loop through and insert results into a table.
0

Author 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

Author 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

Commented:
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

Author Commented:
that still give the top 10 for only "One", how do I get all?
0

Commented:
you'll have to write a loop
0

Author Commented:
and how do you do that?
0

Commented:
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

Commented:
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

Commented:
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

Author Commented:
Yes frankytee I have about 75 a's  cannot use Khoinggs solution!

0

Commented:
rishpat, did you try my function?
i'm about to log off so let me know how it went
0

Commented:
rishpat, have you tried it yet?
0

Commented:
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.