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

Discussion in 'Microsoft Access Queries' started by Guest, Apr 9, 2005.

  1. Guest

    Guest 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?
     
    Guest, Apr 9, 2005
    #1
    1. Advertisements

  2. Guest

    Brian Guest

    "Mecci" <> wrote in message
    news:...
    > 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;
     
    Brian, Apr 9, 2005
    #2
    1. Advertisements

  3. Guest

    PC Datasheet Guest

    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

    www.pcdatasheet.com


    "Mecci" <> wrote in message
    news:...
    > 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?
    >
     
    PC Datasheet, Apr 9, 2005
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Angela

    How to find records closest to a specified date

    Angela, May 18, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    209
    Guest
    May 18, 2004
  2. Rebekah
    Replies:
    7
    Views:
    442
    Tom Ellison
    Sep 15, 2004
  3. Sue

    Query To Find Records As Of Specified Date

    Sue, Oct 29, 2004, in forum: Microsoft Access Queries
    Replies:
    13
    Views:
    255
  4. Replies:
    2
    Views:
    382
    Guest
    Jul 21, 2005
  5. Ian

    Select two records then select the next two records

    Ian, Feb 24, 2010, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    227
    Stefan Hoffmann
    Mar 2, 2010
Loading...

Share This Page