top n macro via union

[therzakid]

Hi All,

I recently came across some code I thought would solve my problems. However, it doesn't. Although i get a "top 3" query for each store, it doesn't actually work. I get top 3 for the first sote, but all the others below are random. I like the code because it's dynamic, also my sales file has 100k+ records so a sub query takes a really long time.

Here is the code I'm using:

Option Compare Database
Option Explicit
Function SelectTop3From_tbl_MoM()
    Const c_SQL As String = "SELECT TOP 3 * FROM [tbl_MoM] WHERE SiteNo = '@' and [Fiscal Year] = ""2012"" ORDER BY [Net Invoiced Revenue] DESC"
    
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    ' Gather all existing values for CompanySymbol.
    '
    strSQL = "SELECT DISTINCT SiteNo FROM [tbl_MoM];"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' Assemble the query.
    '
    strSQL = ""
    With rst
        Do Until .EOF
            If strSQL <> "" Then strSQL = strSQL & " UNION "
            strSQL = strSQL & Replace(c_SQL, "@", !SiteNo)
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    strSQL = "SELECT * FROM (" & strSQL & ") ORDER BY [Net Invoiced Revenue] DESC;"
    
    ' Edit the existing query object and replace its SQL property by the SQL previously assembled, then open it.
    '
    Set qdf = CurrentDb.QueryDefs("top_3_items_from_MoM")    ' There must be a query named "Qry_Top_3_Sales" in the database
                                                        ' (can be: "SELECT * FROM Tbl_Sales", only the name matters).
    qdf.SQL = strSQL
    DoCmd.OpenQuery "top_3_items_from_MoM"
    Set qdf = Nothing
    
End Function

And, here is a sample of the output SQL:

SELECT *
FROM (SELECT TOP 3 * FROM [tbl_MoM] WHERE SiteNo = '03' and [Fiscal Year] = "2012" ORDER BY [Net Invoiced Revenue] DESC UNION SELECT TOP 3 * FROM [tbl_MoM] WHERE SiteNo = '10' and [Fiscal Year] = "2012" ORDER BY [Net Invoiced Revenue] DESC UNION SELECT TOP 3 * FROM [tbl_MoM] WHERE SiteNo = '12' and [Fiscal Year] = "2012" ORDER BY [Net Invoiced Revenue] DESC UNION SELECT TOP 3 * FROM [tbl_MoM] WHERE SiteNo = '17' and [Fiscal Year] = "2012" ORDER BY [Net Invoiced Revenue] DESC) AS [%$##@_Alias]
ORDER BY [Net Invoiced Revenue] DESC;





Thanks for any guidance and help you might provide - much appreciated.

[June7]

Could you provide some sample data so can test with? An Excel spreadsheet of about 1000 of the tbl_MoM records would be enough. Make sure there is an assortment of SiteNo and Year values.

[therzakid]

Could you provide some sample data so can test with? An Excel spreadsheet of about 1000 of the tbl_MoM records would be enough. Make sure there is an assortment of SiteNo and Year values.

Thanks for your reply, June. Below is the link:

http://www.4shared.com/folder/8AUNxZQY/ pw= 5678

I look forward to hearing any suggestions and moreover a solution! Thank you for your time.

[June7]

My experimentation shows that the ORDER BY clause is ignored after the first SELECT line of the UNION query and the sort order is applied to the entire UNION dataset. I am thinking this is a bug in the Access SQL engine. Never thought much about it but have seen this behavior before.

I suggest an alternative is to write the top 3 records to a temp table. Purge the table each time the procedure is run.

[therzakid]

I took your advice and made some code to simply delete records from the top 10 table and fill them in by running an appened query N amount of times. Thanks for the direction June7, you're great.