Top 3 results

[Clarencephil]

This may be a simple problem, but I cannot find the solution myself.

I need to design a query based on a table describing all sales during a year to a number of clients of my company.
However , even if 50 sales may have been made during the year to some clients, I want the query to return only the top 3 sales for each client.

Any suggestions
Thanks

 

[KeithG]

First go into the query properties and set max records to 3, then sort descending.

 

[Clarencephil]

First go into the query properties and set max records to 3, then sort descending.

Thanks for the advice.
I'm not sure we're talking about the same thing (or at least, it does not seem to be working).
Suppose I have a table with the following companies and sales:

Company/.. Sales /.. Date
Comp.A $15 000 27 April 05
Comp.B $ 15 000 .....
Comp.A $ 40 000 ......
Comp.A $ 32 000 .....
Comp.C $ 22 000
Comp.B $ 25 000
Comp.A $ 50 000
Comp.A $10 000
Comp.B $ 6 000

And I want the top 2 sales results for each Company, the query should return this:

Comp.A $ 50 000
Comp.A $ 32 000
Comp.B $ 25 000
Comp.B $ 15 000
Comp.C $ 22 000

I don't get this with your suggestion . any ideas ?

 

[Jon K]

Take a look at this MS Article
ACC2000: How to Create a "Top N Values Per Group" Query
http://support.microsoft.com/kb/210039/en-us


Note:
In Method 2, the recordset used in the NthInGroup() function is a DAO recordset.
.

 

[Clarencephil]

Take a look at this MS Article
ACC2000: How to Create a "Top N Values Per Group" Query
http://support.microsoft.com/kb/210039/en-us


Note:
In Method 2, the recordset used in the NthInGroup() function is a DAO recordset.
.

Method 1: I tried it with my database: did not work
Strangely enough, I tried it by curiosity with the Northwind datase : did not work either (try it).

There must be a better or simpler way

 

[Jon K]

I tried. Both methods worked. I don't think you can find a better or simpler way.

You have to tweak the subquery in Method 1 a little to make it work on a single table, though.

See the two queries in the attached database, which contains your sample data. Both queries should return these results:-

ID	Company		Sales		Date
7	Comp.A		$50,000.00	
3	Comp.A		$40,000.00	
6	Comp.B		$25,000.00	
2	Comp.B		$15,000.00	
5	Comp.C		$22,000.00

.

 

[Clarencephil]

I tried. Both methods worked. I don't think you can find a better or simpler way.

You have to tweak the subquery in Method 1 a little to make it work on a single table, though.

See the two queries in the attached database, which contains your sample data. Both queries should return these results:-

ID	Company		Sales		Date
7	Comp.A		$50,000.00	
3	Comp.A		$40,000.00	
6	Comp.B		$25,000.00	
2	Comp.B		$15,000.00	
5	Comp.C		$22,000.00

.

OK Sorry, I tried Method 1 again and got it to work.
I had forgotten to remove the _ in the subquery (from the supplied example) .
I had not tried Method No.2.

Thanks a lot. You went through a lot of effort. Well appreciated.