Variable VAT by product and customer

[DMWM]

Hi,

Im very stuck on this. I have an invoice report which shows customer details
and a subreport that shows the products they have ordered. the sql for each
looks like this:

Invoice

SELECT tblCustomer.Account_Contact, tblCustomer.Company_Name,
tblCustomer.Account_Contact_Number, tblCustomer.Invoice_Address1,
tblCustomer.Invoice_Address2, tblCustomer.Invoice_Address3,
tblCustomer.Invoice_Address4, tblCustomer.Invoice_Postcode, tblJob.Job_ID,
tblJob.Purchase_Order_Number, tblJob.Invoiced_Date, tblJob.Payment_Date,
tblJob.Order_Notes, tblCustomer.Customer_ID
FROM tblCustomer INNER JOIN tblJob ON tblCustomer.Customer_ID =
tblJob.Customer_ID;

Subreport - Products

SELECT tblProducts_Quoted.Quantity, tblProducts_Quoted.Item_Specifics,
tblProducts.Product_Name, tblProducts.Product_Description,
tblProducts.Product_Size, tblProducts.Product_Colour,
tblProducts_Quoted.Job_ID, [Actual_Price]*[Quantity]*([tblVAT.VAT_Rate]/100)
AS VAT, [Actual_Price]*[Quantity] AS Price
FROM (tblVAT INNER JOIN tblProducts ON tblVAT.VAT_ID =
tblProducts.Product_VAT_ID) INNER JOIN tblProducts_Quoted ON
tblProducts.Product_ID = tblProducts_Quoted.Product_ID;

The totals for VAT and Price are in the report footer.

What i need to do is make sure that the VAT only calculates for customers
that are not VAT exempt. i.e. when they are i want the report footer total or
the VAT part of the subquery to read 0. I've tried linking the customer vat
rate in via the underlying query but it has not seemed to work as the
subquery then shows all products ordered by all customers on each invoice.

Any pointers would be great thankyou.

DMWM

 

[Mark Andrews]

I do something similar for indicating which invoice lines are taxable. How
I approach mine is to:
1. store the tax rate in the Invoice table
2. On each invoiceline inicate if that line is taxable
3. calculate by this in the query
InvoiceTaxTotal:
Sum(nz([tblInvoice].[TaxPercent]/100)*IIf([tblInvoiceLine].[Taxable]="T",[tblInvoiceLine].[Qty]*[tblInvoiceLine].[Rate],0))

It's not your exact senerio but I hope it helps, basically get that iif() in
the right place to conditionally calculate the amount.

If you are still stuck send me an email with an example of how VAT is
applied and I'll try and help more. See my website for contact info (I
don't check the newsgroup every day).

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com

 

[DMWM]

Hi Ken,

Thankyou for your explanation of VAT, it has most definitely made me
understand the complexities of it; it's very good.

I am, however, experiencing a bit of a problem. The SQL string you wrote for
me does perform a calculation but it is not the correct one. For example, on
a single product line it calculated that 6 products charged at £20 wouls have
VAT of £2.40. When working this out manually using 15% it should be £18, i
cannot for the life of me work out why though. Here's the code just in case:

VAT:
[tblProducts_Quoted]![Quantity]*[tblProducts_Quoted]![Actual_Price]*([tblProducts_Quoted]![VAT Rate]/100)*IIf ([tblCustomer].[VAT_Exempt],0,1)

I added a lookup in the subform of frmJob so that when products are selected
(entered into tblProducts_Quoted) a VAT rate can be selected (this is looked
up from tblVAT) yet it doesnt make any sense to me. Can you shed any light on
a solution please?

DMWM

I think your model is incorrect here. On the basis of the query which you
are using as the Products subreport's RecordSource you appear currently to be
doing as follows:

The VAT applicable to each invoice line in the query is computed by
multiplying the Actual Price by the Quantity by the VAT rate for the product
in the tblVAT table. The VAT rate being applied is therefore functionally
dependent on the key of the tblProducts table. However, the rate of VAT can
vary over time, as we have seen with the UK government's action on VAT in
response to the current economic downturn, reducing the standard rate from 17.
5% to 15%. What this would mean in your case is that when the rate of VAT on
a product changes your prior invoices would now reflect the current rate of
VAT, not the rate of VAT which applied at the time the invoice was raised, so
the earlier invoice records would be incorrect.

What should be the case of course is that each invoice line record should
reflect the rate of VAT at the time the invoice was raised. This means the
rate of VAT is, like price of a product, functionally dependent of the key
not only of the tblProducts table, but also on the key of the
tblProducts_Quoted table. The former represents the *current* rate of VAT,
the latter the rate of VAT at the time the invoice was raised. So, like the
price of a product, there needs to be a VAT rate column in tblProducts_Quoted
as well as the VAT_ID column in tblProducts.

