Fuel VAT Calculations

[Guest]

Hi all

I have an expense system that covers several countries. For the UK
transactions we can claim VAT back on vatable items. The user always enters
the gross expense and I have a table that I use as a lookup to work out net
and VAT based on 17.5%.
This all works ok (I divide the gross amount by 1.175 to get the net and
take this amount from gross to get the VAT).

I am struggling with developing the next bit which is on mileage. For those
who use their personal vehicles the VAT we can claim back is a ratio based on
the car value and the cost of the fuel per litre rather than a
straightforward 17.5%.

I have following fields in tables:

Table: tblVehicles

VehicleID
FuelBracket (A,B,C,D or Diesel)

Table: tblFuel

RateID
FuelCost
Bracket (A,B,C,D or Diesel)
Amount

Table: Expenses

ExpID
VehicleID
Miles
FuelCost

I tried linking all these in a query to return the amount (gross vatable
fuel allowance) which then needs 17.5% VAT calculating from it.

It doesn't seem to be working though, the query returns no value in the
Amount field from the fuel table. I am trying to do it this way as I know you
are not supposed to store calculated values in Access. I am wondering though
if I should store a VAT amount and try to calculate it using a dlookup from
the data entry form at the point the expense gets created?

Confused? I am ;-). Can anyone help with the best way to get this working
please?

Thanks in advance for any help.
Sue

 

[Ken Snell \(MVP\)]

Post the query's SQL statement that you've tried. Also, show what
calculations the query is trying to do -- I am not understanding how the
tables' data are being used for your calculation.

 

[Guest]

Thanks Ken. I found out why it was blank - the amount field was not fixed
decimal places eg contained 89.90001957 instead of 89.9 therefore it could
not match in the lookup table (tblfuel). I've corrected this now and all
works ok. My only problem is that I have two VAT fields (as some of the
mileage claimed are on company cars where the VAT reclaimed is always 17.5%)
and if the calculated Vatable field has an amount I need it to overwrite the
original 17.5% VAT value.

SELECT [qry Expenses Monthly 3].*, Format([tblFuel].[Amount]/100,"0.0000")
AS VATFuel, Format([VATFuel]*[Miles],"Fixed") AS GrossVatable,
Format([GrossVatable]/1.175,"Fixed") AS NetVatable,
Format([grossvatable]-[netvatable],"Fixed") AS Vatable
FROM [qry Expenses Monthly 3] LEFT JOIN tblFuel ON ([qry Expenses Monthly
3].FuelBracket = tblFuel.Bracket) AND ([qry Expenses Monthly 3].FuelCost =
tblFuel.FuelCost);

 

[Ken Snell \(MVP\)]

Sorry for the delay in my reply...been busy with work items.

