Access Pivot Table to show % of all records when only top 10 items are shown

[General Ledger]

Dear All,

I am using Access 2007 and am at best an intermediate user so please be detailed in your response.

I have a pivot table where I show the top 10 customers based on the sume of open sales orders. The fields are Customer Name and Sales. I added another instance of Sales and have the value shown as a % of the total. This works as expected.

My problem is I want the % of Sales to be based on the total of all open sales for all customers, not just the 10 customers being shown in the pivot table.

Can anyone provide guidance on how to get the % to calculated on total sales orders rather than for just the shown records?

Best regards,

General Ledger

 

[alansidman]

After you have created your first query, create a second one with the same table as the record source. Create an aggregate to sum the values.

Here is the SQL statement for the first, substitute your field names as appropriate.

SELECT TOP 5 Table1.ID, Table1.pName, Sum(Table1.pValue) AS SumOfpValue
FROM Table1
GROUP BY Table1.ID, Table1.pName;

Here is the SQL for the second query:

SELECT "" AS ID, "total" AS pName, Sum(Table1.pValue) AS SumOfpValue
FROM Table1
GROUP BY "", "total";

Now do a Union query to join the two queries into one. You now have the details for the top 5 (in this case) and the total for the table in the second query. You can now use this to create your Pivot table.

Alan