Crosstab Report?

[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

 

[Duane Hookom]

There is a solution for printing crosstab reports with dynamic headings at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

If you have a static list of column headings, this becomes much more like a
standard report.

 

[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

 

[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

 

[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.

 

[Duane Hookom]

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.

 

[DS]

Thanks Duane. How would I do this? The setting of the properties for all
fields. It's only five of them.
Thanks
DS

 

[DS]

Got it Duane. Thank You.
DS