Select top 3 within many groups

[ping99]

Hi all,

I have a mytable with 2 fields:

team score
AAAA 10
AAAA 12
AAAA 14
AAAA 16
AAAA 18
AAAA 20
AAAA 22

BBBB 11
BBBB 22
BBBB 33
BBBB 44
BBBB 55
BBBB 66

CCCC 22
CCCC 77
CCCC 88
CCCC 99

DDDD 10
DDDD 12

to have in mynewtable AT LEAST the top 3 in each team if that team has more
than 3 records.

team score
AAAA 22
AAAA 20
AAAA 18


BBBB 66
BBBB 55
BBBB 44

CCCC 99
CCCC 88
CCCC 77

DDDD 12
DDDD 10

I try this SQL statement but not work.


SELECT TOP 3 TEAM,SCORE FROM MYTABLE WHERE TEAM IN ( SELECT TEAM,SCORE FROM MYTABLE ORDER BY TEAM, SCORE DESC;

Please assist to correct my sql.

TIA

 

[dhookom]

Since this is a reports forum my solution is to group/sort your report by team (group header) and then score. Add a text box to your detail section:
Name: txtRank
Control Source: =1
Running Sum: Over Group
Then add code to the On Format event of the detail section:

  Cancel = Me.txtRank >3

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ping99]

Thanks a million Duane.

You help me out a lot. It works fine and easy to understand
you are the PRO.

Ping