how to query table by last 20 dates for each contact ID

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

MS Access 2003 -

I have golf scores entered into a table with date, contact ID, and course
ID. How do I use a query to filter the last 20 scores by date for each
contact ID?
 
See:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/kb/210039/en-us
for a couple of different approaches.

If the target for this query is to create a report, you could also do it by
creating a report that shows the ContactID and CourseID, and use a subreport
to show the top 20 scores. The query for the subreport is then dead simple:
in query design view, just enter 20 into the Properties box beside the Top
Values property.
 
Allen, thanks for the effort -

I tried Method 1 from your link as the following -

SELECT Scores.Date, Scores.[Contact ID], Scores.[Tee ID], Scores.Score
FROM Scores
WHERE (((Scores.Date) In (Select Top 20 [Date] From Scores Where [Contact
ID]=[Contact ID] Order By [Date] Desc)))
ORDER BY Scores.Date DESC;

still obtained same results as before - last 20 scores regardless of Contact
ID - I am sure this is simple but I can't get there.

Date Contact ID Tee ID Score
5/23/2005 2 5 55
5/23/2005 1 5 45
5/22/2005 2 4 50
5/22/2005 1 4 40
5/21/2005 2 4 50
5/21/2005 1 4 40
5/20/2005 1 5 45
5/20/2005 2 5 55
5/19/2005 2 4 50
5/19/2005 1 4 40
5/18/2005 2 4 50
5/18/2005 1 4 40
5/17/2005 1 4 40
5/17/2005 2 4 50
5/16/2005 1 4 40
5/16/2005 2 4 50
5/15/2005 1 4 40
5/15/2005 2 4 50
5/14/2005 1 4 40
5/14/2005 2 4 50
 
got it - realized the greouping doesn't work unless I have an outside table.
I linked the members table in and all is well

looks like this now

SELECT Scores.Date, Scores.[Contact ID], Scores.[Tee ID], Scores.Score
FROM Scores INNER JOIN Members ON Scores.[Contact ID] = Members.[Contact ID]
WHERE (((Scores.Date) In (Select Top 20 [Date] From Scores Where [Contact
ID]=[Members].[Contact ID] Order By [Date] Desc)))
ORDER BY Scores.Date DESC;

many many thanks Allen ..........

scrappydue said:
Allen, thanks for the effort -

I tried Method 1 from your link as the following -

SELECT Scores.Date, Scores.[Contact ID], Scores.[Tee ID], Scores.Score
FROM Scores
WHERE (((Scores.Date) In (Select Top 20 [Date] From Scores Where [Contact
ID]=[Contact ID] Order By [Date] Desc)))
ORDER BY Scores.Date DESC;

still obtained same results as before - last 20 scores regardless of Contact
ID - I am sure this is simple but I can't get there.

Date Contact ID Tee ID Score
5/23/2005 2 5 55
5/23/2005 1 5 45
5/22/2005 2 4 50
5/22/2005 1 4 40
5/21/2005 2 4 50
5/21/2005 1 4 40
5/20/2005 1 5 45
5/20/2005 2 5 55
5/19/2005 2 4 50
5/19/2005 1 4 40
5/18/2005 2 4 50
5/18/2005 1 4 40
5/17/2005 1 4 40
5/17/2005 2 4 50
5/16/2005 1 4 40
5/16/2005 2 4 50
5/15/2005 1 4 40
5/15/2005 2 4 50
5/14/2005 1 4 40
5/14/2005 2 4 50



Allen Browne said:
See:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/kb/210039/en-us
for a couple of different approaches.

If the target for this query is to create a report, you could also do it by
creating a report that shows the ContactID and CourseID, and use a subreport
to show the top 20 scores. The query for the subreport is then dead simple:
in query design view, just enter 20 into the Properties box beside the Top
Values property.
Click to expand...
Click to expand...
 
Back
Top