Max 3 rocords

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi ;

I wrote a simple query to bring the maximum value of a field .
Now i want to modify this to bring me maximum 3 values in that field .
Can someone explain how can this be done ?

Steve
Thank in advance
 
Don't know how others here (experts) would handle this, but I would query
the query another two levels.
So if for instance you want the 2nd most recent date you purchased item 99,
in your second query
in the date field set the total row to "max" and after adding query1 to the
query window enter in the criteria row: <[query1].[max of date] . (assumes
that you accepted the default name "max of date"
in your first query).
Repeat for as many levels as you want. Create a final query that calls
values from each of the previous queries.
Hope that helps.

Ed G
 
Steve said:
I wrote a simple query to bring the maximum value of a field .
Now i want to modify this to bring me maximum 3 values in that field .
Click to expand...


See KB article 210039
 
Hi Marsh ;

Thanks , that was very useful.
However , the result of this query returns the top three for each category .
Actually what i would like to have is the top 3 records among all categories
..
Would you mind modifying below query as to return top 3 records
of all the records .

Thanks
Steve

SELECT Categories.CategoryName, Products.ProductName, Products.UnitsInStock
FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID
WHERE (((Products.UnitsInStock) In (Select Top 3 [UnitsInStock] from
Products Where [CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock]
Desc)));
 
You mean you only want 3 records total? If so:

SELECT TOP 3 UnitsInStock
FROM Products
ORDER BY UnitsInStock

But that's too easy, so you probably meant something else
;-)
--
Marsh
MVP [MS Access]


Thanks , that was very useful.
However , the result of this query returns the top three for each category .
Actually what i would like to have is the top 3 records among all categories
.
Would you mind modifying below query as to return top 3 records
of all the records .

SELECT Categories.CategoryName, Products.ProductName, Products.UnitsInStock
FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID
WHERE (((Products.UnitsInStock) In (Select Top 3 [UnitsInStock] from
Products Where [CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock]
Desc)));

Marshall Barton said:
See KB article 210039
Click to expand...
Click to expand...
 
Back
Top