Crosstab Report?

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I want to build a report that would mirror this...
FOOD LIQUOR BEER WINE SUB TAX
TOTAL
03/01/2008 $5.00 $0.00 $0.00 $0.00 $5.00 .41
$5.41
03/02/2008 $5.00 $0.00 $0.00 $0.00 $5.00 .41
$5.41
03/03/2008 $4.00 $1.00 $0.00 $0.00 $5.00 .41
$5.41
etc....


The Date and Amounts are in one Table - Table1
The FOOD, LIQUOR, etc. are in another. - Table2
The two tables are connect by a third table...-Table3

Table 1 Table 3 Table2
Date
Amount SalesID SalesID
ReportID ReportID SalesName

Any help is appreciated.
Thanks
DS
 
OK Duane, I made this crosstab query and it worked...

TRANSFORM Sum(QCross.TL) AS SumOfTL
SELECT QCross.CDBizDay
FROM QCross
GROUP BY QCross.CDBizDay
PIVOT QCross.SalesCatName;

Untill another salesgroup was added...Then the report came up missing the
new Sales Group! So I gues this is what you mean by Dynamic! I'll try to
figure out a little more from your example and get back to the group.
Thanks Duane
DS
 
This works in the Query...
TRANSFORM Sum([CDQuantity]*[CDPrice]) 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;

However in the Report I get an Error message...
Cannot use fixed column in a subquery!
No idea what this means?
Thanks
DS
 
Found This....but I'm still clueless!
Thanks
DS

Cannot use the crosstab of a non-fixed column as a subquery. (Error 3637)
You have attempted to sort on a variable column crosstab. You should save
the results of the query, and then sort the results with the Sort Ascending
or Sort Descending button.
 
You can try set the Column Headings property of the crosstab to all possible
values from the SalesCatName field or use the solution I suggested.
 
Thanks Duane. How would I do this? The setting of the properties for all
fields. It's only five of them.
Thanks
DS
 
Back
Top