Qry that returns top N records in each group

[vettetax]

Crystal Reports has a built-in query function to return the top "N" records in groups of data, not just the top "N" records in the table. Am I not able to replicate such a function using queries in Access? For example:

Group Customer Revenue
A 1 10
A 2 9
A 3 8
A 4 7
A 5 6
A 6 5
A 7 4
B 1 20
B 2 19
B 3 18
C 1 30
C 2 27
C 3 26
C 4 22
C 5 19
C 6 5


I need to query the top 3 records in each group so that if gives me the top 3 highest Revenue values in each Group?

Group Customer Revenue
A 1 10
A 2 9
A 3 8
B 1 20
B 2 19
B 3 18
C 1 30
C 2 27
C 3 26

Thanks for your assistance.

Dan

 

[SydneyGeek]

Hi Dan, check out this thread

Denis