Code: Group and select first records

[cfobare79]

I am trying to figure out how I would write a code that will recognize information by same “ID” and then only take the first 6 years of that information. Each row has an ID and a corresponding year with other information associated with it. The data looks similar to this:


ID Year Avg HR
JohnSmith 1988 .789 78
JohnSmith 1989 .854 85
JohnSmith 1990 .456 85
TomJones 1978 .465 56
TomJones 1979 .165 85

[orange]

What is your table structure? What fields?

Can you be more specific?

write a code ---create a query There are free M$oft videos here

[cfobare79]

There are three tables imported from three different excel spreadsheets. I'm still working on which fields I actually will need. The problem I am having is figuring out the code to select the first "6" years of an ID. I think if I can be helped on that I will be able to add the rest of the fields I need easily enough... Well that is the plan at least.

[rpeare]

Top N by group:

http://stackoverflow.com/questions/9...-in-same-query

[cfobare79]

Below is the code I was able to get from those links. From this code it gave me the top 6 years but not the top 6 years for each player. How do I get it to give me top 6 years for each player?

SELECT Batting.playerID, Batting.yearID, Batting.G
FROM Batting
WHERE (((Batting.yearID) In (Select Top 6 [yearID] From [Batting] where [playerID]=[Batting].[playerID] Order by [yearID] Desc)))
ORDER BY Batting.yearID DESC;

[rpeare]

Post a sample of your data

[cfobare79]

As in attach the database? I don't see how to attach it?

[rpeare]

you have to click the 'go advanced' button on the bottom right of the posting area just zip it up before posting and remove any private information.

[cfobare79]

Attached is the file. Thank you for your help.

[rpeare]

I followed the guide exactly on the last post of the link I gave, just substituting your field names.

Create this query first:

SELECT Batting.playerID, Batting.yearID, IIf(IsNull([ab]) Or [ab]=0,0,[h]/[ab]) AS BatAvg
FROM Batting
ORDER BY Batting.playerID, IIf(IsNull([ab]) Or [ab]=0,0,[h]/[ab]) DESC;

Save it as QryPre

Create this query second:

SELECT PlayerID, YearID, BatAvg FROM QryPre WHERE BatAvg IN (SELECT TOP 6 BatAvg FROM QryPre as PreQry WHERE PreQry.PlayerID = QryPre.PlayerID ORDER BY BatAvg DESC)

top 6 years (by batting average) for each player