Solved Correct table structure for managing inventory

[KitaYama]

@arnelgp we checked your version of allocation. It's really great but unfortunately it doesn't fit our situation. We'll try to build a solution that works for us. I really appreciate your help and the time you spent on it.
Million thanks.

PS:
In case you're curious how it didn't fit our situation:

• It mostly depends on allocation table (tblInventory_OUT_Alloc). Every time inventory out forms opens it deletes the whole table and rebuild it. In a large scale of inventory not only it's time consuming but slows down the system. We have more than 10,000 items just now and it's increasing.
  
  
  
• Editing Records :
  Open Inventory_IN and register 3 records for the same product. (100 - 100 -100). Now total is 300.
  Open Invenetory_Out form. Register two records for the same product. (100 & 100). The remaining is 100. Now go back to the first record and try to edit the quantity from 100 to 120. Your receive a message that you don't have enough balance.
  So you need to re-allocate your table in OnCurrent event and it adds the heat and pressure to network.
  
  
  
  
  
• Allocation table's data is not the way we expect. In bellow image, I have 3 inputs for part1, each one with quantity=100.
  In Inventory Out I have two records, 80 & 40.
  now look at the tblInventory_Out. It shows allocation for IN_ID 7 (80+40=120). While IN_ID 7 has only 100 capacity.
  There should be 3 records :
  IN_ID ---- Allocation
  7----------80
  7----------20
  8----------20
  
  
  
  
  
  
• Deleting records in Inventory_IN or Inventory_Out :
  Input several records in Inventory_Out and make the balance of a product being 0. Close InventoryOut form. Open it again. Delete all records of the part you just registered. Close the form and open it to reallocate your table. Still InventoryOut form doesn't allow you to enter new records for the same part because tblInventory_Out_Alloc is not empty. It seems closing and opening the form doesn't delete the records in this table. You have to delete them manually.
  
  
  

Again I'm not criticizing your app. It's really great and I saved a copy in my archive for later use. It simply didn't fit our case.
Million thanks my friend.

 

[arnelgp]

Every time inventory out forms opens it deletes the whole table and rebuild it.

NO, it doesn't re-create it everytime.
i have modified the Allocation code, since there is a bad computation.
now it is ok.

Question is Why are you deleting the record.
if you are Receiving something, it must be accompanied by any document.
if the reason you are deleting the item being Received is that it does not exists, then
why enter it in the system anyway.
Also why are you deleting Withdrawals/Out.
when an item being requested means, it is needed for production (like resin, plastic, etc).
I don't believe the materials being request for production is a mistake.
Your PPIC knows the standard of each product.
they know how much quantity of plastic is needed to produce one product.
they know how much time it will take and how many manpower.
I also worked in Plastic manufacturing and they have their standards.
from machine setup time. to material allocation when there are orders.

Deleting record when it is entered 2 days after is a bad idea.
You need to ensure that the items being received with accompanied attachment is correct
before entering to any system. you don't received items In-Transit.

the demo i gave you is as-is. it doesn't have strict validation nor
does not takes into account deletion of record.
you need to code them. when the parent get deleted, you ensure that
the siblings get deleted also.

what i showed you is how does the allocation process works.

anyway if it does not meet your requirement, stop using.
it might cause you more damage than good.

 

[KitaYama]

NO, it doesn't re-create it everytime.

maybe that's the source of problem in editing records.
Thanks for your new version. We will check it.

Question is Why are you deleting the record.
if you are Receiving something, it must be accompanied by any document.
if the reason you are deleting the item being Received is that it does not exists, then
why enter it in the system anyway.

A lot of reasons. We can not shut deleting records (or marking them as deleted) in a database.
Human error is one of the reasons.
Last month we purchased 310 End Mills. Several days later we noticed 3 defective mills in the package. We sent the unusable mills back to the reseller for a replacement. They asked us to wait for 3 weeks because they were out of stock and the next cargo won't arrive soon.
Apparently we had to ask for a refund and change our order to 307 and correct our inventory.

