Crosstab Query Fields

[DS]

I have a crosstab query.

TRANSFORM CCur(Nz(Sum([CDQuantity]*[CDPrice]),0)) AS TL
SELECT tblCheckDetails.CDBizDay
FROM tblCheckDetails INNER JOIN (tblRptSetUp INNER JOIN tblSalesCats ON
tblRptSetUp.SalesCatID = tblSalesCats.SalesCatID) ON
tblCheckDetails.CDReportID = tblRptSetUp.ReportCatID
GROUP BY tblCheckDetails.CDBizDay
PIVOT tblSalesCats.SalesCatName In
("FOOD","LIQUOR","WINE","BEER","OTHER","SUB","TAX","TOTAL");

What I would like to do is the "Other" SalesCat field to be a total of the
SalesCat fields other than "Food","Liquor","Wine" or "Beer".
How would I do this?
Thanks
DS

 

[John Spencer]

I would try the following. If you want Sub, Tax, and Total as separate
headings then add them into the IIF statement and into the In clause of PIVOT
clause

TRANSFORM CCur(Nz(Sum([CDQuantity]*[CDPrice]),0)) AS TL
SELECT tblCheckDetails.CDBizDay
FROM tblCheckDetails INNER JOIN (tblRptSetUp INNER JOIN tblSalesCats ON
tblRptSetUp.SalesCatID = tblSalesCats.SalesCatID) ON
tblCheckDetails.CDReportID = tblRptSetUp.ReportCatID
GROUP BY tblCheckDetails.CDBizDay
PIVOT IIF(tblSalesCats.SalesCatName In ("FOOD","LIQUOR","WINE","BEER"),
tblSalesCats.SaleCatName,"Other")
In ("FOOD","LIQUOR","WINE","BEER","OTHER");

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[DS]

Thanks John,
I'l play further with this today. Thank you very much.
DS