How do I produce a top 5 or top 10 list from access (values determined via AHP model)

121 Views Asked by At

I want to produce a list of top 5 or top 10(descending) car matches from an access database. Currently this provides me with a single best match. I'm using an Analytical Hierarchy Process (AHP) Model. My output is determined by a series of calculated weights from Criteria and Sub-Criteria matrices based on user preferences(from UserForm). I provided an album to show an overview of my program. Please let me know if I need to provide any more information.

https://i.stack.imgur.com/sX73I.jpg

StrSQL = "Select * from carInfo where " & Worksheets("AHP Code").Range("Q3")
    & " = '" & Worksheets("AHP Code").Range("S3") & "'"

strConnect = "Provider=Microsoft.Ace.OLEDB.12.0; Data Source=" & ThisWorkbook.Path
    & "/CarInfo.accdb;"
Dim rs As New Recordset

rs.Open StrSQL, strConnect, adOpenStatic

Do Until rs.EOF
    Worksheets("final car").Range("C2") = rs("Make")
    Worksheets("final car").Range("C4") = rs("Model")
    Worksheets("final car").Range("C6") = rs("Year")
    Worksheets("final car").Range("C8") = rs("Price")
    Worksheets("final car").Range("C10") = rs("Age")
    Worksheets("final car").Range("C12") = rs("Mileage")
    Worksheets("final car").Range("C14") = rs("MPG Highway")
    Worksheets("final car").Range("C16") = rs("ColorExterior")
    Worksheets("final car").Range("C18") = rs("NumberofSeats")
    Worksheets("final car").Range("C20") = rs("Gas/Hybrid/Electric")
    Worksheets("final car").Range("C22") = rs("DriveType")
    Worksheets("final car").Range("C24") = rs("Type")
    rs.MoveNext
Loop
rs.Close

ActiveWorkbook.Worksheets("final car").Activate
Unload Me
0

There are 0 best solutions below