I am not clear about what you want to do with "overwriting" a value in a
field? A select query will not do an overwrite; only an update query will do
that (the query you've posted is a select query). How are you using this
query? is it to support a form, and the user is making changes/entries on
the form?


--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. I found out why it was blank - the amount field was not fixed
decimal places eg contained 89.90001957 instead of 89.9 therefore it could
not match in the lookup table (tblfuel). I've corrected this now and all
works ok. My only problem is that I have two VAT fields (as some of the
mileage claimed are on company cars where the VAT reclaimed is always
17.5%)
and if the calculated Vatable field has an amount I need it to overwrite
the
original 17.5% VAT value.

SELECT [qry Expenses Monthly 3].*, Format([tblFuel].[Amount]/100,"0.0000")
AS VATFuel, Format([VATFuel]*[Miles],"Fixed") AS GrossVatable,
Format([GrossVatable]/1.175,"Fixed") AS NetVatable,
Format([grossvatable]-[netvatable],"Fixed") AS Vatable
FROM [qry Expenses Monthly 3] LEFT JOIN tblFuel ON ([qry Expenses Monthly
3].FuelBracket = tblFuel.Bracket) AND ([qry Expenses Monthly 3].FuelCost =
tblFuel.FuelCost);

Post the query's SQL statement that you've tried. Also, show what
calculations the query is trying to do -- I am not understanding how the
tables' data are being used for your calculation.

 

[Guest]

Sorry for the confusion - bad use of words on my part! This select query will
be the basis of a report. If there is a value in the 2nd VAT field (vatable)
I want the VAT amount on the report to show the contents of the 2nd VAT field
rather than the contents of the first VAT field (Vatamt). I suspect I can do
this though using the IIf statement and check for null. If it is null then
show VATamt and if not show Vatable.

Thanks
Sue

Sorry for the delay in my reply...been busy with work items.

I am not clear about what you want to do with "overwriting" a value in a
field? A select query will not do an overwrite; only an update query will do
that (the query you've posted is a select query). How are you using this
query? is it to support a form, and the user is making changes/entries on
the form?


--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. I found out why it was blank - the amount field was not fixed
decimal places eg contained 89.90001957 instead of 89.9 therefore it could
not match in the lookup table (tblfuel). I've corrected this now and all
works ok. My only problem is that I have two VAT fields (as some of the
mileage claimed are on company cars where the VAT reclaimed is always
17.5%)
and if the calculated Vatable field has an amount I need it to overwrite
the
original 17.5% VAT value.

SELECT [qry Expenses Monthly 3].*, Format([tblFuel].[Amount]/100,"0.0000")
AS VATFuel, Format([VATFuel]*[Miles],"Fixed") AS GrossVatable,
Format([GrossVatable]/1.175,"Fixed") AS NetVatable,
Format([grossvatable]-[netvatable],"Fixed") AS Vatable
FROM [qry Expenses Monthly 3] LEFT JOIN tblFuel ON ([qry Expenses Monthly
3].FuelBracket = tblFuel.Bracket) AND ([qry Expenses Monthly 3].FuelCost =
tblFuel.FuelCost);

Post the query's SQL statement that you've tried. Also, show what
calculations the query is trying to do -- I am not understanding how the
tables' data are being used for your calculation.
--

Ken Snell
<MS ACCESS MVP>



Hi all

I have an expense system that covers several countries. For the UK
transactions we can claim VAT back on vatable items. The user always
enters
the gross expense and I have a table that I use as a lookup to work out
net
and VAT based on 17.5%.
This all works ok (I divide the gross amount by 1.175 to get the net
and
take this amount from gross to get the VAT).

I am struggling with developing the next bit which is on mileage. For
those
who use their personal vehicles the VAT we can claim back is a ratio
based
on
the car value and the cost of the fuel per litre rather than a
straightforward 17.5%.

I have following fields in tables:

Table: tblVehicles

VehicleID
FuelBracket (A,B,C,D or Diesel)

Table: tblFuel

RateID
FuelCost
Bracket (A,B,C,D or Diesel)
Amount

Table: Expenses

ExpID
VehicleID
Miles
FuelCost

I tried linking all these in a query to return the amount (gross
vatable
fuel allowance) which then needs 17.5% VAT calculating from it.

It doesn't seem to be working though, the query returns no value in the
Amount field from the fuel table. I am trying to do it this way as I
know
you
are not supposed to store calculated values in Access. I am wondering
though
if I should store a VAT amount and try to calculate it using a dlookup
from
the data entry form at the point the expense gets created?

Confused? I am ;-). Can anyone help with the best way to get this
working
please?

Thanks in advance for any help.
Sue

 

[Ken Snell \(MVP\)]

On your report, use a textbox with a Control Source expression similar to
this:

=IIf(Len([vatable] & "")>0, [vatable], [Vatamt])

--

Ken Snell
<MS ACCESS MVP>

Sorry for the confusion - bad use of words on my part! This select query
will
be the basis of a report. If there is a value in the 2nd VAT field
(vatable)
I want the VAT amount on the report to show the contents of the 2nd VAT
field
rather than the contents of the first VAT field (Vatamt). I suspect I can
do
this though using the IIf statement and check for null. If it is null then
show VATamt and if not show Vatable.

Thanks
Sue

Sorry for the delay in my reply...been busy with work items.

I am not clear about what you want to do with "overwriting" a value in a
field? A select query will not do an overwrite; only an update query will
do
that (the query you've posted is a select query). How are you using this
query? is it to support a form, and the user is making changes/entries on
the form?


--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. I found out why it was blank - the amount field was not
fixed
decimal places eg contained 89.90001957 instead of 89.9 therefore it
could
not match in the lookup table (tblfuel). I've corrected this now and
all
works ok. My only problem is that I have two VAT fields (as some of the
mileage claimed are on company cars where the VAT reclaimed is always
17.5%)
and if the calculated Vatable field has an amount I need it to
overwrite
the
original 17.5% VAT value.

SELECT [qry Expenses Monthly 3].*,
Format([tblFuel].[Amount]/100,"0.0000")
AS VATFuel, Format([VATFuel]*[Miles],"Fixed") AS GrossVatable,
Format([GrossVatable]/1.175,"Fixed") AS NetVatable,
Format([grossvatable]-[netvatable],"Fixed") AS Vatable
FROM [qry Expenses Monthly 3] LEFT JOIN tblFuel ON ([qry Expenses
Monthly
3].FuelBracket = tblFuel.Bracket) AND ([qry Expenses Monthly
3].FuelCost =
tblFuel.FuelCost);

:

Post the query's SQL statement that you've tried. Also, show what
calculations the query is trying to do -- I am not understanding how
the
tables' data are being used for your calculation.
--

Ken Snell
<MS ACCESS MVP>



Hi all

I have an expense system that covers several countries. For the UK
transactions we can claim VAT back on vatable items. The user always
enters
the gross expense and I have a table that I use as a lookup to work
out
net
and VAT based on 17.5%.
This all works ok (I divide the gross amount by 1.175 to get the net
and
take this amount from gross to get the VAT).

I am struggling with developing the next bit which is on mileage.
For
those
who use their personal vehicles the VAT we can claim back is a ratio
based
on
the car value and the cost of the fuel per litre rather than a
straightforward 17.5%.

I have following fields in tables:

Table: tblVehicles

VehicleID
FuelBracket (A,B,C,D or Diesel)

Table: tblFuel

RateID
FuelCost
Bracket (A,B,C,D or Diesel)
Amount

Table: Expenses

ExpID
VehicleID
Miles
FuelCost

I tried linking all these in a query to return the amount (gross
vatable
fuel allowance) which then needs 17.5% VAT calculating from it.

It doesn't seem to be working though, the query returns no value in
the
Amount field from the fuel table. I am trying to do it this way as I
know
you
are not supposed to store calculated values in Access. I am
wondering
though
if I should store a VAT amount and try to calculate it using a
dlookup
from
the data entry form at the point the expense gets created?

Confused? I am ;-). Can anyone help with the best way to get this
working
please?

Thanks in advance for any help.
Sue