Pivot Table Newbie (2003 All Updates)

Topic

New Reply

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

Reply | Quote

Replies

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.ShowDetails
frm1.PivotTable.AllowDetails = True

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

End Sub

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks again – I’ve been experimenting with the linking in Excel and its looking pretty good
Steve

Reply | Quote