Help With MS Access TOP Querie

[Netopia]

Greetings All,

I'm a neophyte with Access (and databases in general) but have been tasked with some work to do. One of the things I'm required to do is to come up with the top X values of a field for each of the unique values in another field.

For instance, let's assume the table below has all of a company's salesmen in one column, and their average daily sales for each of the last three months.

Name	           Daily Sales
Bob		   30
Bob		   50
Bob		   28
John	           60
John	           72
John               85
Paul	           15
Paul               08
Paul	           12

But what is wanted from a query is the TOP 2 values from Daily Sales for each Salesman. So the result should look something like this:

Name	           Daily Sales
Bob		   50
Bob		   30
John               85
John	           72
Paul	           15
Paul	           12

This is a much simplified example of what I need to do, but I think it gives a good picture.

I've tried making nested queries, but can't seem to get it right. The closest I've gotten returns an error saying that I might return more than one record (WHICH IS WHAT I WANT!).

Because I'm using Access, some of the SQL I've seen suggested out on the web doesn't work right.

Any help, short of telling me to get rid of Access (not an option, since this is for work) is greatly appreciated!

Joe

 

[KLin]

http://support.microsoft.com/kb/210039

See if that works for you.

 

[Netopia]

KLin,

I appologize for taking so long to get back with you.

Thanks for that link. Although it didn't work in 2007 (the examples they give for you to work through actually require example db's from 2000), it did give me the direction I need to go in. It also made me realize that I needed to simply bite the bullet and buy a couple of books on Access programming, queries, vba, and SQL.

Thanks again,

Joe