Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Newbie (2003 All Updates)

    Edited by HansV to reduce huge screenshot in size.

    I need to analyse income and expenses related to a series of motorbike races. Races can be made at a number of tracks each year and all Income and expenses record the account these relate to (eg ticket sales) as well as the particular track and year they relate to eg 61 EC means Eastern Creek, Round 1, 2006, 71 EC means Eastern Creek, Round 1, 2007, 62 WA means Round 2 Western Australia, 2006 etc.

    A Pivot table seems to give me the perfect potential layout for this analysis and I can get the Income or Expense Account as rows down the left side of the page, and the Rounds and associated years across the top as the columns (see attached) and I can choose which years, rounds etc - Magic stuff

    However (isnt there always a however)
    I dont need the Total Row as I have already summarised these in a summary query - is there a way to get rid of this
    I also dont need the Total column as its meaningless in context
    I would like sub-totalling (may be asking too much here) so that all Accounts starting with 4 (I can add an account type if appropriate) as sub-totalled as Income, All those starting with 5 sub-totalled as Cost of Goods and all Accounts starting with 6 as Expenses, with a grandtotal of all 3 types
    I would also like to be able to export this to Excel with exactly the same layout (or close to) still with only the Totals.

    Appreciate any suggestions
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Newbie (2003 All Updates)

    Hi
    Here's a sub to remove totals fields adapted from Programming Access 2003 by Rick Dobson:
    As to the sub-totalling, maybe you'd better post your mdb here.

    Sub RemovePriceAndQuantityTotals(strFName As String)
    Dim frm1 As Access.Form

    'Open a form named strFName
    DoCmd.OpenForm strFName, acFormPivotTable
    Set frm1 = Forms(strFName)

    'Remove totals from datasheet and Totals field list setting
    With frm1.PivotTable.ActiveView
    .DataAxis.RemoveTotal "Sum of Price" 'removes the appearance
    .DataAxis.RemoveTotal "Sum of Quantity"
    .DeleteTotal "Sum of Price" 'removes it from the PivotTable
    .DeleteTotal "Sum of Quantity"
    End With

    'Show Detail Rows
    Screen.ActiveDatasheet.PivotTable.ActiveData.ShowD etails
    frm1.PivotTable.AllowDetails = True

    'Save changes as you close form's PivotTable view
    DoCmd.Close acForm, frm1.Name, acSaveYes

    End Sub

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

    Re: Pivot Table Newbie (2003 All Updates)

    I don't know much about Access pivot tables, since I dislike the way they work. I prefer to use a crosstab query in Access or a pivot table in Excel based on an Access table or query (you can create an Excel pivot table directly from Access data, without importing the data into Excel).
    Crosstab queries don't have subtotals, but Excel pivot tables do - add a field to the query in Access that returns the first character of Account, and use this field in the Excel pivot table.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Newbie (2003 All Updates)

    Thank you both very much for your help.
    As suggested, I have attached a cut down version of the database. The Pivot Table is very close to what I want except for the Total Column after each round (and I even (accidentally !!) got sub totalling working). My perfect world would be to also get this to excel in exactly the same format but I'll keep working on that.
    Re the process to remove the totals - where would I have this activated eg OnReport
    Thanks again
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Pivot Table Newbie (2003 All Updates)

    Depending on what you prefer to see along the top, drag either Job Name or Job Number off the pivot table.

    You can copy/paste the pivot table into Excel. but the result will be static, not a pivot table. If you want a pivot table in Excel, you'll have to create it there.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Newbie (2003 All Updates)

    Thanks again - I've been experimenting with the linking in Excel and its looking pretty good
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

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