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.

 

[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

 

[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.

 

[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.

 

[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.

 

[gemma-the-husky]

@KitaYama It's not been a waste of time, it's been an interesting discussion, but personally I think you need to consider the physical process, and make the IT solution work with the process. Maybe the process is a) record the materials used in each production batch and b) use older materials first, but a) outranks b), which comes back to @Pat Hartman and @CJ_London mentioning simple manual processes such as colour coding the batches to make a) work and b) relatively easy to achieve without needing any fancy IT process.

The thing is, how will you tell when a product fails which part was actually used in the production. To me it's not really about your IT system recording the usage as officially being batch 1, rather than batch 2, it's about you can tell the difference between an item from batch 1 and an item from batch 2. One way is to turn this around, and let the stores people pick the product you specify, and then they record the batch tag they actually picked. If you think there should still be 100kg of batch 1 left, but the stores have wrongly issued the last 100kg in a different production run, they will never be able to locate the product you want them to use.

Is there a real reason you need to use a particular consignment of item 1234 in a particular order other than for your IT system to "use" the oldest items first.

So it's not a stores process you need, so much as a batch management/control process that works alongside the stores process. For each batch you record accurately the products used in the batch without this being part of the inventory management at all. You requested 8 items of 1238, and the stock record just records 8 items issued, but the batch record for Batch A124/0912 records specific information about the 8 items that were actually picked. You could do this by barcoding the goods inwards for instance, so that the stores clerks can record the items in the batch by simply scanning the bar codes.

I am sure this is what happens when you register a washing machine you purchase. The serial number is on a plate on the washing machine in a sealed box. There is absolutely no way you know which washing machine ended up with the customer until the customer returns his warranty card to tell you. You know the batch of watching machines you made, and gradually you mark them off as the customer identifies them. And if he doesn't bother to tell you because there wasn't a maintenance issue, there's still not an issue. And I'm pretty sure the serial number management process is not part of the production process. It's more of a free standing after sales activity.

 

[KitaYama]

@gemma-the-husky I'll pass your comments in the next meeting. Forgive me for not answering most parts of what you kindly explained because I think I've already cleared what we need. Let me explain only this section.

I am sure this is what happens when you register a washing machine you purchase. The serial number is on a plate on the washing machine in a sealed box. There is absolutely no way you know which washing machine ended up with the customer until the customer returns his warranty card to tell you. You know the batch of watching machines you made, and gradually you mark them off as the customer identifies them. And if he doesn't bother to tell you because there wasn't a maintenance issue, there's still not an issue. And I'm pretty sure the serial number management process is not part of the production process. It's more of a free standing after sales activity.

Have you ever visited a tire line production? The tire that you use on your car? I don't know how things are managed there, but I've visited manufacturing line of Bridgestone here. If you have an accident because of a tire burst, they are able to trace back who, when and where the tire was made. They can exactly give you the name of the staff in charge and trace down to minutes of production.

In our case, if some of our products fails in what they have to do and we receive a complain, we are able to give a report which machine, who and exactly when it's been produced. We also have to address a list of maintenances of the machine with some other data.
I don't know about the washing machines but I know about many production lines that are able to control these data.
I don't go into details because it's going to change this thread into an engineering discussion but for short, every one has a key card. They have to insert it into machines to be able to use them. The machines log their operator and production numbers along with the date, manufacturing serial number and a lot of other data, such as inspection date, package date and even shipping date. It's a perfect system and we don't have any problem in that area.

One more time, we have a complete solution for production. But unfortunately it's not able to trace back the production to our used parts/materials. And I think it's out of this discussion. We were looking for a way to add a database to what we have and the single rule was what comes in goes out first.

As I said, we start working on the database and will be back if we faced a wall.
Thanks for your concern.

 

[KitaYama]

@gemma-the-husky
I'm not allowed to show you everything on a public forum, but it's a part of production of one of our machines. I can give you the exact time of a the produced part. Each color has a meaning. Errors, running time. Program, operator, Did he took a break, Everything that we need to know is there. I hide personal data and product data.

 

[jdraw]

KitaYama

You may find articles on "traceability" of interest. I've often wondered how the food industry can take a product recall and trace the "production/batch" back to the supplier, delivery, farm and even animal. I realize your situation is different, but concepts are similar as Pat described for Pratt and Whitney.
Here's one link