Ranking scores

[Guest]

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

 

[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));

 

[Guest]

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

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
--

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

 

[Duane Hookom]

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

--
Duane Hookom
MS Access MVP
--

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

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
--

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