multiple queries in one report 1

[womper]

I run a pool league. I have a large table of played matches. The table consists of PlayerID, Date, Win/Loss, Match Info........
The table is sorted by most recent date at top (ie. descending order) I want to create a report which includes the most recent 5 matches from every player. I can get the info I want by using PlayerID as criteria and selecting Top 5, but I have to enter the PlayerID for each player. Not acceptable for 200+ players. I could not get criteria to accept a loop. Can anyone suggest a way to create a temp table with only last 5 matches for each player or some code to call the query in a loop from min to max player within a report.

 

[PHV]

Something like this ?
SELECT A.PlayerID, A.Date, A.[Win/Loss], ...
FROM yourTable AS A
WHERE A.Date In (SELECT TOP 5 [Date]
FROM yourTable WHERE PlayerID=A.PlayerID ORDER BY 1 DESC)
ORDER BY 1, 2 DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

I have been looking into this. I don't have an answer yet. But let me verify your table structure. Is this a good representation??

matchID black white matchDate
1 Smith Jones 01-Jan-05
2 Smith Richards27-Feb-05
3 Cooper Smith 03-Feb-05
4 Jones Richards10-May-05
5 Jones Cooper 09-May-05
6 RichardsCooper 10-May-05
7 Baker Jones 01-Jun-05
8 Baker Smith 07-Jun-05
9 RichardsBaker 30-Mar-05
10 Baker Cooper 25-Apr-05

 

[womper]

My table looks like this :

PlayerID Date Win/Loss Skill Balls Rounds Safeties
2 06/09/2005 1 8 76 12 2
126 06/04/2005 0 3 21 19 1
43 06/03/2005 1 5 42 21 0

sorted by Date, more info to right but this is the idea. This table will grow to contain thousands of lines

 

[lespaul]

have you tried PHV's solution? should work for you, just modify the table name and you'll need to enclose Date in the SELECT statement in [] like the one further in the sub-query:

SELECT A.PlayerID, A.[Date].....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[OhioSteve]

Yes after looking at PHV's sql again, I see that it is pretty solid.

 

[womper]

Thanks folks. This was very helpful! Here is the actual code I used :

SELECT A.PlayerID, A.DATE, A.SkillLevelPlayed, A.NumRounds, A.NumBalls, A.WinLoss, A.Split, A.NumSafeties, A.BreakandRuns
FROM NineBallMatches AS A
WHERE A.DATE IN ( SELECT TOP 5 [DATE] FROM NineBallMatches WHERE PlayerID = a.PlayerID ORDER BY 1 DESC)
ORDER BY 1, 2 DESC;