First off you should not store this data in a new column in your table.
It should be calculated in a query.

I would calculate the new column to be a percent of the top performer in that RetailSegment

You can see this by creating an new blank access database

Add the following table

tblRetailStore
------------------
RetailStoreID (autonum) <--- PrimaryKey
NumericInfo (number)
RetailSegment (text)


Add some values to that table then create a new query

use the following formula to calculate the RankByRetailSegment

RankByRetailSegment: [tblRetailStore].[NumericInfo]/DMax("NumericInfo","tblRetailStore","RetailSegment ='" & [tblRetailStore]![RetailSegment] & "'")


Or just cut and paste this SQL Statement into the SQL design view of the query

SELECT tblRetailStore.RetailStoreID, tblRetailStore.NumericInfo, tblRetailStore.RetailSegment, [tblRetailStore].[NumericInfo]/DMax("NumericInfo","tblRetailStore","RetailSegment ='" & [tblRetailStore]![RetailSegment] & "'") AS RankByRetailSegment
FROM tblRetailStore
ORDER BY tblRetailStore.RetailSegment, [tblRetailStore].[NumericInfo]/DMax("NumericInfo","tblRetailStore","RetailSegment ='" & [tblRetailStore]![RetailSegment] & "'") DESC;

HTH
-Merk


David 11/25/03 11:07AM
I have a database that includes both numeric and categorization
information about a set of retail stores. I want to create one
additional column in the database that represents the rank of each store
within its segment (i.e. the ranking for a numeric performance field
within the store's segment, as defined by one of the categorization
fields). I want the ranking to be in any of three possible forms -
either a numeric rank, or a percentile, or simple categorization (e.g.,
Top 20%, Bottom 80%).

I have not been able to figure out any way to do this other than by
manual, brute force - creating a page for each segment, calculating
rankings there, and then bringing results back to the main database by
formula. I have tried the percentile function - which appears to work on
an entire database, even if the database is filtered (not the way sums
and averages work). I have also hunted for database functions, but have
not found anything to do what I need.

Can anyone offer ideas on whether there is something other than my brute
force approach?

Thanks in advance