Diesel Tanks FIFO Stock

[haxan786]

I need help designing FIFO Based Stock for Diesel Tanks, There are 2 Diesel Tanks. Fuel is received via Tankers. Each tanker has a different Price and Chemical Properties.
Normally 1 tank will be on Filling and the other will be In-Service. I have a Calculator to correct the received Tanker Volume with Temperature and the Corrected Volume will be considered received fuel. The consumption form will be updated on daily basis.

I have attached the Tanker Received Form in which the circled value is to be considered as Received Volume. Now the issue is how can I check the Stock Flow as per FIFO Method.

 

[CJ_London]

really need to see your table design, relevant relationships, some example data and your expected result from that example data. Make sure your example data reflects real life.

 

[CJ_London]

first off - looks like you have taken an excel approach to your table design which at best will only work with difficulty and at worst, won't work at all.

Also need to understand some of the fields

what does Fuel CV mean?
how are you translating the dips into volumes? Are these tanks rectangular in profile so that 1mm = 5 litres? i.e. area is a consistent regardless of depth, the tank does not have a cylindrical profile?
why are you dipping tank2 2 when you are filling tank1?
where does fuel consumption come from?
Is your FIFO based on per tank? or total storage?

 

[haxan786]

first off - looks like you have taken an excel approach to your table design which at best will only work with difficulty and at worst, won't work at all.

Also need to understand some of the fields

what does Fuel CV mean?
how are you translating the dips into volumes? Are these tanks rectangular in profile so that 1mm = 5 litres? i.e. area is a consistent regardless of depth, the tank does not have a cylindrical profile?
why are you dipping tank2 2 when you are filling tank1?
where does fuel consumption come from?
Is your FIFO based on per tank? or total storage?

yes, you are right.
I was actually trying to make a proper table structure but don't know why I am confused.
As I mentioned earlier, I have Tank correction data. which is more complicated to be discussed here. ( Tank Height (m) x 50.29 = Tank Volume)
Fuel CV or LHV is the Heating Value of any Fuel.
I need Tank Based FIFO because at a time only 1 tank will be used for consumption and another for filling.

Tank 2 dip - just in case If tank-1 is full so we have to change the tank. Then Tank-1 will be in-service and tank-2 will be on filling.

 

[haxan786]

just check the attached report, On the left side, you will see Daily Consumption data with Weighted Average LHV and on the right side you can see FIFO Based allocation.


On ROW 6 you can see fuel is consumed from two diff batches.

 

[Pat Hartman]

Unless a Tank is completely emptied and cleaned before it is filled and the fill comes from a single tanker, your product is always comingled. There is no way to actually separate product once a tank is filled. This is different from the product on the shelf type of inventory keeping where you can actually tie a box to a receive transaction and you can charge box 1 at $2.35 and box 2 at $2.40. Therefore, this is a paper exercise only. That means that regardless of which tank the product is dispensed from, you calculate price based on the receive transactions. So, starting from zero. You have three receive transactions of 100 gallons each for $2.85, $4.50, and $5.10 (this is Biden's America). When you dispense, you decrement the first transaction until it is depleted and charge $2.85, then you decrement the second transaction and deplete it, and then move on to the third. It doesn't matter what storage tank you are drawing from.

If you want to track by tank, then you need to break up the receive transaction by tank. i.e. 25 gallons went into tank 1 and the remaining 75 went into tank 2. In that case, you select the receive transactions by tank and draw down from those rather than from the bulk receipt.

And by "draw down", I am saying create a transaction. Do not attempt to keep this inventory by updating quantity fields. You will never be able to reconcile it.

 

[plog]

Tables tables tables. Forms put data into tables, reports get data out of tables. You don't have the correct table structure all your work on forms and reports is moot.

Complete the Relationship Tool in Access, expand every table so we can see fields and then post a screenshot. Or, post a copy of your database with some sample data so we can see the tables. With what I have seen so far, my gut is telling me you have a ways to go for your tables.