PC Review


Reply
Thread Tools Rate Thread

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

 
 
=?Utf-8?B?TWVjY2k=?=
Guest
Posts: n/a
 
      9th Apr 2005
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?

 
Reply With Quote
 
 
 
 
Brian
Guest
Posts: n/a
 
      9th Apr 2005

"Mecci" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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;




 
Reply With Quote
 
 
 
 
PC Datasheet
Guest
Posts: n/a
 
      9th Apr 2005
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(E-Mail Removed)
www.pcdatasheet.com


"Mecci" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select two records then select the next two records Ian Microsoft Access Queries 3 2nd Mar 2010 11:27 AM
number of records in form do not match number of records in querie Graciela Microsoft Access Database Table Design 13 5th Jun 2009 11:37 PM
Repeat labels a Specified Number of Times for a specified record =?Utf-8?B?ZmFzY2Fs?= Microsoft Access Reports 1 1st May 2006 08:44 PM
Automatically generating a specified number of new rows (records) =?Utf-8?B?TGVsZQ==?= Microsoft Excel Programming 6 1st Apr 2006 11:59 PM
Number of Records pulled by Query don't match number of records in table Rebekah Microsoft Access Queries 7 15th Sep 2004 08:08 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:23 AM.