query or report to resemble pivot table - Please help.

  • Thread starter Mitchell_Collen via AccessMonster.com
  • Start date


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,

Mitchell_Collen via AccessMonster.com said:
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.

--> stefan <--





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


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