I'm trying to build a query that returns only the five most expensive items. 
The field name for price is selling price. 

I have looked at the sample database Northwind that comes with Access. I see 
they have a query for the ten most expensive products but cannot find where 
they developed the query field tenmostexpensiveproducts:productname. When I 
cut and paste into my database and change productname to selling price it 
does not work.

Can you help? 

select top 5
  [selling price]
from
  [you table name here]

Cheers,
Jason Lepack


On Jul 31, 9:08 am, John <J...@discussions.microsoft.com> wrote:
> I'm trying to build a query that returns only the five most expensive items.
> The field name for price is selling price.
>
> I have looked at the sample database Northwind that comes with Access. I see
> they have a query for the ten most expensive products but cannot find where
> they developed the query field tenmostexpensiveproducts:productname. When I
> cut and paste into my database and change productname to selling price it
> does not work.
>
> Can you help?

You forgot the order by clause.

SELECT Top 5 [Selling Price]
FROM [Your Table]
ORDER BY [Selling Price] DESC

If you want to get more information you may need to use the above query as a 
subquery in a Where clause.

SELECT  <<Your Field List>>
FROM [YourTable] INNER JOIN [SomeOtherTable]
ON [YourTable].FK = [SomeOtherTable].PK
WHERE [Selling Price] IN
   (SELECT Top 5 [Selling Price]
      FROM [Your Table]
      ORDER BY [Selling Price] DESC)

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Jason Lepack" <jlepack@gmail.com> wrote in message 
news:1185887622.251166.268110@z28g2000prd.googlegroups.com...
> select top 5
>  [selling price]
> from
>  [you table name here]
>
> Cheers,
> Jason Lepack
>
>
> On Jul 31, 9:08 am, John <J...@discussions.microsoft.com> wrote:
>> I'm trying to build a query that returns only the five most expensive 
>> items.
>> The field name for price is selling price.
>>
>> I have looked at the sample database Northwind that comes with Access. I 
>> see
>> they have a query for the ten most expensive products but cannot find 
>> where
>> they developed the query field tenmostexpensiveproducts:productname. When 
>> I
>> cut and paste into my database and change productname to selling price it
>> does not work.
>>
>> Can you help?
>
> 

SELECT Top 5 [Selling Price]
FROM yourTable
ORDER BY [Selling Price] DESC

If you want the top 5 distinctly different selling prices, you would need to 
use:

SELECT Top 5 [Selling Price]
FROM (SELECT DISTINCT [Selling Price] FROM yourTable) as Temp
ORDER BY [Selling Price] DESC

HTH
Dale
-- 
Email address is not valid.
Please reply to newsgroup only.


"John" wrote:

> I'm trying to build a query that returns only the five most expensive items. 
> The field name for price is selling price. 
> 
> I have looked at the sample database Northwind that comes with Access. I see 
> they have a query for the ten most expensive products but cannot find where 
> they developed the query field tenmostexpensiveproducts:productname. When I 
> cut and paste into my database and change productname to selling price it 
> does not work.
> 
> Can you help?