Ranking question

[golfinray]

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

 

[Steve]

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
(e-mail address removed)

 

[Guest]

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

 

[John Spencer]

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