TOP (N) Records in a Group

[wcrimi]

I have a table called "Running Lines" with RCTrack, RCDate, RCRace, Horse, and Date

In this table there are multiple horses that ran in each race, on each day, at each track.

This is the query I wrote to get every horse's latest race for every track/date/race in the table. It works fine.

SELECT [Running Lines].RCTrack, [Running Lines].RCDate, [Running Lines].RCRace, [Running Lines].Horse, Max([Running Lines].Date) AS MaxOfDate
FROM [Running Lines]
GROUP BY [Running Lines].RCTrack, [Running Lines].RCDate, [Running Lines].RCRace, [Running Lines].Horse;



Now I would like to get each of their latest 5 races. I found some documentation online, but each of my efforts to modify it for my needs either got syntax or other errors. I can't find good direction on this one.

Thanks

[orange]

Try http://allenbrowne.com/subquery-01.html

[wcrimi]

I've had several issues along the same line and have been referred to that link each time. I have yet to solve any of those issues using the examples in that link as a guideline. Now granted, I am a beginner, but the examples are not clear or explanatory enough to get me through the details of what I want. I need an "Access for Dummies" kind of presentation. I actually have a book like that, but it doesn't document what I need either.

[orange]

Perhaps you could tell us in simple English the issue you are trying to solve.
Keep it real simple, from start to finish and we'll see if we can guide you through.

Simple and complete.
Some sample data would be good, too. Even a copy of your database.

[wcrimi]

I have table with data on all the horse races that have occurred at several tracks in the US in 2014.

Here's an example of 1 race at 1 track on a specific date. But there will be races at any number of several tracks, with lots of dates, with several races on each date, with any number of horses in each race in the entire table.

1. This following data is telling me that the race in question occurred at AQU, on 12/27/2014, and it was race number 1.

2. The 3 horses in this race were Seattle Slew, Affirmed, and Alydar

3. Seattle Slew had 6 races prior to this race (Date tells you when they occurred)

4. Affirmed had 8 races prior to this race (Date tells you when they occurred).

5. Alydar had 8 races etc...


RCTrack...RCDate...RCRace...Horse..........Date


AQU........12/27/14.....1.....Seattle Slew...11/10/2014
AQU........12/27/14.....1.....Seattle Slew...10/15/2014
AQU........12/27/14.....1.....Seattle Slew...09/21/2014
AQU........12/27/14.....1.....Seattle Slew...09/07/2014
AQU........12/27/14.....1.....Seattle Slew...08/25/2014
AQU........12/27/14.....1.....Seattle Slew...08/01/2014
AQU........12/27/14.....1.....Affirmed........11/10/2014
AQU........12/27/14.....1.....Affirmed........10/09/2014
AQU........12/27/14.....1.....Affirmed........09/30/2014
AQU........12/27/14.....1.....Affirmed........09/01/2014
AQU........12/27/14.....1.....Affirmed........08/15/2014
AQU........12/27/14.....1.....Affirmed........07/25/2014
AQU........12/27/14.....1.....Affirmed........07/04/2014
AQU........12/27/14.....1.....Affirmed........06/22/2014
AQU........12/27/14.....1.....Alydar............08/22/2014
AQU........12/27/14.....1.....Alydar............07/22/2014
AQU........12/27/14.....1.....Alydar............06/22/2014
AQU........12/27/14.....1.....Alydar............05/21/2014
AQU........12/27/14.....1.....Alydar............04/20/2014
AQU........12/27/14.....1.....Alydar............12/22/2013
AQU........12/27/14.....1.....Alydar............12/01/2013
AQU........12/27/14.....1.....Alydar............11/15/2013



I want to display RCTrack, RCDate, RCRace, Horse, and Date but ONLY the most recent 5 for each horse. So I would drop off the oldest race from Seattle Slew on 8/1/2014, the oldest 3 from Affirmed, and the oldest 3 from Alydar. RCTRack, RCDate, RCRace, and Horse are a group.

I hope that's clearer.

In my example from a query that is working, I did a group and selected the maximum date to get the horse's most recent race. But there's nothing like that to easily select the TOP 5 within a group. It becomes a convoluted query that I can't get to work.

[orange]

from this sample from your data, you can see duplication

AQU........12/27/14.....1.....Seattle Slew...11/10/2014
AQU........12/27/14.....1.....Seattle Slew...10/15/2014 
AQU........12/27/14.....1.....Seattle Slew...09/21/2014
AQU........12/27/14.....1.....Seattle Slew...09/07/2014
AQU........12/27/14.....1.....Seattle Slew...08/25/2014
AQU........12/27/14.....1.....Seattle Slew...08/01/2014

So one of the first things I'm going to suggest is to get your tables designed to meet your requirements.

RaceTrack
Race
RaceDate
Horse
HorseWasInRace

all seem like possible tables within the scope of your example.

A Horse may compete in 0 or more Races
Races occur at RaceTracks on RaceDates

You are only interested in the fact the Horse was in a Race on a particular Date? You don't care about finish position?

Can you post a copy of your database?

[orange]

I used some of your data --most dates were rejected at my end.
So I made up some dates

ID
Horse
OldRaceDate

1
Seattle Slew
10/10/2014

2
Seattle Slew
21/09/2014

3
Seattle Slew
04/07/2014

4
Seattle Slew
19/07/2014

5
Seattle Slew
27/04/2014

6
Seattle Slew
08/11/2013

7
Affirmed
11/10/2014

8
Affirmed
10/09/2014

9
Affirmed
14/08/2014

10
Affirmed
09/07/2014

11
Affirmed
18/06/2014

12
Affirmed
13/05/2014

13
Affirmed
07/02/2014

14
Affirmed
09/01/2013

15
Alydar
11/11/2014

16
Alydar
10/10/2014

17
Alydar
09/09/2014

18
Alydar
08/08/2014

19
Alydar
07/07/2014

20
Alydar
06/06/2013

21
Alydar
12/11/2013

22
Alydar
23/10/2013

and created a query

SELECT horse, oldRaceDate
FROM horseinfo
WHERE oldracedate 
in (select top 5 XX.oldracedate from
   horseinfo as XX
    WHERE XX.horse = horseinfo.horse
    
    Order By XX.oldracedate desc)
GROUP BY horse, oldracedate
ORDER BY horse DESC , oldracedate DESC;

That gives this result

horse
oldRaceDate

Seattle Slew
10/10/2014

Seattle Slew
21/09/2014

Seattle Slew
19/07/2014

Seattle Slew
04/07/2014

Seattle Slew
27/04/2014

Alydar
11/11/2014

Alydar
10/10/2014

Alydar
09/09/2014

Alydar
08/08/2014

Alydar
07/07/2014

Affirmed
11/10/2014

Affirmed
10/09/2014

Affirmed
14/08/2014

Affirmed
09/07/2014

Affirmed
18/06/2014

[wcrimi]

from this sample from your data, you can see duplication

AQU........12/27/14.....1.....Seattle Slew...11/10/2014
AQU........12/27/14.....1.....Seattle Slew...10/15/2014 
AQU........12/27/14.....1.....Seattle Slew...09/21/2014
AQU........12/27/14.....1.....Seattle Slew...09/07/2014
AQU........12/27/14.....1.....Seattle Slew...08/25/2014
AQU........12/27/14.....1.....Seattle Slew...08/01/2014

So one of the first things I'm going to suggest is to get your tables designed to meet your requirements.

RaceTrack
Race
RaceDate
Horse
HorseWasInRace

all seem like possible tables within the scope of your example.

A Horse may compete in 0 or more Races
Races occur at RaceTracks on RaceDates

You are only interested in the fact the Horse was in a Race on a particular Date? You don't care about finish position?

Can you post a copy of your database?

The table actually has dozens of fields about each race that each horse has run in. Those others are more or less irrelevant for this discussion because if I can get the logic to select those last 5 records correctly, I can always select get to the rest of those fields.

[wcrimi]

I used some of your data --most dates were rejected at my end.
So I made up some dates

ID
Horse
OldRaceDate

1
Seattle Slew
10/10/2014

2
Seattle Slew
21/09/2014

3
Seattle Slew
04/07/2014

4
Seattle Slew
19/07/2014

5
Seattle Slew
27/04/2014

6
Seattle Slew
08/11/2013

7
Affirmed
11/10/2014

8
Affirmed
10/09/2014

9
Affirmed
14/08/2014

10
Affirmed
09/07/2014

11
Affirmed
18/06/2014

12
Affirmed
13/05/2014

13
Affirmed
07/02/2014

14
Affirmed
09/01/2013

15
Alydar
11/11/2014

16
Alydar
10/10/2014

17
Alydar
09/09/2014

18
Alydar
08/08/2014

19
Alydar
07/07/2014

20
Alydar
06/06/2013

21
Alydar
12/11/2013

22
Alydar
23/10/2013

and created a query

SELECT horse, oldRaceDate
FROM horseinfo
WHERE oldracedate 
in (select top 5 XX.oldracedate from
   horseinfo as XX
    WHERE XX.horse = horseinfo.horse
    
    Order By XX.oldracedate desc)
GROUP BY horse, oldracedate
ORDER BY horse DESC , oldracedate DESC;

That gives this result

horse
oldRaceDate

Seattle Slew
10/10/2014

Seattle Slew
21/09/2014

Seattle Slew
19/07/2014

Seattle Slew
04/07/2014

Seattle Slew
27/04/2014

Alydar
11/11/2014

Alydar
10/10/2014

Alydar
09/09/2014

Alydar
08/08/2014

Alydar
07/07/2014

Affirmed
11/10/2014

Affirmed
10/09/2014

Affirmed
14/08/2014

Affirmed
09/07/2014

Affirmed
18/06/2014

I'm not sure why my dates would be rejected. They are all valid dates in MM/DD/CCYY format. Oh well, that's not significant because they were made up dates anyway.

It appears you are very close to what I want. I just RCTrack, RCDate, and RCRace as part of this output. Here's why.

For example, Seattle Slew's 6 prior races are listed for the 12/27/2014 race date at AQU.

On 11/10/2014, his 5 races prior to that were listed under AQU...11/10/2014....race 1 and so on. For each race date, all a horse's prior races are listed.

I realize that's may seem like a lot of duplication, but this is the way the data is produced by an outside vendor. I import the data in exactly the same format that is used to create the race card for customers. It makes it easy to recreate the past performances for a race.

[orange]

Well you don't always receive data in the format you need/desire/want. Many people have spent careers receiving Name and address info and have to reformat data for their own use. If you design your tables, and queries I'm sure you can get all the info you need. If you are working from a "report" produced by other software, that already has various line formatting, duplication/redundant data for ease of reading.

Good luck.

[wcrimi]

Well you don't always receive data in the format you need/desire/want. Many people have spent careers receiving Name and address info and have to reformat data for their own use. If you design your tables, and queries I'm sure you can get all the info you need. If you are working from a "report" produced by other software, that already has various line formatting, duplication/redundant data for ease of reading.

Good luck.

Thanks for your help.

I'll have some time later today to experiment with the code you provided. Hopefully I'll have a "eureka" moment.

[wcrimi]

Well you don't always receive data in the format you need/desire/want. Many people have spent careers receiving Name and address info and have to reformat data for their own use. If you design your tables, and queries I'm sure you can get all the info you need. If you are working from a "report" produced by other software, that already has various line formatting, duplication/redundant data for ease of reading.

Good luck.

I tried to add the rest of what I needed to the code you provided, but had no Luck.

The query seemed to be running, but then it just hung the software and died. I think I'm going to have to file this query away along with several others as either not feasible with my level of knowledge or not feasible at all. I'm a beginner with Access, but I have about 25 years of data processing experience using mainframe languages and a decade of Excel for personal use. I'm beginning to think Access was a terrible choice for this project. It seems incredibly clumsy for all but the most basic information queries relative to the things I've worked with previously. I could probably solve a few of the problems I've run into using Access in half the time by exporting the data to Excel and manually creating the data I want (which of course is preposterous).

This is the code I used:

SELECT [Running Lines].RCTrack, [Running Lines].RCDate, [Running Lines].RCRace, [Running Lines].Horse, [Running Lines].Date

FROM [Running Lines]

WHERE [Running Lines].Date

in (Select Top 5 XX.Date from
[Running Lines] as XX

Where XX.Horse = [Running Lines].Horse
Order By XX.Date Desc)

GROUP BY [Running Lines].RCTrack, [Running Lines].RCDate, [Running Lines].RCRace, [Running Lines].Horse, [Running Lines].Date

ORDER BY [Running Lines].RCTrack Desc, [Running Lines].RCDate Desc, [Running Lines].RCRace Desc, [Running Lines].Horse Desc, [Running Lines].Date ;

Unless there is something obvious, thanks for trying.

[wcrimi]

I see that some people have attached their database to their questions, but my database is already quite large and has about a dozen tables that are in no way related to the issue I am having.

In the sample table data I posted above with horses and dates, those are the exact field names and format of the data I need to manipulate. The only difference is that there are many RCTrack, RCDate, RCRace etc... with race information about horses than need to be selected.

I don't even see where the "attach" option is.

[wcrimi]

This is it

[orange]

I have added a query (Last5DatesForHorse) to your database.

SELECT [running lines].horse, [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].[Date]
FROM [running lines]
WHERE ((([running lines].[date]) In
 (
 select top 5 XX.[Date]  from
   [running lines] as XX
     WHERE XX.horse  = [running lines].horse 
     Order By XX.[Date] desc     ))
 )
GROUP BY [running lines].horse, [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].[Date]
ORDER BY [running lines].horse, [running lines].[Date] DESC;