G
Guest
Is it possible to use the excel function: LARGE in access 2000. or to write
it in vba???
it in vba???
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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?
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]
Do you have a single field as the primary key in the table? Or is the
primary key a multi-field key?
primary key a multi-field key?
A
Allen Browne
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
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
G
Guest
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
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
K
Ken Snell [MVP]
Try Allen's suggestion, and post back if it doesn't work for your situation.
G
Guest
allready tried , i think the best way to solve this is to write a function in
access but i don't know how
Franky
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
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.
--
G
Guest
allready tried that, for some players it gives 8 scores , for other players
it give 7 scores, and some it give 4.
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>
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.
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.
--
G
Guest
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
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
G
Guest
This works fine! Can you explain this one?
Thanks a lot!
Thanks a lot!
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>
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>
G
Guest
ok, clear
Thanks
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.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.