A value would be inserted into the VAT rate column in tblProducts_Quoted by
looking up the current VAT rate for the product from the tblVAT table and
assigning the value to the column in tblProducts_Quoted. The value would
then remain unchanged if the rate of VAT for the product should change. I
imagine that you may well be doing something similar for the unit price per
product using a routine similar to that in the AfterUpdate event procedure of
the ProductID control in the sample Northwind database which comes with
Access.

Turning to your question, you can handle VAT exempt customers by including
the tblCustomer and tblJob tables in the subreports underlying query. You
don't have to return any columns from the tables in the query, however. Lets
assume you have a Boolean (Yes/No) column VAT_Exempt in tblCustomer, but
including this table in the subreport's query (and the tblJob table to
provide the join with tblProducts_Quoted) you can then amend the expression
to compute the VAT to:

tblProducts_Quoted.[Actual_Price] * tblProducts_Quoted.[Quantity] *
(tblProducts_Quoted.[VAT_Rate]/100) * IIf(tblCustomer.[VAT_Exempt],0,1)

The IIf function will return zero if the customer is VAT exempt, so the
expression will evaluate to zero by virtue of the Price*Quantity*VAT being
multiplied by zero; otherwise it will evaluate to Price*Quantity*VAT by
virtue of being multiplied by 1. If the Boolean column in tblCustomer is
True for VAT paying customers rather than VAT exempt customers you'd simply
transpose the 0 and 1 in the IIf function.

One further point worth mentioning is that, if you are showing the VAT per
invoice line in the subreport, you might notice that sometimes the sum of the
VAT in the footer differs slightly from what you would get if you manually
summed the VAT amounts as shown in the invoice lines. This is not an error;
it merely reflects the fact that the amounts shown in each invoice line are
rounded to two decimal places, but the underlying values are to a greater
precision. The sum is the sum of the more precise underlying values, which
is then rounded to two decimal places. This is correct as it is suppressing
cumulative rounding errors, as its designed to do, and any slight apparent
discrepancy reflects this suppression of the rounding errors. Normally VAT
would not be shown per invoice line, only as a sum, so any apparent
discrepancy would not be seen. In fact if you examine invoices from
suppliers VAT is often computed not on each line but on the sum of the net
amounts, grouping items by their VAT rate so that the VAT can be computed
(and shown) separately for each group of differently rated items. I have an
invoice in front of me which does this, with three items summed and then VAT
at 15% applied to the sum for these, followed by 1 zero-rated item. In view
of the higher precision of the underlying amounts with currency data in
Access than the amounts shown, however, the final result should be the same
whichever way its done.

Ken Sheridan
Stafford, England

Hi,

Im very stuck on this. I have an invoice report which shows customer details
and a subreport that shows the products they have ordered. the sql for each
looks like this:

Invoice

SELECT tblCustomer.Account_Contact, tblCustomer.Company_Name,
tblCustomer.Account_Contact_Number, tblCustomer.Invoice_Address1,
tblCustomer.Invoice_Address2, tblCustomer.Invoice_Address3,
tblCustomer.Invoice_Address4, tblCustomer.Invoice_Postcode, tblJob.Job_ID,
tblJob.Purchase_Order_Number, tblJob.Invoiced_Date, tblJob.Payment_Date,
tblJob.Order_Notes, tblCustomer.Customer_ID
FROM tblCustomer INNER JOIN tblJob ON tblCustomer.Customer_ID =
tblJob.Customer_ID;

Subreport - Products

SELECT tblProducts_Quoted.Quantity, tblProducts_Quoted.Item_Specifics,
tblProducts.Product_Name, tblProducts.Product_Description,
tblProducts.Product_Size, tblProducts.Product_Colour,
tblProducts_Quoted.Job_ID, [Actual_Price]*[Quantity]*([tblVAT.VAT_Rate]/100)
AS VAT, [Actual_Price]*[Quantity] AS Price
FROM (tblVAT INNER JOIN tblProducts ON tblVAT.VAT_ID =
tblProducts.Product_VAT_ID) INNER JOIN tblProducts_Quoted ON
tblProducts.Product_ID = tblProducts_Quoted.Product_ID;

The totals for VAT and Price are in the report footer.

What i need to do is make sure that the VAT only calculates for customers
that are not VAT exempt. i.e. when they are i want the report footer total or
the VAT part of the subquery to read 0. I've tried linking the customer vat
rate in via the underlying query but it has not seemed to work as the
subquery then shows all products ordered by all customers on each invoice.

Any pointers would be great thankyou.

DMWM