subtract amount of two fields

yahshuah
yahshuah used Ask the Experts™
on
subtract amount of two fields

[tblPurchaseOrder.Amount]*.12 as VAT, [tblPurchaseorder.Amount] - [Vat] as exVat, tblPurchaseorder.Amount

but exVat is = tlbpurchase.Amount it doesnt subtract tblpurchaseorder.Amount*0.12

whole sql querry

[tblPurchaseOrder.Amount]*.12 as VAT, [tblPurchaseorder.Amount] - [Vat] as exVat, tblPurchaseorder.Amount

SELECT tblPurchaseOrder.ProductID, tblPurchaseOrder.Description, tblPurchaseOrder.Quantity, tblPurchaseOrder.Unit, tblPurchaseOrder.UnitPrice, tblPurchaseOrder.PreparedBy, tblPurchaseOrder.ApprovedBy, tblPurchaseOrder.PurchaseOrderNo, tblPurchaseOrder.Status, [tblPurchaseOrder.Amount]*.12 as VAT, [tblPurchaseorder.Amount] - [Vat] as exVat, tblPurchaseorder.Amount
FROM tblPurchaseOrder INNER JOIN tblSupplier ON tblPurchaseOrder.SupplierID = tblSupplier.SupplierID
WHERE (((tblPurchaseOrder.Status)="Approved")) and  (((tblPurchaseOrder.quantity)<>0))   ;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
[tblPurchaseOrder.Amount]*.12 as VAT, [tblPurchaseorder.Amount] -([tblPurchaseOrder.Amount]*.12)  as exVat, tblPurchaseorder.Amount
Hi,

It could be an issue with using the alias, so 2 things to try

1. [tblPurchaseOrder.Amount]*.12 as VAT, ([tblPurchaseorder.Amount] - [Vat]) as exVat, tblPurchaseorder.Amount
2. [tblPurchaseOrder.Amount]*.12 as VAT, ([tblPurchaseorder.Amount] - ([tblPurchaseOrder.Amount]*.12)) as exVat, tblPurchaseorder.Amount

"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
One of your tables has a VAT column set to always zero, obviously - else you would get a "column name not found" error.
For that simple case here aneeshattingal's formula is best, but if you have more cases where you need to calculate, and use the result in another formula, it is better done with an ad-hoc view (or a Common Table Expression, CTE, on MSSQL 2005 and above).

select ProductID, Description, Quantity, Unit, UnitPrice, PreparedBy, ApprovedBy, PurchaseOrderNo, Status, VAT, Amount - VAT as exVat, Amount
from
(select tblPurchaseOrder.*, tblPurchaseOrder.Amount*.12 as VAT
FROM tblPurchaseOrder INNER JOIN tblSupplier ON tblPurchaseOrder.SupplierID = tblSupplier.SupplierID
WHERE tblPurchaseOrder.Status = "Approved" and tblPurchaseOrder.quantity <> 0) p
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:

Or, since [tblPurchaseOrder].[Amount]- ([tblPurchaseOrder].[Amount] * .12) is the same as

([tblPurchaseorder.Amount] * (1-.12))

you could use:

[tblPurchaseOrder.Amount]*.12 as VAT, ([tblPurchaseorder.Amount] * .88 as exVat, tblPurchaseorder.Amount

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial