Rank without doubling up

[Tanya]

Hi I have finally managed to work out how to rank the results from a MI Quiz,
however I cannot work out how to avoid having to scores equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya

 

[Duane Hookom]

You can't break ties without specifying some tie breaking rule.

 

[louisjohnphillips]

You can't break ties without specifying some tie breaking rule.
--
Duane Hookom
Microsoft Access MVP









- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Duane Hookom]

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

 

[Tanya]

Thank you for this clarification of the problem Duane, I wasn't sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not possible?

cheers
Tanya

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Duane Hookom]

I think it is still possible to break ties but you have to decide how ties
are to be broken.

--
Duane Hookom
Microsoft Access MVP

Thank you for this clarification of the problem Duane, I wasn't sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not possible?

cheers
Tanya

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP

On Jun 15, 9:04 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
You can't break ties without specifying some tie breaking rule.
--
Duane Hookom
Microsoft Access MVP



:
Hi I have finally managed to work out how to rank the results from a MI Quiz,
however I cannot work out how to avoid having to scores equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya- Hide quoted text -

- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Tanya]

Hi Duane
I am sorry but you have lost me.
I don't understand what ties are :|

Kind Regards
Tanya

I think it is still possible to break ties but you have to decide how ties
are to be broken.

--
Duane Hookom
Microsoft Access MVP

Thank you for this clarification of the problem Duane, I wasn't sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not possible?

cheers
Tanya

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP


:

On Jun 15, 9:04 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
You can't break ties without specifying some tie breaking rule.
--
Duane Hookom
Microsoft Access MVP



:
Hi I have finally managed to work out how to rank the results from a MI Quiz,
however I cannot work out how to avoid having to scores equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya- Hide quoted text -

- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Duane Hookom]

A "tie" is like having "2 in second place". If Joe and Jane both have a rank
of 2, which one gets the "2" and which one gets the "3"?

--
Duane Hookom
Microsoft Access MVP

Hi Duane
I am sorry but you have lost me.
I don't understand what ties are :|

Kind Regards
Tanya

I think it is still possible to break ties but you have to decide how ties
are to be broken.

--
Duane Hookom
Microsoft Access MVP

Thank you for this clarification of the problem Duane, I wasn't sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not possible?

cheers
Tanya

:

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP


:

On Jun 15, 9:04 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
You can't break ties without specifying some tie breaking rule.
--
Duane Hookom
Microsoft Access MVP



:
Hi I have finally managed to work out how to rank the results from a MI Quiz,
however I cannot work out how to avoid having to scores equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya- Hide quoted text -

- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[scubadiver]

What is wrong with two people with an equal score given equal ranks?

A "tie" is like having "2 in second place". If Joe and Jane both have a rank
of 2, which one gets the "2" and which one gets the "3"?

--
Duane Hookom
Microsoft Access MVP

Hi Duane
I am sorry but you have lost me.
I don't understand what ties are :|

Kind Regards
Tanya

I think it is still possible to break ties but you have to decide how ties
are to be broken.

--
Duane Hookom
Microsoft Access MVP


:

Thank you for this clarification of the problem Duane, I wasn't sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not possible?

cheers
Tanya

:

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP


:

On Jun 15, 9:04 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
You can't break ties without specifying some tie breaking rule.
--
Duane Hookom
Microsoft Access MVP



:
Hi I have finally managed to work out how to rank the results from a MI Quiz,
however I cannot work out how to avoid having to scores equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya- Hide quoted text -

- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Michel Walsh]

None, but they will either both get the rank 2, either the rank 3. Assume we
take the lowest, 2, then:


10 11 11 12 13 ... values
1 2 2 4 5 ... ranks


note that then, in this case, there is no one with rank = 3.



Vanderghast, Access MVP

What is wrong with two people with an equal score given equal ranks?

A "tie" is like having "2 in second place". If Joe and Jane both have a
rank
of 2, which one gets the "2" and which one gets the "3"?

--
Duane Hookom
Microsoft Access MVP

Hi Duane
I am sorry but you have lost me.
I don't understand what ties are :|

Kind Regards
Tanya

:

I think it is still possible to break ties but you have to decide how
ties
are to be broken.

--
Duane Hookom
Microsoft Access MVP


:

Thank you for this clarification of the problem Duane, I wasn't
sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not
possible?

cheers
Tanya

:

louisjohnphillips,
I don't think you have grasped what the query creates for a
record set. The
[Rank] field in the original query will actually number the
records
sequentially based on score. The MIUnionQuery must be referenced
in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one
record has
the same Rank based on the same Score. The original SQL is very
good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP


:

On Jun 15, 9:04 am, Duane Hookom
<duanehookom@NO_SPAMhotmail.com>
wrote:
You can't break ties without specifying some tie breaking
rule.
--
Duane Hookom
Microsoft Access MVP



