How do I query only the top five selling prices?

[John]

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 tenmostexpensiveproductsroductname. When I
cut and paste into my database and change productname to selling price it
does not work.

Can you help?

 

[Jason Lepack]

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

Cheers,
Jason Lepack

 

[John Spencer]

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
..

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

Cheers,
Jason Lepack

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 tenmostexpensiveproductsroductname. When
I
cut and paste into my database and change productname to selling price it
does not work.

Can you help?

 

[Dale Fye]

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