Ranking question

I have a wealth index field where school district wealth indexes are used to
rank projects. For example, a school district has a wealth index of .44 (44%)
will get state funds before one with a wealth index of .58 (58%). What is
need is a WI rank. For example, Conway school district, anytime one of their
construction projects is entered in the database, they are always ranked 134
so there are 133 districts with higher ranking (funds priority). The when we
save that project in the table it also saves their ranking. Would I use a
combo to display query results on that or what? Thanks a bunch!!!!
--
Milton Purdy
ACCESS
State of Arkansas

Your tables should look like:
TblSchoolDistrict
SchoolDistrictID
SchoolDistrictName
WealthIndex
other school district fields

TblSchoolDistrictProject
SchoolDistrictProjectID
SchoolDistrictID
ProjectSubmissionDate
other project fields

If you wanted a list of all projects for all school districts in the order
of wealth index, create a query based on both tables and sort descending on
Wealth Index.

Steve





"golfinray" wrote in message
...
I have a wealth index field where school district wealth indexes are used
to
rank projects. For example, a school district has a wealth index of .44
(44%)
will get state funds before one with a wealth index of .58 (58%). What is
need is a WI rank. For example, Conway school district, anytime one of
their
construction projects is entered in the database, they are always ranked
134
so there are 133 districts with higher ranking (funds priority). The when
we
save that project in the table it also saves their ranking. Would I use a
combo to display query results on that or what? Thanks a bunch!!!!
--
Milton Purdy
ACCESS
State of Arkansas

"golfinray" ¦b¶l¥ó±i¶K¤º®e¥D¦®
¤¤¼¶¼g...
I have a wealth index field where school district wealth indexes are used
to
rank projects. For example, a school district has a wealth index of .44
(44%)
will get state funds before one with a wealth index of .58 (58%). What is
need is a WI rank. For example, Conway school district, anytime one of
their
construction projects is entered in the database, they are always ranked
134
so there are 133 districts with higher ranking (funds priority). The when
we
save that project in the table it also saves their ranking. Would I use a
combo to display query results on that or what? Thanks a bunch!!!!
--
Milton Purdy
ACCESS
State of Arkansas

One way to get a rank
SELECT SchoolDistrict, WealthIndex,
, 1 + Dcount("*","SchoolDistricts","WealthIndex" & [WealthIndex]) as Rank
FROM SchoolDistricts

That will give you rankings From 1 to N and in cases of ties the tied schools
will all get the same rank and there will be a break in the ranks
....
..44 Someway 133
..44 Conway 133
..44 Sideway 133
..48 MyWay 136
..49 TheWay 137
....

If you needed to rank the wealth index with no breaks you can do that by
creating a list of unique wealth index values and then ranking the unique values.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

golfinray wrote:
I have a wealth index field where school district wealth indexes are used to
rank projects. For example, a school district has a wealth index of .44 (44%)
will get state funds before one with a wealth index of .58 (58%). What is
need is a WI rank. For example, Conway school district, anytime one of their
construction projects is entered in the database, they are always ranked 134
so there are 133 districts with higher ranking (funds priority). The when we
save that project in the table it also saves their ranking. Would I use a
combo to display query results on that or what? Thanks a bunch!!!!