Crosstab Query Fields

D

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top