Ranking By Groups

[ssowjanyau]

Hello

I have a access db table where i have region sales by area, I would like to rank the areas within the region by sales amount,

Region    Area     Sales
R1          A1       100
R1          A2       200
R2          A1       300
R1          A1       400

The output I would like is

Region    Area     Sales  Rank
R1          A1       100    3
R1          A2       200    2
R2          A1       300    1
R1          A1       400    1

Any help on this is appreciated

Thx

[ADezii]

@ssowjanyau
I do have a code-based solution that will work, but I'm sure that there must be a better, more efficient, SQL-based approach. Be patient and wait and see what the other more 'SQL Qualified Members' have to say regarding this Thread. If no valid solutions are forthcoming, then I'll post my code-based response, Check in later.

[NeoPa]

This is possible, but what you're asking is quite unclear.

What are you grouping on? You say Area within Region, yet R1 A1 is listed twice at different ranks in your expected output.

Also, the name of the table would be helpful to know for creating relevant SQL.

Welcome to Bytes!

[ADezii]

@ssowjanyau
The following code segment assumes the following:

1. Your Table Name is tblSales, if it is not, just make the necessary changes in Lines 7, 15, and 16.
2. The Fields and their Data Types in tblSales are:
   • [Region] - {TEXT}
   • [Area] - {TEXT}
   • [Sales] - {CURRENCY}
   • [Rank] - {INTEGER} - (must add to tblSales)
3. Execute the following code, your desired Output, (Ranking in the [Rank] Field), should be in tblSales:
   

   Dim strSQL As String
   Dim MyDB As DAO.Database
   Dim rstDistinctRegions As DAO.Recordset
   Dim rstSalesByRegion As DAO.Recordset
   Dim intRank As Integer
    
   strSQL = "Select Distinct tblSales.[Region] From tblSales"
    
    
   Set MyDB = CurrentDb
   Set rstDistinctRegions = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    
   With rstDistinctRegions
     Do While Not .EOF
       Set rstSalesByRegion = MyDB.OpenRecordset("Select * From tblSales Where tblSales.[Region] = '" & _
                                   ![Region] & "' Order By tblSales.[Sales] Desc;", dbOpenDynaset)
         Do While Not rstSalesByRegion.EOF
          intRank = intRank + 1
            rstSalesByRegion.Edit
              rstSalesByRegion![Rank] = intRank
            rstSalesByRegion.Update
            rstSalesByRegion.MoveNext
         Loop
           intRank = 0
          Debug.Print
       .MoveNext
     Loop
   End With
    
   rstSalesByRegion.Close
   rstDistinctRegions.Close
   Set rstSalesByRegion = Nothing
   Set rstDistinctRegions = Nothing

4. Any questions, please feel free to ask. I would still wait for additional Replies, since this is probably not the most efficient solution. I'm sure NeoPa has one already brewing! (LOL)!