LARGE function in access 2000

F

Franky

Is it possible to use the excel function: LARGE in access 2000. or to write
it in vba???
 
K

Ken Snell [MVP]

If you set a reference to EXCEL in the Visual Basic Editor, you could use
the worksheet function in VBA code. But it probably would be easier to write
your own function as adding the EXCEL reference can add overhaed and version
problems.

What does the LARGE worksheet function do?
 
K

Ken Snell [MVP]

Try Allen's suggestion, and post back if it doesn't work for your situation.
 
F

Franky

allready tried , i think the best way to solve this is to write a function in
access but i don't know how
Franky
 
J

Jamie Collins

Franky said:
allready tried , i think the best way to solve this is to write a function in
access but i don't know how
Click to expand...

No, it's always best to use a query if you can. What about:

SELECT
T1.playerID, T1.score
FROM
tbl_score T1
WHERE 4 > (
SELECT
COUNT(*)
FROM
tbl_score T2
WHERE
T1.score < T2.score
AND T1.playerID=T2.playerID
)
ORDER BY
T1.playerID,
T1.score
;

Jamie.

--
 
F

Franky

allready tried that, for some players it gives 8 scores , for other players
it give 7 scores, and some it give 4.
 
K

Ken Snell [MVP]

Perhaps something like this (not tested):

SELECT tbl_score.playerID, tbl_score.score
FROM tbl_score
WHERE tbl_score.scoreID IN
(SELECT TOP 4 T.scoreID
FROM tbl_score AS T
WHERE T.playerID = tbl_score.playerID
ORDER BY T.score DESC, T.scoreID)
ORDER BY tbl_score.playerID, tbl_score.score DESC;

--

Ken Snell
<MS ACCESS MVP>
 
J

Jamie Collins

allready tried that, for some players it gives 8 scores , for other players
it give 7 scores, and some it give 4.
Click to expand...

Did you even test it? I tested it on your data as posted and it
returns a maximum of four scores per player. It also has the advantage
over the proprietary TOP N syntax that others have posted.

Jamie.

--
 
F

Franky

Try
scoreID playerID score
1 01 19
2 01 19
3 02 19
4 02 20
5 01 18
6 01 20
7 01 20
8 01 19
9 01 19
10 01 20
and see the result. Players can have as much scores as they want. They can
have 3 times 20 , 3 times 19 , 2 times 18 etc..
Then the query fails
 
K

Ken Snell [MVP]

Sure.

The subquery is generating the list of scoreID values that correspond to the
records with the four highest scores for a specific playerID. This subquery
returns only 4 records, even if there are ties, because it's using both the
score and scoreID values for sorting -- because each record has a unique
scoreID value, there are no "ties" that would return more than 4 records by
the TOP 4 predicate.

Then, the main query uses that list of scoreID values as the joining values
to select the appropriate records for the main query to display for the
specific playerID value.
--

Ken Snell
<MS ACCESS MVP>
 
F

Franky

ok, clear
Thanks

Ken Snell said:
Sure.

The subquery is generating the list of scoreID values that correspond to the
records with the four highest scores for a specific playerID. This subquery
returns only 4 records, even if there are ties, because it's using both the
score and scoreID values for sorting -- because each record has a unique
scoreID value, there are no "ties" that would return more than 4 records by
the TOP 4 predicate.

Then, the main query uses that list of scoreID values as the joining values
to select the appropriate records for the main query to display for the
specific playerID value.
Click to expand...
 
Top