Solved Correct table structure for managing inventory

[KitaYama]

I'm trying to understand how FIFO is handled in an Access database.
To learn about FIFO, I've started with a simple structure like this.

Well, I don't know what would the next step be. Do I need another field for controlling FIFO or do I need another table. I've tried to find a sample database but failed to find any.

Edit (based on the feed back I received bellow):
In this case, FIFO is going to control a production line of plastic injection machines. We don't want to keep old ABS material in inventory. The price is not of a concern in this particular case. We need to manage what is brought in first, being sent out of the inventory first. That's all.
If it makes sense. Simple as that. First In, First out. Without price.

Any kind of advice on this is appreciated.

 

[plog]

Sounds kind of backwards. Generally you model a real world situation with Access, you don't use Access to achieve a real world situation. If this is not just an academic exercise, I would look at the real world process you currently have and make Access accommodate that.

If it is just a learning exercise--you need to track items granularly--every single item needs to have its own ID and Date. There would be no quantity stored in a table, but calculated in a query because you are counting all the individual items that are still in inventory. Only with that can you ensure the first item in is the first item out.

If that's too much (which in 95% of the cases it is) you can look at putting items into 'batches' and then using batches to control items at a slightly higher and easier level.

Again though, if this is for something real, you need to start with that real thing and use it to decide how to use Access.

 

[KitaYama]

@plog I'm planning to start an inventory database that handling FIFO is the main concern. At present step I'm free to decide how the structure can be. I also can change the real world situation you're anxious about to follow the correct structure of a normalized database.

So at this step, you can imagine I'm trying to learn best approach for handling FIFO in an inventory database.

Thanks.

 

[CJ_London]

FIFO is about accounting for cost - when an item is removed from stock it takes the cost of that item from the earliest stock in hand.

if you have a qty of 10 at a cost of 1
then add a further qty of 5 at a cost of 1.1
then add a further qty of 9 at a cost of 1.2
then remove 12 items (sold/whatever) the cost is 10*1 + 2*1.1
leaving a stock in hand of 3 at a cost of 1.1 + 9 at 1.2

that is what you need to model.

So at the very least, your stocktake table needs a cost field and a date field. You should not need another table but there are other fields required as well.

If you are familiar with sales and purchase ledgers, the principle is the same, although a bit simpler

you invoice a customer 100.00
you invoice again 200.00
they pay you 150.00, you assign 100.00 to the first invoice and 50.00 to the second

 

[KitaYama]

FIFO is about accounting for cost

With all respects, not always.
FIFO is first in first out. So price MAY not be of a concern (or at least in my case). Imagine a supermarket or a shop inventory. Cheese, bread etc. FIFO is mostly done because of the expiry date, not price.

In my case, FIFO is going to control a production line of plastic injection machines. The expiry date is not like cheese or bread, but we don't want to keep old ABS material in inventory. The price also doesn't count too. We need to manage what is brought in first, being sent out of the inventory first. That's all.
If it makes sense. Simple as that. First In, First out. Without price. If I need date, I will add it too of course.

Maybe I had to explain it in my first help request post.
Sorry for the confusion I've caused.

Now I'm all ear for your advice.

EDIT:
I edited my post to prevent additional confusion.
Thanks.

 

[jdraw]

Have you searched the forum for FIFO related posts and discussion?
You can also check Youtube -- I know there were some tutorials a few years back.

As you mentioned FIFO is often discussed in relation to perishable goods, but CJ is an accountant.
Typical concern is Cost of Goods Sold using FIFO method, but I defer to CJ for info.

 

[KitaYama]

Have you searched the forum for FIFO related posts and discussion?

Yes, three were plenty of posts. Most of them not solved and for no reason the OP was disappeared in the middle of the discussion, the others were something different with this case.
There was also this sample database that 1- returns back to 2006 and 2- I can't understand why the author recreate a table for all stocks every time he opens the form. This causes
a) bloat FE
b) Taking a lot of time to delete FIFO table and fill it every time FIFO form is going to be opened (if the number of parts in stock is a lot) which in our case it is.

You can also check Youtube -- I know there were some tutorials a few years back.

There were 2 FIFO Access database in YouTube. The first takes the whole thing out of stock. For example the author has 10 records for cheese in the inventory and takes it out one by one. It's easy enough. But what about if the amount of taking out is not the whole amount of the first record? For example how about if only 3 kilos of the first record is going to take out of the inventory?

 

[arnelgp]

see if this thread will help you
Solved - FIFO STOCK | Access World Forums (access-programmers.co.uk)

 

[jdraw]

I am attaching a copy of the database that is referenced in
"https://www.access-programmers.co.uk/forums/threads/fifo-batch-allocation.267564/page-3"

Fifo batch allocation

Ok I will show photo of Excel file then you can easily understand.

www.access-programmers.co.uk

