0

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
Community
  • 1
  • 1
payyam
  • 1
  • VBA and VB.NET are two different things. Try to use proper tags. – Steve May 02 '17 at 08:54
  • `SELECT TOP 10 foo FROM bar ORDER BY foo DESC` ? – Andre May 02 '17 at 09:03
  • If you need TOP N by a group criteria, review http://allenbrowne.com/subquery-01.html#TopN – June7 May 03 '17 at 20:52
  • Welcome! The key to asking a good question is to focus on what details really matter. It is not likely that an album of images and unnecessary code will inspire the answer you are looking for. From what I can tell, your question is primarily about the SQL statement, but it is not possible from the code nor the images to know what data `Worksheets("AHP Code").Range("S3")` contains. Have you tried inspecting the actual SQL statement (i.e. `Debug.Print StrSQL') to see what values you are selecting? TOP N (from other comments) will only help if you if the query returns multiple rows. – C Perkins May 04 '17 at 16:49

0 Answers0