Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2000
    Location
    Kent, UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Pivot table totals columns (Acc 2002 sr0)

    Hi

    Hope you can help and that this is enough information:

    We are trying to create a Pivot table query in Access2002. This works fine except there are loads of unwanted subtotal and totals columns. Is there any way to get rid of these or do I have to base a report on it to do this (if so, how? A pointer at an appropriate webpage or resource would do).

    Many thanks in advance for your time.

    Stuart

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove Pivot table totals columns (Acc 2002 sr0)

    Sorry, but are you trying to create a crosstab query for a report or a pivot table on a form?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Dec 2000
    Location
    Kent, UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Pivot table totals columns (Acc 2002 sr0)

    I knew that I hadn't included enough info ;o)

    At the moment I am just looking at the result of the query itself in pivot table view. The aim would be to produce a report from this, I just hoped that I could use a Q & D method by printing the query (if I was able to suppress the total subtotal columns). It was the Q & D method that this question was trying to facilitate.

    The pivot table was created by going to the "pivot table view" next to the design and sql views in the query.

    If there is a reasonable example of the creation of a report from a pivot table query then please feel free to point me in that direction.

    many thanks

    Stuart

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove Pivot table totals columns (Acc 2002 sr0)

    OK, now I know where you were looking in the query grid. Now please translate "Q & D" for me. That's one I don't recall running across before in any version of Access.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Dec 2000
    Location
    Kent, UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Pivot table totals columns (Acc 2002 sr0)

    Oops, sorry just slang, should have picked it up

    'Quick & Dirty'

    regards

    Stuart

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Remove Pivot table totals columns (Acc 2002 sr0)

    Stuart,

    It sounds as if you are creating the pivot table, i.e., select edit pivot table object and then refresh the data and want to get rid of the total and/or subtotal columns. If this is true, do the following:

    In the pivot table,
    Right click on the field value you want to change and select field settings. You will see radio buttons for subtotals. Set the radio button for subtotals for none to remove them.
    - Also note that if you select advanced, you can sort the pivot table data.

    I hope this is what you are looking for.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    New Lounger
    Join Date
    Dec 2000
    Location
    Kent, UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Pivot table totals columns (Acc 2002 sr0)

    Unfortunately not what I am doing (tried right clicking, but to no avail where I am creating this, the subtotals radio button doesn't exist). See my response to Charlotte for how the query is set up.

    However your approach to the pivot table sounds promising in terms of what we are trying to produce. How do I get to that point, if you don't mind me asking?

    Many thanks

    Stuart

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Remove Pivot table totals columns (Acc 2002 sr0)

    Stuart,

    Create a query that contains the data you want to analyze or stage the data in a table. If you create a new form, select the Pivot Table Wizard and base the wizard on the Table or Query that has the data. Follow the steps to create the pivot table.

    When the pivot table is created, Access will ask to edit the Pivot table object. Pressing this button launches the Pivot table in excel which links to the data. When in the pivot table area, select view, toolbars, pivot table to see the available fields that can be dragged and dropped into the Pivot table. You will also need to refresh the data before you can do anything. (Select the exclamation mark from the pivot table toolbar.)

    I use this feature as a quick way to provide users the ability to analyze their data and modify the view to what suits them. It works extremely well.

    When done analyzing with the Pivot table, close excel and you will jump back to access. Close the edit pivot table screen and you are back where you started.

    I have created a database that allows the users to make a number of selections to stage the data they want. The users can then create the pivot table view based on the data the selected. ....Fun Stuff...

    Let me know if you have any other questions.
    Regards,

    Gary
    (It's been a while!)

  9. #9
    New Lounger
    Join Date
    Dec 2000
    Location
    Kent, UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Pivot table totals columns (Acc 2002 sr0)

    Many thanks, I'll play around with this.

    Regards

    Stuart

Posting Permissions

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