:
Hi I have finally managed to work out how to rank the
results from a MI Quiz,
however I cannot work out how to avoid having to scores
equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya- Hide quoted text -

- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery".
From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself,
but does
not use the StudentID key. Therefore, it will join pairs of
Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less
that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Tanya]

Thanks, now I understand what you were talking about... I thought ties may
have had something to do with relationships between queries and/or tables...

You have a very valid point!

I guess in this case it would be beneficial to break ties by order of
another field i.e. althabetical order of 'skill'.

cheers
Tanya

A "tie" is like having "2 in second place". If Joe and Jane both have a rank
of 2, which one gets the "2" and which one gets the "3"?

--
Duane Hookom
Microsoft Access MVP

Hi Duane
I am sorry but you have lost me.
I don't understand what ties are :|

Kind Regards
Tanya

I think it is still possible to break ties but you have to decide how ties
are to be broken.

--
Duane Hookom
Microsoft Access MVP


:

Thank you for this clarification of the problem Duane, I wasn't sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not possible?

cheers
Tanya

:

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP


:

On Jun 15, 9:04 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
You can't break ties without specifying some tie breaking rule.
--
Duane Hookom
Microsoft Access MVP



:
Hi I have finally managed to work out how to rank the results from a MI Quiz,
however I cannot work out how to avoid having to scores equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya- Hide quoted text -

- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Duane Hookom]

That's what I was talking about. Your tie breaker would need to not allow
duplicates or else you will have the same issue.

--
Duane Hookom
Microsoft Access MVP

Thanks, now I understand what you were talking about... I thought ties may
have had something to do with relationships between queries and/or tables...

You have a very valid point!

I guess in this case it would be beneficial to break ties by order of
another field i.e. althabetical order of 'skill'.

cheers
Tanya

A "tie" is like having "2 in second place". If Joe and Jane both have a rank
of 2, which one gets the "2" and which one gets the "3"?

--
Duane Hookom
Microsoft Access MVP

Hi Duane
I am sorry but you have lost me.
I don't understand what ties are :|

Kind Regards
Tanya

:

I think it is still possible to break ties but you have to decide how ties
are to be broken.

--
Duane Hookom
Microsoft Access MVP


:

Thank you for this clarification of the problem Duane, I wasn't sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not possible?

cheers
Tanya

:

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP


:

On Jun 15, 9:04 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
You can't break ties without specifying some tie breaking rule.
--
Duane Hookom
Microsoft Access MVP



:
Hi I have finally managed to work out how to rank the results from a MI Quiz,
however I cannot work out how to avoid having to scores equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya- Hide quoted text -

- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Tanya]

Hi Duane

I have the idea now... below are my 3 related queries, how could I change
the result in query 1 OR 2 so that I don't have a tie? would it be a where
statement in query 1? i.e. WHERE
Verbal/Linguistic.Score=Logical/Mathematical.Score ADD 1
[As you can see my SQL skills are limited]

Query1: MITotals
SELECT Students.StudentID, [VL1]+[VL2]+[VL3]+[VL4]+[VL5]+[VL6]+[VL7]+[VL8]
AS [Verbal/Linguistic], [LM1]+[LM2]+[LM3]+[LM4]+[LM5]+[LM6]+[LM7]+[LM8] AS
[Logical/Mathematical], [VS1]+[VS2]+[VS3]+[VS4]+[VS5]+[VS6]+[VS7]+[VS8] AS
[Visual/Spacial], [IN1]+[IN2]+[IN3]+[IN4]+[IN5]+[IN6]+[IN7]+[IN8] AS
Interpersonal, [MU1]+[MU2]+[MU3]+[MU4]+[MU5]+[MU6]+[MU7]+[MU8] AS Musical,
[NA1]+[NA2]+[NA3]+[NA4]+[NA5]+[NA6]+[NA7]+[NA8] AS Naturalist,
[BK1]+[BK2]+[BK3]+[BK4]+[BK5]+[BK6]+[BK7]+[BK8] AS [Body/Kinesthetic],
[IR1]+[IR2]+[IR3]+[IR4]+[IR5]+[IR6]+[IR7]+[IR8] AS Intrapersonal
FROM Students INNER JOIN [MI Quiz Data] ON Students.StudentID = [MI Quiz
Data].StudentID;


