Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Lightbulb Unanswered: Access2013 SQL: how to get next 5 records per ID

    Hi - thanks for reading and here's to hoping you can assist!

    I am looking for a way to pull the next delivery qty & dates scheduled for an order, by item. I have a query returning 240 delivery dates that looks like this, I would like to see next 5 per order.
    For example purposes, here's enough data to return 2
    order item deliverydate delqty
    12345 box 8/1/16 20
    12345 box 8/15/16 20
    12345 box 8/31/16 20
    12345 bag 10/1/16 100
    12346 box 8/2/16 25
    12346 box 9/1/16 25
    12346 bag 7/31/16 100
    23456 box 8/1/16 50
    23456 wire 8/2/16 25

    The data I am looking for would come back like this - next dates for an item, per order
    box 12345 8/1/16 20
    box 23456 8/1/16 50 *next 2 dates for this box

    bag 12346 7/31/16 100
    bag 12345 10/1/16 100

    wire 23456 8/2/16 25

    Can anyone assist on how I would write this?

    THANKS!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So its dion to yor query design
    Define thecolumns you want
    Use.the SELECT TOP syntax
    Specify a where clause to exclude irrelevant rows
    Specify a sort order so that you get the top rows you want

    The wrinkle is how you define what the next 5 rows are. Thats your WHERE clause
    presumably its the next 5 orders from today, or possibly from now() or from tomorrow

    A first cut could be
    Code:
     select top 5 order, item, deliverydate, qty from mytable
    Having deliverydate > now () 
    Group by order, item
    Order by order, item, deliverydate
    You will then need to refine the query as required
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    My record source is a query not a table - is that a problem?
    It's relevant rows only and from all of these I want to see top 5 order, delivery date, delqty for EVERY item

    Quote Originally Posted by healdem View Post
    So its dion to yor query design
    Define thecolumns you want
    Use.the SELECT TOP syntax
    Specify a where clause to exclude irrelevant rows
    Specify a sort order so that you get the top rows you want

    The wrinkle is how you define what the next 5 rows are. Thats your WHERE clause
    presumably its the next 5 orders from today, or possibly from now() or from tomorrow

    A first cut could be
    Code:
     select top 5 order, item, deliverydate, qty from mytable
    Having deliverydate > now () 
    Group by order, item
    Order by order, item, deliverydate
    You will then need to refine the query as required

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •