Bringing back the 20 most recent entries for each group by

[Sean]

I have a database detailing the performance of golfers (in
their respective tournaments) over the last few years.

I have written a query to group the data of each
individual golfer ....(SELECT GolferName, Date, Position
FROM Results ORDER BY Golfername, Date)....however, this
query brings back every performance by the golfer and I
only want to see the 20 most recent performances by each
golfer.

Any ideas on how to do this ?

 

[Pavel Romashkin]

Change this to

SELECT TOP 20 GolferName ... WHERE GolferName = 'John' ORDER BY theDate

and it should work as long as it is ordered by the Date first and is
limited to a single golfer.
Pavel

 

[Michael Wong]

You may try like the following (haven't tested myself), although slower than
in a report:

SELECT R.GolferName, R.[Date], R.Position FROM Results AS R
WHERE R.Date IN
(SELECT TOP 20 S.[Date] FROM Results AS S
WHERE S.GolferName = R.GolferName ORDER BY S.[Date] DESC)
ORDER BY R.Golfername, R.[Date]

Note that [Date] is bracketed because it's an Access function.

 

[Gary Walter]

I have a database detailing the performance of golfers (in
their respective tournaments) over the last few years.

I have written a query to group the data of each
individual golfer ....(SELECT GolferName, Date, Position
FROM Results ORDER BY Golfername, Date)....however, this
query brings back every performance by the golfer and I
only want to see the 20 most recent performances by each
golfer.

Hi Sean,

Do you have a single primary key field for
your table "Result"?

If so (say primary key field = "Result.pk"),
then I believe Michael was close to what
you want:

SELECT
R.GolferName,
R.[Date],
R.Position
FROM Results AS R
WHERE R.pk IN
(SELECT TOP 20 S.pk
FROM Results AS S
WHERE S.GolferName = R.GolferName
ORDER BY S.[Date] DESC)
ORDER BY R.Golfername, R.[Date]

Michael also alludes to an alternative
(I believe).

Would you have a problem with each
golfer getting their own page in a report?

Then just design a main report to give
you golfer names (plus maybe other details
about golfer?).

In subreport, get your results sorted by [Date]
Desc. Set the subreport to not grow nor shrink,
i.e., give it height so each page gets only one golfer.

Let master/child relationship on GolferName give
you as many results as will fit on one page for each
individual golfer.

The same could hold true for a form/"continuous subform".

Well...just a thought.

Good luck,

Gary Walter

 

[Michael Wong]

Ok, sorry for having given you the other way, by report:

Sort your records, which include a sorting by date descending.
Add a textbox TxtCounter with a control source =1 and running sum set to
over group.

On the Format event of the detail section, add the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = TxtCounter > 20
End Sub

Hope that might help