Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: Multiple ranking based on different fields.

    Ok,

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


    Any help would be appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I have a way to do it using hidden ListBoxes.

    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:
    Code:
    =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:
    Code:
    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.Requery
    Me.RankStateList = Me.FilerID
    Me.txtRankState = Me.RankStateList.ListIndex + 1
    Do this for each ranking grouup.

    Have fun

    PS: It's a lot easier to accomplish this using the grouping levels in a report.

  3. #3
    Join Date
    May 2004
    Posts
    2
    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....



    SELECT
    t1.[Name],
    t1.Address,
    t1.City,
    t1.State,
    t1.Zip,
    t1.Volume,
    (SELECT COUNT(*)
    FROM yourtable as t2
    WHERE t2.Volume > t1.Volume) + 1 AS NationRank,
    (SELECT COUNT(*)
    FROM yourtable as t3
    WHERE t3.Volume > t1.Volume
    AND
    t3.State = t1.State ) + 1 AS StateRank,
    (SELECT COUNT(*)
    FROM yourtable as t4
    WHERE t4.Volume > t1.Volume
    AND
    t4.City = t1.City ) + 1 AS CityRank,
    (SELECT COUNT(*)
    FROM yourtable as t5
    WHERE t5.Volume > t1.Volume
    AND
    t5.Zip = t1.Zip ) + 1 AS ZipRank
    FROM yourtable as t1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •