Pivot Table Settings? query Could Not Be Processed

  • Hey, I just started trying to play around with Pivot Tables in Access, but keep getting an error.
    I tried following this Microsoft Office Tutorial:
    http://office.microsoft.com/en…3.aspx?pid=CL100570041033
    But when I start dropping the fields in I get this error in the details box of the pivot table.
    "The query could not be processed:
    o The data provider didn't supply any further error information"


    Do I have a setting or preference setup worong? Anybody have any ideas I could try?

    Thanks,
    Jason

  • Re: Pivot Table Settings? query Could Not Be Processed


    Hey there


    Looks like you've stumped us all with this one ;)


    What are you using as a data source? The mention of "data provider" tends to point in that direction. Maybe there is a security issue, so where does the data source reside in relation to you as a network user - assuming you are on a network?


    Some more background on the environment in which you are doing this would probably help to clarify this.


    Cheers


    Rowan

  • Re: Pivot Table Settings? query Could Not Be Processed


    Im still fairly new to Access. What do you mean by the data source? For this particular example I was just using the Microsoft example database. I am using Access at my job so there could be a security issue. Is there preferences or security settings I can check?
    Thanks

  • Re: Pivot Table Settings? query Could Not Be Processed


    Hi Jason


    Data source - in this case I was trying to find out whether the actual data was held in an Access, or perhaps SQL Server, database. Also whether it is sitting on your local PC or somewhere on the network.


    If you are using the sample then I assume you are using the Northwind mdb (Access database) and that it is located on your local PC which should eliminate any network security issues. Unless you have very limited rights to your own PC?? You will need full control rights to the folder where the Access mdb is located.


    That brings us back to the query itself. I guess the first thing to work out is whether or not you can perform a simple select query. Have you tried any yet? If not, give one a go and let me know how you get on. Otherwise can you post the text of the query which is failing (go to SQL view in design mode).


    Cheers


    Rowan

  • Re: Pivot Table Settings? query Could Not Be Processed


    Yes, I am using the Northwind.mdb. I can do all of the queries, select, crosstab, etc. I am just having no luck with the pivot tables.


    Here is the SQL of the failing qry:
    SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [FirstName] & " " & [LastName] AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, Orders.Freight
    FROM Shippers INNER JOIN (Products INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Shippers.ShipperID = Orders.ShipVia;


    Let me know if you have any other ideas. Thanks Again.

  • Re: Pivot Table Settings? query Could Not Be Processed


    Hi Jason


    Well I copied the SQL you included your last post and I could both run it and create a pivot table view.


    I am now begining to suspect that there may be an issue with your Office install, although I am using Office 2003 rather than XP.


    I have done a bit of a Google search on this and have come up with plenty of information on a similar problem involving SQL Server Cubes and pivot tables. In one of them there was a suggestion (subsequent to the poster incurring the problem having re-installed his software) that running a "detect & Repair" from the help menu of any of the Office products would have fixed this problem.


    Lacking any better advice for you at the moment, if Office XP includes this option (2003 does as I have actually used it myself) then I suggest you give it a try. You will need the installation CD so that the process can re-install anything it finds at fault.


    Post back to let us know how it goes. If you still can't get it to work I will have another go at Google for something more specific to Access.


    All the best. Regards


    Rowan

  • Re: Pivot Table Settings? query Could Not Be Processed


    It must be some error on the computer because the pivot table works fine when I get on a different computer. I guess I need to reload Access. Thanks for the help

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!