H
hughess7
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
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