Query to get last 3 Beyers in Access

I have the following SQL that does not work:

SELECT top 3  Trial.Rdate, Trial.Trk, Trial.Rc, Trial.Horse, Trial.Beyer
FROM Trial
WHERE (((Trial.[Beyer])>0))
ORDER BY  * Trial.date DESC;

I have a table that is named Trial  and below is  part of this table that has thousands of records.

Trk        Rdate        Rc         Horse         Beyer         Date
ALB      9/3/2010      5      Calsilver      19      8/15/2010
ALB      9/3/2010      5      Calsilver      40      10/10/2009
ALB      9/3/2010      5      Calsilver      32      9/4/2009
ALB      9/3/2010      5      Calsilver      32      8/15/2009
ALB      9/3/2010      5      Calsilver      47      7/23/2009
ALB      9/3/2010      5      Mongo        18        8/14/2010
ALB      9/3/2010      5      Mongo        50        11/17/2009
ALB      9/3/2010      5      Mongo        18        10/25/2009
ALB      9/3/2010      5      Mongo        68        10/2/2009
ALB      9/3/2010      5      Mongo        59        9/15/2009
ALB      9/3/2010      5      Mongo        53        8/28/2009
ALB      9/3/2010      5      Mongo        58        7/18/2009
ALB      9/3/2010      5      Hattie L      44      8/15/2010


The result I am wanting is to return the last 3 Beyers based on the date.   So, Calsilver would return 19, 40, and 32.  Mongo would return 18, 50 and 18.  There will be some horses that has only 1 past record such as Hattile L, which would return 44.  There might be a horse that has 2 records which would return those 2 Beyers.  There are some horses that might have 100 past records, but I only need the last 3 Beyers.
JackJackson54Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Here is how:

SELECT
  tblTrial.Rdate,
  tblTrial.Trk,
  tblTrial.Rc,
  tblTrial.Horse,
  tblTrial.Beyer
FROM
  tblTrial
WHERE
  ((tblTrial.Date In
    (Select Top 3
      T.Date
    From
      tblTrial As T
    Where
      T.Trk=tblTrial.Trk
      And
      T.Rdate = tblTrial.Rdate
      And
      T.Rc = tblTrial.Rc
      And
      T.Horse = tblTrial.Horse
    Order By
      [Date] Desc)))
ORDER BY
  tblTrial.Rdate,
  tblTrial.Trk,
  tblTrial.Rc,
  tblTrial.Horse,
  tblTrial.Date DESC;

Output:

03-09-2010      ALB      5      Calsilver      19
03-09-2010      ALB      5      Calsilver      40
03-09-2010      ALB      5      Calsilver      32
03-09-2010      ALB      5      Hattie L      44
03-09-2010      ALB      5      Mongo      18
03-09-2010      ALB      5      Mongo      50
03-09-2010      ALB      5      Mongo      18

/gustav
0
 
QPRCommented:
ORDER BY  * Trial.date DESC;

I'll admit it's been a while since I did queries in Access (as opposed to SQL Server) but are you sure this is correct?

ORDER BY one or more columns but not by *
Try removing the *
0
 
JackJackson54Author Commented:
That should not have been in there.  It was removed and still does not work correctly
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ThomasianCommented:
SELECT T.Rdate, T.Trk, T.Rc, T.Horse, T.Beyer
FROM Trial As T
WHERE T.[Beyer]>0
      AND 3>(SELECT COUNT(1) FROM Trial WHERE [Beyer]>0 AND T.Horse=Horse AND T.date<[date])

Open in new window

0
 
QPRCommented:
you haven't specified what date, your criteria only specifies beyer > 0
Your code will always return the 3 most recent dates.
You also need to s[pecify which horse you are interested in
0
 
JackJackson54Author Commented:
QPR,

I need the query to return the last 3 Beyers for all horses in the table.  There are thousands of horses and this particular tables has 680,012 rows of past data.  

I am interested in getting all horses last 3 Beyers if they had at least 3 races.   If the horse had 1 past race, it would need to return that Beyer and if a horse had 2 past races, it should return those 2 Beyers.

Jack
0
 
JackJackson54Author Commented:
Thomasian:

That only returned Hattie L's data.

Jack
0
 
ThomasianCommented:
Try
SELECT T.Rdate, T.Trk, T.Rc, T.Horse, T.Beyer
FROM Trial As T
WHERE T.[Beyer]>0
      AND 3>(SELECT COUNT(1) FROM Trial As T2 WHERE T2.[Beyer]>0 AND T.Horse=T2.Horse AND T.date<T2.date)

Open in new window

0
 
JackJackson54Author Commented:
Thomasian:

I tried that and it still only returned Hattie L;s data.

Jack


0
 
ThomasianCommented:
Can you upload a sample db with test data?
0
 
JackJackson54Author Commented:
cactus_data:

It did run on the sample of data I posted, but when I ran the query on the 680k db, it ran for 3 hours and did nothing, so I stopped it.  

Jack
0
 
Gustav BrockCIOCommented:
Patience, Jack, patience.

It may be faster to write a function that runs through the records and appends up to three records for each horse to another table. But do use DAO for this, not ADO, neither SQL.

/gustav
0
 
Gustav BrockCIOCommented:
So what did you do, please? Let it run during the night?

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.