Query2: MIUnionQuery
SELECT StudentID, "Verbal/Linguistic" AS Skill, [Verbal/Linguistic] AS Score
FROM MITotals
UNION ALL SELECT StudentID, "Logical/Mathematical" AS Skill,
[Logical/Mathematical] AS Score FROM MITotals
UNION ALL SELECT StudentID, "Visual/Spacial" AS Skill, [Visual/Spacial] AS
Score FROM MITotals;
UNION ALL SELECT StudentID, "Body/Kinesthetic" AS Skill, [Body/Kinesthetic]
AS Score FROM MITotals;
UNION ALL SELECT StudentID, "Musical" AS Skill, [Musical] AS Score FROM
MITotals;
UNION ALL SELECT StudentID, "Interpersonal" AS Skill, [Interpersonal] AS
Score FROM MITotals;
UNION ALL SELECT StudentID, "Intrapersonal" AS Skill, [Intrapersonal] AS
Score FROM MITotals;
UNION ALL SELECT StudentID, "Naturalist" AS Skill, [Naturalist] AS Score
FROM MITotals;

Query3: RankQuery
SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)>=[a2].[Score] Or (a1.Score)=[a2].[Score]))
GROUP BY a1.StudentID, a1.Skill, a1.Score;


Cheers
Tanya

 

[scubadiver]

Hello,

I would like to add the point that if a person has an equal score on two
different abilities then surely they should be treated with equal importance?
Isn't that what you are trying to achieve.

The ranks are 'arbitrary' so they don't mean anything in themselves.

Thanks, now I understand what you were talking about... I thought ties may
have had something to do with relationships between queries and/or tables...

You have a very valid point!

I guess in this case it would be beneficial to break ties by order of
another field i.e. althabetical order of 'skill'.

cheers
Tanya

A "tie" is like having "2 in second place". If Joe and Jane both have a rank
of 2, which one gets the "2" and which one gets the "3"?

--
Duane Hookom
Microsoft Access MVP

Hi Duane
I am sorry but you have lost me.
I don't understand what ties are :|

Kind Regards
Tanya

:

I think it is still possible to break ties but you have to decide how ties
are to be broken.

--
Duane Hookom
Microsoft Access MVP


:

Thank you for this clarification of the problem Duane, I wasn't sure how to
best describe the problem.

From your earlier post, I gather you are saying what I ask is not possible?

cheers
Tanya

:

louisjohnphillips,
I don't think you have grasped what the query creates for a record set. The
[Rank] field in the original query will actually number the records
sequentially based on score. The MIUnionQuery must be referenced in the query
twice in order to see where a record falls in the ranking.

The OP wants to know how to break the ties where more than one record has
the same Rank based on the same Score. The original SQL is very good. It only
needs to identify which record in a tie might be the higher rank.

--
Duane Hookom
Microsoft Access MVP


:

On Jun 15, 9:04 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
You can't break ties without specifying some tie breaking rule.
--
Duane Hookom
Microsoft Access MVP



:
Hi I have finally managed to work out how to rank the results from a MI Quiz,
however I cannot work out how to avoid having to scores equal. i.e. 2 in
second place.

Here is my sql

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE (((a1.Score)<=a2.Score Or (a1.Score)=a2.Score))
GROUP BY a1.StudentID, a1.Skill, a1.Score
ORDER BY a1.Score DESC;

Any suggests please?

Kind Regards
Tanya- Hide quoted text -

- Show quoted text -

Perhaps a better description of the problem would help here.

This query seems to employ a single table named "MIUnionQuery". From
the table's name one might assume it is derived from some other
data.

Nonetheless, this query joins the "MIUnionQuery" with itself, but does
not use the StudentID key. Therefore, it will join pairs of Scores
without regard to which student earned the score.

Further, the WHERE clause is redundant. A1.Score is to be less that
or equal to A2.Score.
There is no need to repeat the condition.

To rank Students by their cumulative Scores one might query:

SELECT a1.StudentID, a1.skill, sum( a1.Score )
from MIUnionQuery as a1
group by a1.StudentID, a1.skill
order by 3

 

[Duane Hookom]

I agree with others that having two people with similar scores and ranks
makes sense to me. Also, you still haven't suggeste how you would break the
ties. If StudentID is numeric and you want to use it as a tie breaker, you
could use:

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM MIUnionQuery AS a1, MIUnionQuery AS a2
WHERE a1.Score + (a.StudentID/10000)>=[a2].[Score] + (a2.StudentID/10000)
GROUP BY a1.StudentID, a1.Skill, a1.Score;

IMHO, your table structure resembles a spreadsheet rather than a relational
database. A better structure would create a single record for each score so
you wouldn't need a union query to sort it all out.

 

[scubadiver]

I did send Tanya an improved design with what you suggest but she didn't
want to change

(sorry T!)


:

IMHO, your table structure resembles a spreadsheet rather than a relational
database. A better structure would create a single record for each score so
you wouldn't need a union query to sort it all out.

 

[Duane Hookom]

I assumed someone had provided the union queries

 

[scubadiver]

my design didn't involve union queries!!

I assumed someone had provided the union queries