I have searched thru my archives and found the attached. This material is from 2014/16.
If it is useful to you, use it as you see fit.
I'm not exactly sure where the original came from. I did find a reference that Khawar made the original, but I see that thread in the forum is not complete. There were no comments in the code when I downloaded it. I have made comments and posted some materials re this database.
There is a tbljComments included that were some note I made at the time.

For FIFO concepts and examples try Youtube "FIFO Tutorial".

 

[KitaYama]

The database in @arnelgp 's link was mostly in Arabic that made it hard to understand for me.
@jdraw 's database : I added a new product (Test) and added two records in purchase table for it. FIFO report doesn't show this product. I think mostly because there was no Sale record for this product (or because the price was 0).

@jdraw 's database had a field "BatchNo" witch I think is for controlling FIFO.
I added it to the table of my sample file attached here.
All I think is necessary is a query to show a list of available products. Can anybody correct the query?
thanks.

 

[CJ_London]

See a lot of responses whilst I've been getting some shuteye

In my case, FIFO is going to control a production line of plastic injection machines. The expiry date is not like cheese or bread, but we don't want to keep old ABS material in inventory.

fair comment - but you still need a date field to identify the old stock. Perhaps that is the 'stocktakeon' field?

This isn't really FIFO since you simply need to find the oldest stock to issue from, so it is more a stock allocation issue. Which means the stock manager needs to find the oldest stock which in turn means you need a stock location field of some sort - which may be a batch number, all depends on how product is stored and identified.

In a supermarket, customers pick from a shelf and take to the checkout, they probably will inspect the best before date and choose the latest item. So for good management the barcode needs to include a batch number or similar identifier (which may be the best before date) so the stock of the specified batch can be adjusted. For stock management a report can then be run to identify those items of stock where the best before date has been reached so they can be removed from the shelves.

 

[KitaYama]

but you still need a date field to identify the old stock.

I added StockTakeOn (Date/Time data type) per your suggestion.

This isn't really FIFO since you simply need to find the oldest stock to issue from,

Not only the oldest, but the oldest remaining one. Which I thought it makes the situation FIFO.
We purchase 1 Ton of ABS-32 . Then another one ton and then another 2 ton. It makes our stock 4000kg (4ton)
Now we use 1300Kg of the first purchase. The next time we want to use this material, we should use the remaining 700kg of the second purchase.

the oldest stock which in turn means you need a stock location field of some sort - which may be a batch number,

I guessed we need some field of this sort, so I added BatchNo field to the database I attached in #10

Now I'm working on a total query to return only the remaining of the oldest purchase. But I'm stuck.

 

[arnelgp]

if you go deeper on the Link i gave you, you will
find out that that is what my code does, Allocating
quantity, to each material according to the earliest date
of purchase.

 

[KitaYama]

if you go deeper on the Link i gave you, you will
find out that that is what my code does, Allocating
quantity, to each material according to the earliest date
of purchase.

Do you mean the database attached to this post?

As I said, the forms and table contain Arabic character and are right to left which makes it very hard for me to understand.
I also found a qryItemAllocation which shows a 1 out (forth line) that I couldn't find a record for that in transaction table.

Can you post the sql for following query based on the my tables in #10?
a query that shows a list of batchNos where ProductFK=1 and the sum of InOutQuantity>0

I really appreciate your help.,

 

[CJ_London]

Not only the oldest, but the oldest remaining one.

I stand corrected, wasn't specific enough. But the definition of FIFO involves cost - just google for a definition. However it is a mute point as your requirement is now clear.

I'm not going to get into a competition with Arnel by providing alternative solutions - it only muddies the water and he generally knows what he is doing

 

[jdraw]

There are queries and comments within the database provided in post #9.

There are only a few records so you can test to follow the logic. The reports all use the related queries, so you can see the various outputs and refine the dates to see what data and logic is used in each report.

You should review the comments and expand the row height (as I discovered) to see all of the text of the comment.

The SQL for each query that supports a report is readily available.

Please note- I did not write the original code. There were NO comments in the original. I reviewed FIFO tutorials and samples, then added comments to all the code as I worked my way through the logic and the cryptic naming (2014).

 

[arnelgp]

i also made a demo (from your db), to show "allocating" withdrawals/issuance.
you should test the "inventory out" form.

 

[KitaYama]

i also made a demo (from your db), to show "allocating" withdrawals/issuance.
you should test the "inventory out" form.

@arnelgp How many times you've saved me?
I really appreciate your help.
Unfortunately I'm in the middle of a very important design and the dead line is there.
I will try to understand how you did the allocation and if there's any question, I'll get back to you.

Once again, I sincerely appreciate your help.

 

[arnelgp]

it's basic idea.
the material with the "earliest" received date will get "exhausted" first.
if it cannot suffice the withdrawal/out quantity, it will deduct the remaining
to the next "earliest" date (same material).

 

[KitaYama]

it's basic idea.
the material with the "earliest" received date will get "exhausted" first.
if it cannot suffice the withdrawal/out quantity, it will deduct the remaining
to the next "earliest" date (same material).

It sounds exactly like what we were trying to do and failed.
Million thanks.