Get Last 6 from List

[MacroAlan]

We have a table with thousands of comments on a program. Each tracking item has a number, one or more dates, and one or more comments.

I need to get the last 6 dates/comments from each tracking item.

{I don't seem to have access to upload a sample file}

ID    TrackID     Date        Comment
{auto}  202      10/20/07     Brown cow
2       202      11/02/07     Green cow

Alan

 

[MacroAlan]

Here is the file


Alan

 

[PHV]

Typed, untested (SQL code):

SELECT A.TrackingID, A.CommentID, A.CommentDate, A.By, A.UpdateReason, A.LongComment
FROM JobComments AS A
INNER JOIN JobComments AS B ON A.TrackingID = B.TrackingID AND A.CommentDate <= B.CommentDate
GROUP BY A.TrackingID, A.CommentID, A.CommentDate, A.By, A.UpdateReason, A.LongComment
HAVING Count(*) <= 6

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886