Sorting Crosstab query results?

[Dragongem]

Hello forum goers! I have a sort of hopefully easy question that has been challenging me. Unfortunately, I cannot post the databases themselves due to confidental info, though I'd be happy to make a table up if needed. Basically I have two queries. One does this:

 
SELECT Product_NO,SBR,Order_ID
FROM 18MonthData
WHERE OrderID (Select Order_ID From 18MonthData WHERE Product_NO = [Product_Part])

This essentially finds all of the orders that a particular product part is associated with, then pulls all the information for those orders. The next query looks at the query and does this:

 
PARAMETERS [Product_ID] Text ( 255 );
TRANSFORM Count(SelectProdId.Order_No) AS CountOfOrder_No
SELECT SelectProdId.Product_NO
FROM SelectProdId
GROUP BY SelectProdId.Product_NO
ORDER BY SelectProdId.SBR
PIVOT SelectProdId.SBR;

It counts all the order numbers for each part and then creates a table where it associates part by number of orders. SBR is a marker variable here. Now, if it were just one part, it would work fine- it's doing that well. However, I need to do this for 1000 product parts, and only the top 10 of that table. So ideally I would love to automatically sort that table by the count of orders, since I am trying to create a VBA solution. (note: code is below)

Sub RunFiles()

Dim db As DAO.Database
Dim rsValues As DAO.Recordset
Dim rsXTB As DAO.Recordset
Dim qdfXTB As DAO.QueryDef
Dim InputTable As DAO.TableDef
Dim OutputTable As DAO.TableDef
Set db = CurrentDb()

Set OutputTable = db.TableDefs("Outputtable")
Set qdfXTB = db.QueryDefs("SelectProdID")
Set QueryTop10 = db.QueryDefs("SelectProdID_Crosstab")
Set rsValues = db.OpenRecordset("SELECT Product_Name FROM Top1000")
Set InputTable = db.TableDefs("Top1000")

DoCmd.SetWarnings (False)

'-- Process the values in ColumnName one at a time...
Do Until rsValues.EOF
'-- pass the parameter from rsValues into the rsXTB recordset as a filter
qdfXTB.Parameters(0) = rsValues.Fields("Product_Name")
QueryTop10.Parameters(0) = rsValues.Fields("Product_Name")

'Add product


Set rsXTB = QueryTop10.OpenRecordset

'InputTable.Fields("Product_Name").Value

'DoCmd.RunSQL ("INSERT INTO OutputTable VALUES ( " & rsValues.Fields("Product_Name") & ", Null , Null)")

rsXTB.Sort


For i = 2 To 11
rsXTB.MoveNext
DoCmd.RunSQL  ("INSERT INTO OutputTable VALUES (" &  rsXTB.Fields("Product_NO").Value & "," & rsXTB.Fields("1").Value  & " ,1)")
Next i



rsXTB.Close
rsValues.MoveNext
Loop

rsXTB.Close
rsValues.Close
Set rsXTB = Nothing

DoCmd.SetWarnings (True)
End Sub

Is there a way to automatically sort the results of the crosstab? I can't do a sort in the query because it's a grouped variable. If not, should I pursue a VBA solution then?

Thanks in advance!

[rpeare]

How are you determining your 'top 10'

aphabetically?
some sort of quantity field?
some sort of date field?

Your crosstab SQL statement is below. It's currently sorting by the SBR field when you run through your code is it not following the same order that you have set up in your query?

PARAMETERS [Product_ID] Text ( 255 ); TRANSFORM Count(SelectProdId.Order_No) AS CountOfOrder_No SELECT SelectProdId.Product_NO FROM SelectProdId GROUP BY SelectProdId.Product_NO ORDER BY SelectProdId.SBR PIVOT SelectProdId.SBR;

Can I ask what you are trying to accomplish with this code, it looks like you're just creating at temp table, there may be a better solution if you are. Temp tables should be a last resort.