Access2000: Advice needed!

[Arvi Laanemets]

Hi


I'm designing a vehicle management database for my relative, and it looks
like I have hit a wall at moment.

I have a form where all route info (Routes table) is registered, like
DateTime, VehicleID, Mileage, etc. Also it's possible to link fueling info
form Expenses table (ExpenseID, ExpenseDate, ArticleID, Amount, Value) to
Routes table (Routes.Tanking-Expenses.ExpenseID)

Additionaly on form are some calculated fields which display speedometers
starting and end mileages, starting amount of fuel, tanked amount of fuel
and the cost of it, spended amount of fuel and end amount of fuel. Spended
amount of fuel depends on mileage, season (table SetUp: SummerStart,
WinterStart - both fields are dates), and on vehicles fuel spending
(liter/100km), which is read from vehicle registering table Vechicles:
VehicleID, ... , FuelInSummer, FuelInWinter, ...
Also there is a table, where some information about vehicle at some fixed
date is stored - Fix: FixID, VehicleID, FixDate, SpeedometerValue,
FuelAmount. Tis information is used as base data for all calculations.

Now, I need also to calculate the monetary value of fuel in tank at start of
route, and at end of it, and the value of spended fuel. The problem is, that
there can be fuel from several tankings in vechicles tank, and prices for
different tankings may differ. The rule must be, than always the oldest fuel
is used as first. But so long I don't have any cleatr idea, how to calculate
this. I'm even not sure, what is better: to calculate all, or to store some
calculated values in Routes table. Against latter speaks, that sometimes
routes may be entered later, or some information (mileage, tanking, monetary
value of tanked fuel, etc.) may be altered later - with storing calculated
values the recalculation of whole table is needed whenever some change is
made.

And at last, I foresee that I need to design some report(s), where also the
monetary value of spended fuel is needed.


Has someone designed something like this? Any ideas are welcome!
Thanks in advance!
Arvi Laanemets

 

[Guest]

First, notice that the whole problem will be much simpler
if you use the Average Value of the tank, instead of the
Oldest Fuel First.

If you use the Average Value of the tank, the whole tank
has only one value, which you update every time you add
fuel.

The total result will be the same, but mileage will be priced
at a running average of fuel cost (which always gradually
trends towards current cost) instead of pricing mileage at
the cost of fuel entered exactly one tank full previous. (Which
is what you get using First In First Out pricing).

First In First Out pricing is used when you are going to
retrospectively revalue costs (or are compelled for other
reasons to account for each tranche separately).

If you don't understand that, use the running average value
of the tank. Add AvgPrice and TankSize fields to your
Vehicle table, and recalculate the AvgPrice everytime you
add fuel:

AvgPrice = (NewPrice * NewAmount) + (AvgPrice * (TankSize - NewAmount))

If you do need to use FIFO pricing, you need a table
with idxAutonumber, VehicleID, date, BoughtAmount,
NewPrice, RemainingAmount.

Each time you add mileage, you need to use VBA to find
the oldest fuel record for that vehicle with remaining
fuel, subtract fuel from that record, adjust the remaining
amount, and find the next record if more fuel is used.

When you add fuel, you simply create a new fuel record
in the fuel table.

To value tank, you sum the fuel records.

(david)