Access Ranking Problem

[abesimpson]

I know similar questions have been asked and answered, but I can't figure out the answers to meet my requirements so hopefully someone can help me.

I have a database which consists of several stock tickers sorted on "Date" and and calculated value ("ABS"). What I require is to be able to generate a "Ranking Value" from the ABS value, where the highest ABS = 1, next highest =2, etc. for any particular date group, then starts again. I currently create the ranking in Excel and re-import the results into Access. I have almost reached the 65.5k record Excel limit and now need to do all the work in Access

The code I use to generate my un-ranked table is as follows:

INSERT INTO [Ranking ABS Access] ( Ticker, [Date/Time], Equationtype, Calcgain, ABS )
SELECT [Main program walkforward explore table].Ticker, [Main program walkforward explore table].[Date/Time], [Main program walkforward explore table].Equationtype, [Main program walkforward explore table].Calcgain, Abs([PositionScore]) AS ABS
FROM [Main program walkforward explore table]
ORDER BY [Main program walkforward explore table].[Date/Time], Abs([PositionScore]) DESC;

I have found the following code which by description does what I need (though it will take someone much smarter than I am to translate it to my needs) albeit for a differently structured database:

select a.Dept, a.Subdept, a.Amount, count(*) as rank
from
(
select Dept, Subdept, Amount
from joe
group by Dept, Subdept, Amount
) as a
inner join
(
select Dept, Subdept, Amount
from joe
group by Dept, Subdept, Amount
) as b on a.Dept = b.Dept and a.Amount <= b.Amount
group by a.Dept, a.Subdept, a.Amount
order by a.Dept, a.Subdept, count(*)

Any and all help/directions/suggestions/and even questions will be greatly appreciated.

Abe

 

[apr pillai]

Please check the following Code and use a similar method for your needs. The logic in the following Code we have used for determining the lowest Quoted Parties among Quotations invited for building materials. The Data is sorted on Company Code, Product Code and Ascending order on Rate. Company code and Product Code pairs are tested for the same product continuation with different rates and put in a loop (the inner loop) and directly updated the Rank Field with 1,2,3 in that order directly because the value is sorted in ascending order no further testing of value is not necessary. When the Product Code or company Code changes the rank variable i is reset to zero and starts with Rank 1,2,3 for the next group.

The Code is not tested and provided only as an example.

Public Function AwardRanks()
Dim db As Database, rst As Recordset, i As Integer
Dim oldProductCode As String, newProductCode As String
Dim oldCompany As String, newCompany As String
Dim strSQL As String

strSQL = "SELECT Quotations.COMPANY, Quotations.PRODUCTCOD, Quotations.DESCRIPTION, Quotations.RATE, RANK "
strSQL = strSQL & "FROM Quotations ORDER BY Quotations.COMPANY, Quotations.PRODUCTCOD, Quotations.DESCRIPTION, Quotations.RATE;"

Set db = CurrentDb

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

newCompany = rst![COMPANY]
newProductCode = rst![PRODUCTCOD]
oldCompany = newCompany
oldProductCode = newProductCode
Do While Not rst.EOF
    i = 0
       Do While newCompany = oldCompany And newProductCode = oldProductCode And Not rst.EOF
           i = i + 1
           With rst
              .Edit
              ![RANK] = i
              .Update
            End With
            rst.MoveNext
            If Not rst.EOF Then
                oldCompany = newCompany
                oldProductCode = newProductCode
                newCompany = rst![COMPNAY]
                newProductCode = rst![PRODUCTCOD]
             End If
        Loop
        oldCompany = newCompany
        oldProductCode = newProductCode
  Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Function

a.p.r. pillai