Also why are you deleting Withdrawals/Out.
when an item being requested means, it is needed for production (like resin, plastic, etc).
I don't believe the materials being request for production is a mistake.
Your PPIC knows the standard of each product.
they know how much quantity of plastic is needed to produce one product.

Again a lot of reasons.
As long as a human sits in front of a PC, any unpredicted error is possible. We can not fire someone because she chose KSOM-66 instead of KSOM-68 from the parts combo box.
There's also changes in design at the last moment. The operator of a machine has withdraw Facemill 60, but now Facemill 55 is needed.

Deleting record when it is entered 2 days after is a bad idea.

You may be correct. But unfortunately in our system it's inevitable.

the demo i gave you is as-is. it doesn't have strict validation nor
does not takes into account deletion of record.
you need to code them. when the parent get deleted, you ensure that
the siblings get deleted also.

I know and I really thank you for your time. We're trying to use your sample as the base and build additional parts on it.

Again million thanks from this part of the world.

 

[Pat Hartman]

Correcting the inventory shouldn't be done by deleting items. It should be done with inventory transactions so that every thing that ever came in, is accounted for by another transaction that relieves inventory to dispose of it somehow. Maybe you need to add additional transaction types.

Also FIFO doesn't necessarily mean first in. When you are talking about material that expires, you want to use it in order of expirationDate no matter when you received it. If you received something last week that will expire prior to items received earlier, you want your "FIFO" logic to be working on ExpirationDate rather than ReceivedDate.

In the case of things that don't technically expire, then you would use the ReceivedDate. I have applications that use both methods by sorting first on ExpirationDate then next on ReceivedDate. So, if the ExpirationDate is null, the ReceivedDate will get me the oldest item.

And then there's other things like Stocks or other items where there is some tax consequence you need to account for. In that case, you might want to minimize the tax consequence so you have to calculate the gain and sort on that rather than a date.

 

[KitaYama]

i have modified the Allocation code, since there is a bad computation.
now it is ok.

@arnelgp I couldn't find a modified version of your file.
Can you upload it too please.
Thank you.

 

[arnelgp]

KSOM-66 instead of KSOM-68 from the parts combo box.

my past employers in constructions industry for the past 10 years are either
Japanese (chiyoda) or korean (daewoo/hyundai).
very strict.
there is no error of margin for them.
they always remind, when submitting the work it should be:
check, double check and again check.

i updated the inventory_out to deal with deletions.
i did not touch inventory_in.
this is more complicated than the out.
what do i mean?
if there is already an OUT transaction for a particular material (eg. ksom-66)
and now being used in production and you changed it to ksom-68 in the Receiving.
wait, there is a problem there. what exactly did you Issue?

Last month we purchased 310 End Mills. Several days later we noticed 3 defective mills in the package.

you only enter items/quantity that are Good items. meaning No damage, no Spoilage, no missing count and
no NCR. Quarantined items (for replacement/verification) should not be entered in the final system but
instead in a Separated worksheet until they are deemed good.

 

[KitaYama]

Correcting the inventory shouldn't be done by deleting items. It should be done with inventory transactions so that every thing that ever came in, is accounted for by another transaction that relieves inventory to dispose of it somehow. Maybe you need to add additional transaction types.

Also FIFO doesn't necessarily mean first in. When you are talking about material that expires, you want to use it in order of expirationDate no matter when you received it. If you received something last week that will expire prior to items received earlier, you want your "FIFO" logic to be working on ExpirationDate rather than ReceivedDate.

In the case of things that don't technically expire, then you would use the ReceivedDate. I have applications that use both methods by sorting first on ExpirationDate then next on ReceivedDate. So, if the ExpirationDate is null, the ReceivedDate will get me the oldest item.

And then there's other things like Stocks or other items where there is some tax consequence you need to account for. In that case, you might want to minimize the tax consequence so you have to calculate the gain and sort on that rather than a date.

Thanks for the advice.
Regarding deletion, based on @arnelgp 's warnings and yours, we'll talk it over to change current running method.

In case of expiry date, as I explained in #5 the tools and materials we have, don't have a expiry date. We are solely using ReceivedDate.

 

[gemma-the-husky]

Just a general note . FIFO is to do with costing, not to do directly with inventory quantity, and not to do with inventory management.

If you buy a quantity for £10 each, then another quantity for £12 each, when you sell the stock, or use it in production, then you charge £10 per item until the first quantity is exhausted, then start charging £12. Very difficult to do in practice. It doesn't matter which product you actually use, as the assumption is that you cannot differentiate between old and new inventory. It's the theoretical application that when you use a homogeneous product, you apply the costs based on using the oldest item first. Therefore First in First out.

This isn't the same as inventory identification - ie giving every item a serial number, and selling or using the items in the same order as you manufactured them. That isn't what FIFO means. If you have an identifiable serial number then you a priori do not have a FIFO situation, although you do have a different issue.

FIFO is to do with the cost absorption. Using a method of costing other than FIFO affects your inventory value, and therefore your profit evaluation, and certainly in the UK is contrary to accepted accounting practices to determine how profit should be evaluated, So it becomes a theoretical and practical exercise in managing the quantity and value of homogeneous inventory items.

Of course if you use a different paradigm, say Average Cost or Standard Cost, at the end of the accounting period you can assess the difference between the profit according to FIFO, and the actual profit you recorded and adjust the reported profit if the difference is material.

 

[CJ_London]

FIFO is to do with costing

that point was made in the first few posts to this thread. But still not clear to me whether this is about physical allocation of old stock or simply noting usage which is assigned to the oldest stock regardless of which stock is physically used

 

[Pat Hartman]

I have worked with numerous inventory applications and you are correct FIFO is a financial term. However, when you can identify inventory because it has expiration dates or serial numbers, AND you use those specific parameters to allocate inventory then you use the same method also to cost it. Otherwise, you'd go nuts.

The first post indicated that this was a usage issue rather than a costing issue so my answers were based on that version of "FIFO". I don't recall if there is a usage term for FIFO different from the costing term. All I know is when you are selling securities you get FIFO, LIFO, and tax consequence (high or low) methods of choosing the lots to sell.

 

[gemma-the-husky]

Thanks for the clarification.

Well, in that case, I don't think you can use an ordinary inventory management system if you want a batch control. I have never developed a batch control system, but maybe someone with experience in that field will know how to manage the batches.

Maybe it's easier to tell the stores management to put new stuff to the back of the warehouse store area, and use the oldest stuff first. Once stuff gets really old, you need to scrap it, or make the issuing policy even more specific so it never gets old enough to scrap.

IT systems are great at doing stuff that's easy to do. It's much harder to deal with stuff that isn't easy to do, and this is one of them.

Surely you can't need this level of control applying to every item in inventory.

For the items that really matter, because of sheer value, or health and safety reasons, run a process to compare the inventory with recent inwards deliveries and thereby identify potentially slow moving product. Have a stock check process that tests a sample of products regularly, and physically examines inventory to see if the issuing policies are being managed adequately. Stuff like that.

Don't try to develop an IT solution for something like this. Maybe there's one out there you can buy. Alternatively maybe it's cheaper to find another way to do it.

 

[KitaYama]

Maybe I have to change the title. ( EDIT : I did)
To be honest, I (we) don't know about the policy behind this, but it's a rule where I'm employed. Use The oldest stock.
All we were trying to accomplish was to build a database to manage this rule for us. Google translate suggested FIFO, and that was what I used.
Somebody later suggested it's stock allocating. I even had to lookup Allocating, because I didn't know what it exactly is.
I studied about FIFO before posting, a lot of sites explain it as a part or tool for accounting and costing is a part of FIFO, I already have seen sites that don't mention about cost while discussing FIFO and talk about expiry date. I apologize for using an inappropriate word.

Well, as I explained in my second or third post above, all we want is to take out oldest parts/tools/materials in our inventory for manufacturing purpose. Cost, Tax, or any financial problem is accounting department's problem. They have their own software or tools.
They order tools/materials/parts based on the orders they receive from customers and it's almost more than enough. So if we actually need 1000 pieces of a part, they order 1100..
We (the manufacturing line) receive what is purchased. And have been asked to use the oldest stock.
If it makes sense.

 

[plog]

I apologize for using an inappropriate word.

