LARGE function in access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
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?
 
Do you have a single field as the primary key in the table? Or is the
primary key a multi-field key?
 
Hi Franky

When you ask for the top n records, Access returns more than n only when:
a) there is a tie (exactly the same values), and
b) there is no way to tell them apart.

You can give it a way to tell the records apart by sorting your query on the
primary key, e.g.:

SELECT TOP 5 ...
FROM ...
WHERE ...
ORDER BY MyPrimaryKeyField;

If you have a composite key, list the fields separated by commas in the
ORDER BY clause
 
table = tbl_score with scoreID , playerID and score
scoreID is primary key . Every player can have as much scores as he wants.
Some have 10 , some 100 so it can be
scoreID playerID score
1 01 19
2 01 19
3 02 19
4 02 20
5 01 18
6 01 21
7 01 23


and so on... I need the 4 highest scores for each player. for example
player 01 has 23 21 19 19
The LARGE(array;k) function in excel provides this but i need it in Access
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
 
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.

--
 
allready tried that, for some players it gives 8 scores , for other players
it give 7 scores, and some it give 4.
 
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>
 
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.

--
 
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
 
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>
 
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...
 
Back
Top