Unanswered: Multiple ranking based on different fields.
I have a table of clients, the primary key is a FilerID followed by Name, Address, City, State, Zipcode, and Volume. I need to rank each client based on Volume by overall, City, State, and Zipcode. For example, xyz company is 50th overall, 20th in TX, 10th in Austin, and 1st in 73301. I was able to do an overall rank in Excel, but the regional rankings get skewed on a resort. there are approximately 30,000 records per table, and 5 tables total. i've read a few topics on query ranking, but when I tried to adapt them to my fields, I crashed Access twice.....
Basically, set the recordsource of each hidden list box to be a "Totals" query that filters by the item you are grouping by. Set the bound column equal to the FilerID and set the value equal to the FilerID as well. The ListIndex property of the List Box + 1 will now equal the rank within the group.
For eample, to rank within State, set the Rowsource to something like:
=SELECT FilerID, Sum(Volume) AS SumOfVolume FROM tblData GROUP BY FilerID WHERE State = 'NY' ORDER BY Sum(Volume) DESC;
Set the property under the "OnCurrent" event of the form using a string:
Dim strSQL as String
strSQL = "=SELECT FilterID, Sum(Volume) AS SumOfVolume FROM tblData GROUP BY FilerID WHERE State = '" & [txtState] & "' ORDER BY Sum(Volume) DESC;"
Me.RankStateList.RowSource = strSQL
Me.RankStateList = Me.FilerID
Me.txtRankState = Me.RankStateList.ListIndex + 1
Do this for each ranking grouup.
PS: It's a lot easier to accomplish this using the grouping levels in a report.
Thanks for your reply, however I had posted this on one of Microsoft's disscussion groups as well and checked it first. It seemed to do the trick, so I thought I'd post it here in case anyone else could use it....
FROM yourtable as t2
WHERE t2.Volume > t1.Volume) + 1 AS NationRank,
FROM yourtable as t3
WHERE t3.Volume > t1.Volume
t3.State = t1.State ) + 1 AS StateRank,
FROM yourtable as t4
WHERE t4.Volume > t1.Volume
t4.City = t1.City ) + 1 AS CityRank,
FROM yourtable as t5
WHERE t5.Volume > t1.Volume
t5.Zip = t1.Zip ) + 1 AS ZipRank
FROM yourtable as t1