Ranking scores

S

Steve S

I have a table that contains fields for scores in an athletic event and the
rank of that score within a subset of competitors in a certain skill and age
level. the SQL string created by the query GUI is

UPDATE Entries SET Entries.Rank =
IIf([Entries].[score]>0,1+DCount("*","Entries","[score]>" &
[Entries].[score]),Null)
WHERE (((Entries.[Fee ID])=495) AND ((Entries.[Age Group ID])=4));

when I run this update query ALL scores, even thoes outside the subset are
included in determining the ranking but only the ones in the subset are
updated. Instead of getting ranks such as 1,2,3,4,5 I get 2, 30,115, 209,
357. I think the problem is with the IIF clause but I can not see the problem

any help is appreciated.

Steve S
 
D

Duane Hookom

I think this is what you need.
UPDATE Entries SET Entries.Rank =
IIf([Entries].[score]>0,1+DCount("*","Entries","[score]>" &
[score] & " And [Fee ID]=495 AND [Age Group ID]=4"),Null)
WHERE (((Entries.[Fee ID])=495) AND ((Entries.[Age Group ID])=4));
 
S

Steve S

That worked. thanks very much. I guess I need to become more familiar with
the aggregate functions. Can you explain the difference between a Domain and
a SQL Aggregate? How are they different?

Steve

Duane Hookom said:
I think this is what you need.
UPDATE Entries SET Entries.Rank =
IIf([Entries].[score]>0,1+DCount("*","Entries","[score]>" &
[score] & " And [Fee ID]=495 AND [Age Group ID]=4"),Null)
WHERE (((Entries.[Fee ID])=495) AND ((Entries.[Age Group ID])=4));


--
Duane Hookom
MS Access MVP
--

Steve S said:
I have a table that contains fields for scores in an athletic event and the
rank of that score within a subset of competitors in a certain skill and
age
level. the SQL string created by the query GUI is

UPDATE Entries SET Entries.Rank =
IIf([Entries].[score]>0,1+DCount("*","Entries","[score]>" &
[Entries].[score]),Null)
WHERE (((Entries.[Fee ID])=495) AND ((Entries.[Age Group ID])=4));

when I run this update query ALL scores, even thoes outside the subset are
included in determining the ranking but only the ones in the subset are
updated. Instead of getting ranks such as 1,2,3,4,5 I get 2, 30,115,
209,
357. I think the problem is with the IIF clause but I can not see the
problem

any help is appreciated.

Steve S
 
D

Duane Hookom

Domain aggregate values return single values and aren't very optimized.

--
Duane Hookom
MS Access MVP
--

Steve S said:
That worked. thanks very much. I guess I need to become more familiar
with
the aggregate functions. Can you explain the difference between a Domain
and
a SQL Aggregate? How are they different?

Steve

Duane Hookom said:
I think this is what you need.
UPDATE Entries SET Entries.Rank =
IIf([Entries].[score]>0,1+DCount("*","Entries","[score]>" &
[score] & " And [Fee ID]=495 AND [Age Group ID]=4"),Null)
WHERE (((Entries.[Fee ID])=495) AND ((Entries.[Age Group ID])=4));


--
Duane Hookom
MS Access MVP
--

Steve S said:
I have a table that contains fields for scores in an athletic event and
the
rank of that score within a subset of competitors in a certain skill
and
age
level. the SQL string created by the query GUI is

UPDATE Entries SET Entries.Rank =
IIf([Entries].[score]>0,1+DCount("*","Entries","[score]>" &
[Entries].[score]),Null)
WHERE (((Entries.[Fee ID])=495) AND ((Entries.[Age Group ID])=4));

when I run this update query ALL scores, even thoes outside the subset
are
included in determining the ranking but only the ones in the subset are
updated. Instead of getting ranks such as 1,2,3,4,5 I get 2, 30,115,
209,
357. I think the problem is with the IIF clause but I can not see the
problem

any help is appreciated.

Steve S
 

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.

Ask a Question

Similar Threads


Top