query or report to resemble pivot table - Please help.

[Mitchell_Collen via AccessMonster.com]

Hello Access Professionals:
My goal is to be able to print a report every day similar to the pivot table.
Please let me know how I can accomplish this.
Right now, I can accomplish this only by pasting the aggregated data in excel
or using the access pivot table. <--this doesn't print well in access. so I
usually paste in excel. I am using Access 2002 with SQL Server database.

My query shows this:
Area Hour Total
7 WST 5 7
3 WST 5 1
4 NOR 6 5

I need the data to show like this pivot table:
Area Hour 1 Hour 2 Hour 3
7 WST 2 0 0
3 WST 13 1 0
4 NOR 3 1 0

I have pasted my code for the aggregated data below.
SELECT Area, DATEPART(hh, OrderStateTime) AS Hour, COUNT(OrderStateTime)
AS Total
FROM (..lots of tables joined here)
WHERE (Product = '1') AND (OrderStateTime BETWEEN CONVERT(DATETIME, '2007-
06-17 07:00:00', 102) AND
CONVERT(DATETIME, '2007-06-18 06:59:00', 102))
GROUP BY Area, DATEPART(hh, OrderStateTime)

 

[Stefan Hoffmann]

hi Mitchell,

My goal is to be able to print a report every day similar to the pivot table.

The problem is that the report needs fixed column headers. Open your
pivot query in design view and open the property editor. Use the
ColumnHeadings property to assign fixed column names, then you can
easily bind it to your reports controls.


mfG
--> stefan <--

 

[Guest]

Mitch,

Checkout this link. http://support.microsoft.com/kb/328320

In order to do this properly, you need to create the report based on the
current set of query values. Then, you need to modify the code in your
report so that it will dynamically rename the header and reset the control
source for the fields in the details section of your report. The provided
link gives all the code and a brief explaination. There are other articles
as well.

Google on: Access +crosstab +report

HTH
Dale