?
Solved

Query to get last 3 Beyers in Access

Posted on 2011-10-12
13
Medium Priority
?
288 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:JackJackson54
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 36959864
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
 

Author Comment

by:JackJackson54
ID: 36959888
That should not have been in there.  It was removed and still does not work correctly
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36959911
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:QPR
ID: 36959912
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
 

Author Comment

by:JackJackson54
ID: 36959930
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
 

Author Comment

by:JackJackson54
ID: 36959936
Thomasian:

That only returned Hattie L's data.

Jack
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36959944
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
 

Author Comment

by:JackJackson54
ID: 36959958
Thomasian:

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

Jack


0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36959963
Can you upload a sample db with test data?
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 36960556
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
 

Author Comment

by:JackJackson54
ID: 36962634
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36962743
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36969029
So what did you do, please? Let it run during the night?

/gustav
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question