Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: Selecting Last three records

    I have a database for horse racing results and have records for unique horse names. They have one entry per week. As a relative newbie to Access, I am stumped by how to select the last three records (last three dates) from my database each horses name , and what happens if they don't have three entries?

    Any help is appreciated.

    Mar

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    either selct based on a date criteria
    ie where racedate > dateadd("w", -3,date()) to get the results in the previous 3 weeks
    of racedate > dateadd("w", -3,<adate value in ISO date yyy-mm-dd or #nn/dd#yyyy>) to get the results in the previous 3 weeks from a specified date

    or use the "top 3" predicate to get the last 3 results
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I think the problem is that the last 3 rows for each horse is wanted. If it was just the last 3 rows, a simple "Select top 3 ... Order by date-col DESC" would work.

    To get the last (up to 3) rows for each unique horse, will probably require programming.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT horse
         , racedate
      FROM results AS T
     WHERE ( SELECT COUNT(*) 
               FROM results  
              WHERE horse = T.horse
                AND racedate > T.racedate ) < 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you group by the horse date add should work if you want lots of horses. if you wan the specific horse the where clause should handle that

    in my books its not clear from the OP whether they want the last 3 results, or the results in the last 3 weeks.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2009
    Posts
    5

    Clarification of request for last 3

    I am looking for the last three records for each horse regardless of the date.

    Thanks,

    Marianne

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ganarask
    I am looking for the last three records for each horse regardless of the date.
    did you try the query in post #4 yet?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2009
    Posts
    5

    Only single horses

    This query requested a horses name when run, so only one horse data.

    Just tried it.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ganarask
    This query requested a horses name when run
    yours might've, mine didn't

    try it again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2009
    Posts
    5

    Now I get all horses, but all starts not just three

    Now I get all horses, but all starts not just last three

    Here's what I wrote: Must be something wrong....

    Thanks,


    SELECT [Horse Name]
    , [Date of Race]
    FROM [Results Last Two Years Query] AS T
    WHERE ( SELECT COUNT(*)
    FROM [Results Last Two Years Query]
    WHERE [Horse Name] = T.Horse
    AND [Date of Race] > T.racedate ) < 3

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tsk tsk, you got your own column names wrong
    Code:
    SELECT [Horse Name]
         , [Date of Race]
      FROM [Results Last Two Years Query] AS T
     WHERE ( SELECT COUNT(*) 
               FROM [Results Last Two Years Query]
              WHERE [Horse Name] = T.[Horse Name]
                AND [Date of Race] > T.[Date of Race] ) < 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2009
    Posts
    5

    Thanks, thanks!

    Thank you so much for your help. It worked like a charm!!!

    I am slowly learning more about how to do things and helpful people are a great treasure in that process.

    Thanks again,

    Marianne

Posting Permissions

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