question

OK, so the thing to remember here is the [`CASE` expression][1]. Use that to take out all your Access `IIF`s. SELECT CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END As Expr1, CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END AS mymfr, SUM(tblMain.AdCost) AS SumOfAdSize INTO ytdAll FROM tblMain LEFT JOIN tblUpdate ON Year(tblMain.AdDate) = tblUpdate.Year LEFT JOIN tblBrand ON tblMain.mfr = tblBrand.Manufacturer GROUP BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END , CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END ORDER BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END A few things to bear in mind... 1. I moved the `WHERE` Clause to a `JOIN` criterion 2. That `JOIN` criterion may not be so performant - you might want to consider changing it in the future. You'll be wanting to eliminate the call to the `YEAR` function to improve speed of processing. 3. The `CASE` statement in the `ORDER BY` clause can be replaced with a 1 to tell SQL Server to sort by the first column in the result set. [1]: http://msdn.microsoft.com/en-us/library/ms181765.aspx

i have one other problem related its similar to the one you helped me with but it has a pivot SELECT CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END As Expr1, CASE WHEN Manufacturer '' THEN Manufacturer ELSE 'Other' END AS mymfr FROM tblMain LEFT JOIN tblUpdate ON Year(tblMain.AdDate) = tblUpdate.[Year] LEFT JOIN tblBrand ON tblMain.mfr = tblBrand.Manufacturer and tblmain.media not like '%tv%' PIVOT ( datepart(Month,addate) for addate in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT GROUP BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END , CASE WHEN Manufacturer '' THEN Manufacturer ELSE 'Other' END ORDER BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END ENd