Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table (Access 2000)

    Is there a way to convert a query into an excel pivot table??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table (Access 2000)

    You can base a pivot table in Excel on data in Access instead of on data in Excel itself.
    In Excel, select Data | PivotTable and PivotChart Report...
    In the first step of the Wizard, select "External Data Source".
    In the second step, click "Get Data..."
    Select MS Access Database in the Select Data Source dialog, then browse to the database.
    Next, the Query Wizard will start.
    If you follow all the steps, you'll be returned to the Pivot Table Wizard.
    Continue as with a standard pivot table in Excel.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Access 2000)

    I found this on the knowledge database

    The following subroutine uses Automation to call the Microsoft Excel Median() function.
    Half of the set of numbers fall below and half above the median. Sub xlMedian()

    Dim obj As Excel.Application
    Set obj = CreateObject("Excel.Application")
    MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13)
    obj.Quit
    Set obj = Nothing
    End Sub

    Could this be done to run the pivot table wizard?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table (Access 2000)

    Automation would be the way to go if you want to create an Excel pivot table in code. You'd best create and test the code in Excel first, then port it to Access. Look up the Add method of the PivotCaches object in Excel VBA help for an example.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Access 2000)

    I am quite unfamilar with excel besides the basics and i do not know what you mean by "port it to access" or "pivotcaches"

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table (Access 2000)

    In that case, perhaps you shouldn't try it. You can't expect the Lounge to write your whole application for you. We can help you with specific questions.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (Access 2000)

    I did not expect that from anyone, I thought it might be simple explanations.

    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table (Access 2000)

    Creating a pivot table from a database in code is not a trivial exercise. I suggest that you brush up on your Excel VBA before attempting this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •