How can I find a specified number of records in a SELECT query?

[Guest]

I have implemented a Video database which has tables for 'Video', 'Rental',
'Member' etc. I wish to run a query to select the top twenty video rentals in
the last week. The period of time is not an issue; I have used (>Date()-7).
The problem is returning only the top twenty rentals; can I use COUNT to do
this?

 

[Brian]

I have implemented a Video database which has tables for 'Video', 'Rental',
'Member' etc. I wish to run a query to select the top twenty video rentals in
the last week. The period of time is not an issue; I have used (>Date()-7).
The problem is returning only the top twenty rentals; can I use COUNT to do
this?

Not quite. You (obviously) need to use Count to get the results sorted in
descending order of number rented, but then you can use the TOP keyword to
get just the top 20. Something like (using Northwind):

SELECT TOP 20 Orders.CustomerID, Count(Orders.OrderID) AS CountOfOrderID
FROM Orders
GROUP BY Orders.CustomerID
ORDER BY Count(Orders.OrderID) DESC;

 

[PC Datasheet]

You should have tables that look like:
TblVideo
VideoID
VideoName
<other fields you need.

TblVideoRental
VideoRentalID
VideoID
DateRented
CustomerID
,other fields you need.

Create a query that joins both tables on VideoID. Include the fields
VideoName, VideoID and DateRented in the query. Put your criteria for
DateRented in the criteria of DateRented. Click on the Sigma (looks loke E)
button in the toolbar at the top of the screen. Under VideoID, change Group
By to Count. Set the sort order for that field to Descending. Click anywhere
in the open area of the query window where your tables are. A dialog will
appear; click on Properties. Change the Top Values property to 20. When you
run the query you will get a list of the top 20 videos rented in the past
week where the most rented video will be at the top of the list and the
least rented (out of the 20) will be at the bottom of the list.