You prompted 2 pages (and who's to say we're done?) worth of arguing over semantics. That's what people come to the internet for.

We should be thanking you.

 

[CJ_London]

And have been asked to use the oldest stock.
If it makes sense.

I understand that, I may have missed it in the forest of replies but still don’t see how you are physically identifying the oldest stock to use- is it marked with a batch number? Stored in an identifiable bin? Barcode? Something else?

and how do you then verify down the line that that stock was actually used? Stocktake? A system similar to canban?

 

[KitaYama]

how you are physically identifying the oldest stock to use- is it marked with a batch number? Stored in an identifiable bin? Barcode? Something else?

and how do you then verify down the line that that stock was actually used? Stocktake? A system similar to canban?

At present we print a batch number and stick it to the part/material.
An Excel file is used to keep the count And location (A1 to A48).
It's very inconvenient and it's why we're looking for an alternative way.

We have started using an inventory database (following the concepts here.
But as I explained in my original post, We are open to any suggestion Because we don't have any effective method running now.

In case of canban system, I really don't know what it is.

How do we verify that stock was actually used?
We don't have any effective solution yet. The only thing we have is a batch number and a date and a spreadsheet.

 

[Pat Hartman]

I'm really confused now. If batch number is assigned sequentially, then for any given part, sort by batch to find the oldest item. If a batch identifies a quantity >1 received at the same time, you need to manage the count also to exhaust the entire batch before moving on to the next one.

 

[gemma-the-husky]

I think it's not so much a matter of sorting by batch, it's a matter of issuing stuff from stock, and then charging the stores staff with the job of finding the specific batches of stuff that you want them to use in a given production or sales cycle. You surely can't do this with every item of stock. Well you could but it's going to make everything extremely labour intensive and expensive. Even if you can do it, how do you "know" that a particular item of final production included the correct particular parts.

How does an inventory system such as a food production or medicine production cycle, manage to track batch codes and sources of ingredients used in recipe production, so you can recall and trace products that were defective. That's the sort of solution you need. Maybe recipes and products have batch bar code numbers that need to be scanned and verified as part of the provenance, so you know that the Chicken Kievs made on 18th September used identified batches of chicken, identified batches of butter, garlic, and so on. Would this traceabilty go as far as salt, spice, sugar and so on? Surely not. Surely some items are so safe they can be mixed without needing to be traced.

So maybe it's not the production process that stipulates the batch codes. Maybe the process stipulates the Chicken Kiev production run requires 500Kg of chicken, and the stores record the batches of chicken that they have actually issued, with them having the responsibility of using the products in date order, which they manage by the way the new chicken is received into the stores. Something like that. So the warehouse staff record the batches they actually used, and there's some sort of phantom IT process behind the scenes checking for old batches not being used in production, so they can be manually located and used before they go out of date and have to be scrapped.

 

[Pat Hartman]

I think it's not so much a matter of sorting by batch,

Sorting by batch tells the clerks which products to pull. I have applications that do this. It really isn't hard. It is a matter of what information you give the stock clerks. You can tell them to pull productA or you can tell them to pull productA from batch 12.

How does an inventory system such as a food production or medicine production cycle, manage to track batch codes and sources of ingredients used in recipe production, so you can recall and trace products that were defective.

How about the systems that track every single part that goes into making an airplane engine? If a rivet fails causing the engine to fail, they go back to the database to look up the Heat (that's what they call a metallic batch) and then trace that to every other part made from that same heat so they can pull all of those engines for examination. You should feel safer flying in an airplane once you understand how faults are investigated and parts from the same heat, batch, or lot reviewed when a failure is identified.

I don't know exactly how the food industry does it exactly but they seem to be able to track outbreaks of salmonella based on something that ties a bunch of products together They might even have been delivered to other retail outlets. I'm guessing the process is similar to what Pratt & Whitney used. You track the part back to where it came from in inventory and then using the details go outward to find other places parts with the same origin were used.

In the case of a wholesale florist, the process was simplified due to the significantly smaller inventory on hand as well as the rapid turnover. When they get a shipment of roses and put them in the cooler, they are organized so the pickers know how to pick the oldest first. The order process doesn't track the origin or received date of each bunch because if a rose fails, it probably isn't going to make anyone sick or make a plane fall from the sky so there is no need to know that the roses you sent to floristA came from growerB on the 12th of Sept in box 82. They just rely on the competence of the pickers to get it right.

 

[CJ_London]

At present we print a batch number and stick it to the part/material.
An Excel file is used to keep the count And location (A1 to A48).
It's very inconvenient and it's why we're looking for an alternative way.

which is inconvenient? - printing a batch number and sticking it to the part/material? or using the excel file? If the former, then you are talking about a physical process - if you don't want to do it, perhaps you can get your suppliers to do it. Or perhaps they can include a barcode or Q code which your stores personnel can read.

Many years ago I worked for a company that used among other things, chalk and clay powders delivered in 25kg bags on pallets. It did not perish as such but moisture in the air could cause to them to 'clump' and although still useable meant the mixing process took longer. Deliveries occurred most weeks and we carried around two-three weeks stock. To make sure we used the oldest stock first we simply sprayed a big X on each pallet - in red/white/blue (came from the colours if the union jack) depending on the week. We had a big flag on the wall to indicate the 'colour of the day'. So say red. Once the red pallets were all used, the flag was changed to white. And so it went on.

In case of canban system, I really don't know what it is.

Sorry, typo on my part - should be kanban

Kanban - Wikipedia

en.wikipedia.org

 

[KitaYama]

which is inconvenient? - printing a batch number and sticking it to the part/material? or using the excel file?

1- Managing data in an excel file.
2- Traceability with excel.

I'm really out of words. And don't know how to explain it. I did my best to show our situation, but it seems I've failed. Let me give you an example.

We receive a series of materials, parts, tools and keep them in our inventory.
Material: 1000kg of ABS32-12 ---> we give it a batch number 1234
Tool : 12 pcs of FaceMil-65 ------> we give it a batch number 1235
part : 150 pcs of TL-15C----------> we give it a batch number 1236

again we receive a series of materials, parts, tools and keep them in our inventory.
Material: 600kg of ABS32-12 ---> we give it a batch number 1237
Tool : 12 pcs of FaceMil-85 ------> we give it a batch number 1238
part : 10 pcs of TL-15C----------> we give it a batch number 1239

We receive an order for partA.
We use
800kg of 1234
8 pcs of 1235
78 pcs of 1236

We receive an order for partB.
We use
200kg of 1234 + 300kg of 1237
6 pcs of 1238
72 pcs of 1236 + 10 pcs of 1239

again we receive a series of........

and it goes on and on.

1. We have (I think) more than 10,000 items in our inventory.
2. All of these are managed with spread sheets. I don't need to tell you how hard it is.
3. When we want to start on manufacturing PartB, we have to know how much of materials and parts are left over from previous orders to use them first. It was the main problem and google translate told me it's FIFO. I asked for FIFO and was told it's not first in first out because you don't mind about the cost. Well, we want FIRST IN FIRST OUT. If it's not FIFO, give it any name.
4. If we receive a claim about malfunctioning of one of our products, with this situation it's hard to trace back which material or part has been used for manufacturing that exact product. (Using Excel. We hoped Access gives us a better chance.)
   In case of above example if we receive a complain that PartB has a problem 6 months after production, we need to know exactly what has been used. In this case two set of materials : 1234 & 1237 and the date we received them, which part and tools had been used etc. (Traceability)

But it really doesn't matter how we're doing things now. As I said in my first post, we're trying to build a better and more efficient system from scratch and we are ready to throw away current method and follow given solution. The most important rule is what comes first, goes out first.

I really don't know how else I can explain this.
Me and two others who are trying to work on this, will start working on the database.
We try to build what is best for us (based on our knowledge of Access)
We will decide on table structure ourselves and will be back if we hit a wall and need some advice for the queries or correction on the structure of what we have.

For now you can close the thread because we're going in circles. If we need more help, we start a new thread.

I really appreciate all given advices.
Thanks to all who participate in this thread.
I'm sorry for wasting your and other experts' time.