VAT on Report

[DMWM]

Hi,

From reading around the subject I can see this is a very tricky one and
there are many opinions on it.

I have decided to go down the route of storing the VAT rate in a table and
referring it to it in the query to do the calculation.

Here's the problem:

There are products that are VAT exempt and products that are not. If they
are not the VAT rate is 15% (currently, provision is built in to change this
in the future)

Customers can order both VAT exempt and non exempt products

I only wish to calculate the VAT on items that are non exempt.

So far i have tblVAT with VAT_ID and VAT_Rate in and Product_VAT_ID in
tblProducts that is linked with the VAT_ID from tblVAT

I am trying to get the query to produce a field that is the VAT amount but
only if the product has the VAT_ID = 2 (VAT_ID = 1 is for exempt items and is
null)

this is the code i have devised from research on the newsgroup which is
definetely not working:

[IIf[tblVAT].VAT_ID=2,([Quantity]*[Actual_Price]*[VAT_Rate])]

NB: Quantity and Actual_Price are fields to distinguish how many of the item
and the net price, in this way i can work out the VAT line by line; this may
have been obvious though


Please can someone help me as I am tearing my hair out!!

Many Thanks

DMWM

 

[Wayne-I-M]

SELECT
IIf([tblVAT]![Vat_ID]=2,([OtherTableName]![Quantity]*[OtherTableName]![ActualPrice])*([tblVAT]![VATRate]/100))
AS VATcharge,
IIf([tblVAT]![Vat_ID]=2,([OtherTableName]![Quantity]*[OtherTableName]![ActualPrice])*([tblVAT]![VATRate]/100)+([OtherTableName]![Quantity]*[OtherTableName]![ActualPrice])) AS TOTALcharge
FROM tblVAT, OtherTableName;


Or

VATcharge:
IIf([tblVAT]![Vat_ID]=2,([OtherTableName]![Quantity]*[OtherTableName]![ActualPrice])*([tblVAT]![VATRate]/100))

Plus

TOTALcharge:
IIf([tblVAT]![Vat_ID]=2,([OtherTableName]![Quantity]*[OtherTableName]![ActualPrice])*([tblVAT]![VATRate]/100)+([OtherTableName]![Quantity]*[OtherTableName]![ActualPrice]))

 

[Clifford Bass]

Hi,

Why not set the VAT_Rate in the VAT_ID 1 row to 0? Then you can just
do a standard join and calculation.

select Quantity * Actual_Price as Total_Cost, Total_Cost * VAT_Rate as
VAT_Amount, Total_Cost + VAT_Amount as Total_Cost_With_VAT
from (tblInvoiceItems inner join tblProducts on tblProducts.ProductID =
tblInvoiceItems.ProductID) inner join tblVAT on tblVAT.VAT_ID =
tblInvoiceItems.Product_VAT_ID;

Clifford Bass

 

[Wayne-I-M]

The VAT rate even though normally stored as a number as really a percentage

So it's
simply

To ge the vat
((Number of products * cost of products) * (VAT rate / 100))

and to add the total it's
(((Number of products * cost of products) * (VAT rate / 100)) + (Number of
products * cost of products))


--
Wayne
Manchester, England.

Hi,

Why not set the VAT_Rate in the VAT_ID 1 row to 0? Then you can just
do a standard join and calculation.

select Quantity * Actual_Price as Total_Cost, Total_Cost * VAT_Rate as
VAT_Amount, Total_Cost + VAT_Amount as Total_Cost_With_VAT
from (tblInvoiceItems inner join tblProducts on tblProducts.ProductID =
tblInvoiceItems.ProductID) inner join tblVAT on tblVAT.VAT_ID =
tblInvoiceItems.Product_VAT_ID;

Clifford Bass

Hi,

From reading around the subject I can see this is a very tricky one and
there are many opinions on it.

I have decided to go down the route of storing the VAT rate in a table and
referring it to it in the query to do the calculation.

Here's the problem:

There are products that are VAT exempt and products that are not. If they
are not the VAT rate is 15% (currently, provision is built in to change this
in the future)

Customers can order both VAT exempt and non exempt products

I only wish to calculate the VAT on items that are non exempt.

So far i have tblVAT with VAT_ID and VAT_Rate in and Product_VAT_ID in
tblProducts that is linked with the VAT_ID from tblVAT

I am trying to get the query to produce a field that is the VAT amount but
only if the product has the VAT_ID = 2 (VAT_ID = 1 is for exempt items and is
null)

this is the code i have devised from research on the newsgroup which is
definetely not working:

[IIf[tblVAT].VAT_ID=2,([Quantity]*[Actual_Price]*[VAT_Rate])]

NB: Quantity and Actual_Price are fields to distinguish how many of the item
and the net price, in this way i can work out the VAT line by line; this may
have been obvious though


Please can someone help me as I am tearing my hair out!!

Many Thanks

DMWM

 

[Clifford Bass]

Hi,

Added comments. This eliminates any special coding based on a specific
value in the tblVAT table. If you add a new VAT_ID of 3 with a rate of say
10%, you will not need to go in and change all of your statements that
compare the VAT_ID to 2. I have little familarity with VATs, but here is a
thought that might apply: It could also allow for multiple exempt VATs if
you start specifying the reason for the exemption in the VAT table.

1 - Exempt - Religious - 0%
2 - Not Exempt - xxxxxx - 15%
3 - Not Exempt - yyyyyy - 10%
4 - Exempt - Government - 0%

Clifford Bass

 

[Clifford Bass]

Hi Wayne,

I was only following DMWM's example of calculations that did not do the
division by 100. So, yes, if it is stored as a whole number, then you are
right about the division. And to get the total you can, in Access, use the
results of a calculation to the left:

select 1 + 2 as Calculation1, Calculation1 * 3 as Calculation2
from xxx;

I believe this is non-standard SQL, but it simplifies things and cuts
down on potential human errors. If a change is needed in an earlier
calculation, you only have to do it once.

Clifford Bass