Using TOP with SELECT

[Jerry Baker]

I am familiar with using SQL statements such as "SELECT TOP !) FROM
....", but I have a situation that has me stumped. I have a table similar
to this (but with thousands of individuals):

NAME SCORE AVERAGE
Bob 5 3.25
Ned 4 3.67
Bob 3 3.25
Bob 4 3.25
Ned 2 3.67
Bob 1 3.25
Ned 5 3.67

I can't figure out how to get Access to give me the top n people rather
than the top n records. I tried grouping on name, but then I lose the
ability to see each score. I need a query that will return:

NAME SCORE AVERAGE
Ned 5 3.67
Ned 4 3.67
Ned 2 3.67

In response to something like "SELECT TOP 1 NAME".

 

[KARL DEWEY]

It appears you are want the data associated with the individual with the
highest average. I think this is what you want.

SELECT Jerry_Baker.NAME, Jerry_Baker.SCORE, Jerry_Baker.AVERAGE
FROM Jerry_Baker
WHERE ((((SELECT Max(Jerry_Baker.AVERAGE) FROM Jerry_Baker))=[AVERAGE]))
ORDER BY Jerry_Baker.SCORE DESC;

 

[Jerry Baker]

It appears you are want the data associated with the individual with the
highest average. I think this is what you want.

I actually want the top 20 individuals by average, while being able to
see each individual score. This is so the data can go into a report
grouped by individual with each test score listed. The number of scores
each individual may have is arbitrary and unpredictable, otherwise I
would just do TOP 100 if they all had 5 scores, etc.

 

[KARL DEWEY]

Try this using two queries - replace table and query names with whatever
yours are.

Jerry_Baker_TOP20 ---
SELECT TOP 20 Jerry_Baker.AVERAGE, Jerry_Baker.NAME
FROM Jerry_Baker
GROUP BY Jerry_Baker.AVERAGE, Jerry_Baker.NAME
ORDER BY Jerry_Baker.AVERAGE DESC;


SELECT Jerry_Baker.NAME, Jerry_Baker.SCORE, Jerry_Baker.AVERAGE
FROM Jerry_Baker INNER JOIN Jerry_Baker_TOP20 ON (Jerry_Baker.NAME =
Jerry_Baker_TOP20.NAME) AND (Jerry_Baker.AVERAGE = Jerry_Baker_TOP20.AVERAGE)
ORDER BY Jerry_Baker.AVERAGE DESC;

 

[gramelsp]

I am familiar with using SQL statements such as "SELECT TOP !) FROM
...", but I have a situation that has me stumped. I have a table similar
to this (but with thousands of individuals):

NAME SCORE AVERAGE
Bob 5 3.25
Ned 4 3.67
Bob 3 3.25
Bob 4 3.25
Ned 2 3.67
Bob 1 3.25
Ned 5 3.67

I can't figure out how to get Access to give me the top n people rather
than the top n records. I tried grouping on name, but then I lose the
ability to see each score. I need a query that will return:

NAME SCORE AVERAGE
Ned 5 3.67
Ned 4 3.67
Ned 2 3.67

In response to something like "SELECT TOP 1 NAME".

You have a solution, so consider this just for fun.

Open a new database, create a new module and copy the
subprogram into the module, save the module, in the
immediate window, type call CreateTable and press enter.

It is sort of like your data except it does not start with
data already averaged. It uses TOP 1 so as to illustrate
that it works, that is, hopefully works. (watch for line wrapping)

Sub CreateTable()
With CurrentProject.Connection

.Execute _
"CREATE TABLE MyTable" & _
" (person_name VARCHAR (30) NOT NULL," & _
" test_date DATETIME NOT NULL," & _
" test_score DECIMAL (6,2)," & _
" PRIMARY KEY (person_name, test_date));"

.Execute "INSERT INTO MyTable VALUES('Bob','5/1/2007',5);"
.Execute "INSERT INTO MyTable VALUES('Bob','5/7/2007',3);"
.Execute "INSERT INTO MyTable VALUES('Bob','5/14/2007',4);"
.Execute "INSERT INTO MyTable VALUES('Bob','5/21/2007',1);"
.Execute "INSERT INTO MyTable VALUES('Ned','5/1/2007',4);"
.Execute "INSERT INTO MyTable VALUES('Ned','5/14/2007',2);"
.Execute "INSERT INTO MyTable VALUES('Ned','5/21/2007',5);"

.Execute _
"CREATE VIEW Top_Test_Averages AS " & _
" SELECT MyTable.person_name," & _
" MyTable.test_date," & _
" MyTable.test_score," & _
" (Select Count (*) FRom Mytable as a where a.person_name = " &
_
" MyTable.person_name and a.test_date <=MyTable.test_date) AS
[Tests Taken]," & _
" (Select Sum(a.test_score) FRom Mytable as a where
a.person_name = " & _
" MyTable.person_name and a.test_date <=MyTable.test_date) AS
[Total Score]," & _
" Round((Select Sum(a.test_score) FRom Mytable as a where
a.person_name =" & _
" MyTable.person_name and a.test_date <=MyTable.test_date)/" & _
" (Select Count (*) FRom Mytable as a where a.person_name = " &
_
" MyTable.person_name and a.test_date <=MyTable.test_date),2) AS
[Tests Average]" & _
" FROM MyTable INNER JOIN (SELECT TOP 1 b.person_name,
Avg_score" & _
" FROM (SELECT b.person_name, Avg(b.test_score) AS Avg_score" &
_
" FROM MyTable AS b" & _
" GROUP BY b.person_name) AS b" & _
" ORDER BY Avg_score DESC) AS b" & _
" ON MyTable.person_name = b.person_name;"

Dim rs
Set rs = .Execute("SELECT * FROM Top_Test_Averages;")
MsgBox rs.GetString
rs.Close

End With
End Sub