
Originally Posted by
NBVC
Try instead to add a new column to the main table called Total_Value with formula:
=[@Length]*[@UnitRate]
Then Add that column instead to the Pivot Table.
Hi NBVC,
That is working great as a temporary measure, but this original table that the pivot is querying is a live feed from an SQL Database... and something I read yesterday made complete sense (i did not even think of doing this) but I added a new SELECT line... see below. This way every time new data is read into the table it is always current and up to date!
Code:
SELECT
tblAsset.AssetID AS [AssetID],
tblAsset.AssetDescription AS [AssetDesc],
tblasset.dimension1 AS [Length],
tblTypes.TypeID AS [TypeID],
tblTypes.TypeCode AS [TypeCode],
tblTypes.TypeDescription AS [AssetType],
tblTypes.RepCost AS [UnitRate],
tblAsset.dimension1 * tblTypes.RepCost AS [Total_Price] <---- this was the winning line that helped me out!
FROM
tblAsset Left JOIN
tblTypes ON tblAsset.TypeID = tblTypes.TypeID
WHERE
tblTypes.TypeCode Like '05.05%' And
tblAsset.ValuationAsset = 1
Thanks heaps for your help.
Bookmarks