Pivot table not working in adp

Question

Pivot table not working in adp

Aaron Olmstead

SSC Eights!

Points: 805
More actions
September 28, 2004 at 3:50 pm

#66537

I have a query that I want to open in Pivot Table mode, so I use the code:
DoCmd.OpenView "dbo.qryHoursPivot", acViewPivotTable, acEdit
The pivot tables open, but when I try to set the rows/columns/detail, the message: "The query could not be processed: The data provider didn't supply any further error information". The query opens fine in datasheet mode, so I don't understand what the problem could be. Any ideas?
Note I am using an .adp file in Access 2002 linked to a SQL Server 2000 database
October 1, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Benjamin S Taylor SSC Journeyman Points: 93 More actions · Answer 1

SQL Server does not support the MS Access Pivot Table. OUCH!!!

There are a number of methods to reproduce the Pivot Table in SQL Server. I have an article in VB Developers Journal Oct, 2002 with three methods. If you are a member of the Developers Journal you can find it in the archives. I don't have the article available to me now.

There are also some third party tools that create pivot queries for you (I don't know the name off hand but you can find them as I did by surfing).

For right now the easiest method to get something is to use the case statement. I have seen this published in a number of journals (but it is the slowest method).

Yukon will suport pivot queries.

Cheers,

Ben

Benjamin S Taylor SSC Journeyman Points: 93 More actions · Answer 2

Thought I would provide a little more insight on a pivot table.

A pivot table query actually uses a physical PIVOT TABLE.

For example, if you wanted to pivot the first three months of the yearyou would have a pivot table that looked as follows:

Month Jan Feb Mar

1 1 0 0

2 0 1 0

3 0 0 1

For a full year you would have additional month columns and rows of data. Note that only the month column that correlates with the month value of the current row has the value 1.

To make a pivot query you would join this table to your detail on the month value.

From sales

JOIN MonthPivot on Sales.Month = MonthPivot.Month

Then in the select statement you would use three formulas to return results:

JanSales = MAX(Sales.TotalSales * MonthPivot.Jan),

FebSales = MAX(Sales.TotalSales * MonthPivot.Feb),

MarchSales = MAX(Sales.TotalSales * MonthPivot.Mar)

So your query looks as follows:

SELECT