I am having problems in summation of data in Pivot Table. The source of the data is a 'One to Many' query in MS Access. The name of the master table is 'OrderMaster' and it contains the fields 'OrderNo', 'OrderDate', 'Customer','Location' and 'OrderValue' The details table is called 'OrderDetails' which contains the fields 'OrderNo', 'ProductOrdered' and 'QuantityOrdered'. The tables are related on the field 'OrderNo'.
The 'OrderValue' field is displayed in the 'Data' section of the Pivot Table. The problem is that due to the One to Many relationship, the 'OrderValue' could be repetitive. This results is wrong SubTotals and GrandTotals. For e.g. if OrderNo 'Ord111' of '100000' OrderValue has 2 entries in the 'OrderDetails' table, the value displayed for the 'OrderValue' is double the actual value. This wrongly shows that the OrderNo 'Ord111' is of '200000' OrderValue.
This problem can be solved by using 'Average' instead of using 'Sum' in the 'Data' section for the 'OrderValue' field. But in this case, the SubTotals as also the GrandTotals also show the 'Average' values. We would want the SubTotals and GrandTotals to show the 'Sum' of the 'Average' values.
Please help.
The 'OrderValue' field is displayed in the 'Data' section of the Pivot Table. The problem is that due to the One to Many relationship, the 'OrderValue' could be repetitive. This results is wrong SubTotals and GrandTotals. For e.g. if OrderNo 'Ord111' of '100000' OrderValue has 2 entries in the 'OrderDetails' table, the value displayed for the 'OrderValue' is double the actual value. This wrongly shows that the OrderNo 'Ord111' is of '200000' OrderValue.
This problem can be solved by using 'Average' instead of using 'Sum' in the 'Data' section for the 'OrderValue' field. But in this case, the SubTotals as also the GrandTotals also show the 'Average' values. We would want the SubTotals and GrandTotals to show the 'Sum' of the 'Average' values.
Please help.
