OK, you've got this a bit arse-about-face, as they say.
What you want to do is something more like this, logically speaking:
In other words, the second select is the one we built up in your previous query.
So, something more like:
Something like that.
answered Dec 11 '12 at 10:22 PM
MAn i'm so close.
Here is the access code
This is what prints to screen:
error Msg 102, Level 15, State 1, Line 16 Incorrect syntax near '('.
Had to add few things but i thought you might like to see end result.
Thank you again.SELECT Expr1, MyMfr, , , , , , , , , , , ,  FROM ( 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, MONTH(AdDate) AS AdDateMonth, tblmain.AdCost 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%' GROUP BY tblmain.AdDate, tblmain.AdCost, 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 )P
PIVOT ( sum(AdCost) FOR addatemonth IN (, , , , , , , , , , , ) ) AS pvt ORDER BY MyMfr
answered Dec 12 '12 at 04:51 PM