Inventory Management

[tmyers]

I just want to brainstorm a bit before I begin full on developing something and see what others think. My thought process might end up a little disjointed so bear with me.

Background:
My work has a "bill and hold" area where the material is already invoiced and paid for, therefor it is customer property and is unable to be handled by our companies system any longer. To keep track of this material, we currently use a shared Excel file where all information is kept and updated by upwards of 5 people and lately they have had problems with several trying to access it at the same time and about a week ago became corrupted and was nearly lost.

Mission:
I would like to create an Access app that when material is received, all relevant information is entered, such as PO, catalog numbers, quantity, any damage, attach bill of lading and how many packages and/or pallets and other odds and ends. From there it would prompt them to enter/select bin locations based on number of pallets/boxes entered from a list of currently open locations (aisle, bay, row). With this, I do already see a slight problem as sometimes a single pallet could take up 2 spaces which would be the entire bay. Once all of this is entered and the locations are verified to be open, labels could be created to attach to the material (no barcodes, just visual labels).

Material would be kept under master project names, so when the first shipment arrives, the project would have to be created before the material could be entered. Once a job is complete and there are no more items stored under a project, it would ask if the project is complete and close the project if it is.

When material needs shipped, you will be able to open a project and see all the material that is under said project and select it and enter a quantity to ship that is up to the amount in that location, if you exceed the amount, you must choose another location to pull from. If the quantity in the location is not fully depleted, a new label would be created that would show the remaining quantity. If the entire amount is used, that bin location would become available for use yet again.

Based my thought process so far, how complicated would this be? Does anyone foresee hurdles to this?

 

[mike60smart]

Can you upload a copy of the current Excel file?

 

[tmyers]

Sadly I cannot as I do not have it (have asked to be sent a link to it though). When I took a look at it the other day, its a single sheet file with a massive list of material currently on hand. My first thought when looking at it was that it was very prone to user error as we can have over a thousand pallets of various material that is constantly coming and going.

Ill go ask our warehouse manager if he can send that to me today and I should be able to share a copy of it.

 

[Minty]

Although this sounds a bit backwards, I would solve your immediate problem first (the already sold inventory) before attempting to create a full-on inventory system. That bit sounds relatively simple unless I'm missing something.

Inventory systems aren't trivial to design and build even with some experience of the issues at hand.
Multiple locations etc. need careful planning to cover all eventualities.

 

[tmyers]

I understand. One reason they asked me to start working on something is because we are moving in a few months so everything will leave our current building and go to the new, much larger one and they thought it would be the perfect time to implement something a little more robust than an Excel sheet.

 

[Minty]

If you have a few months then I would crack on and have a go.
It will take you some considerable time I assure you if you haven't done it before.

Some of the basics to consider just to manage the items in and out;
You'll want a transaction table to manage things in and out of the locations. You'll want a transaction type to indicate if it was In or Out or a Stock check.
In addition to forms for receipts and sales(despatches), you'll need forms to allow for stock movements between locations, and location management.

Record timestamps with all the transaction records (Use Now()) - imagine you do a stock-take on something at 11:31 Am, then move stock at 13.54 - you need to differentiate the time of the transactions to ensure your current stock calculation is accurate.

All the other stuff PO's, damage comments, bills of landing etc. are essentially just fluff around the core inventory process.
Make sure that the whole thing works on paper before committing your design to tables and then forms.

 

[tmyers]

Would the Inventory template be a good thing to review per chance?

 

[mike60smart]

Would the Inventory template be a good thing to review per chance?

This is a good place to start:-
http://allenbrowne.com/AppInventory.html

 

[The_Doc_Man]

The most common inventory systems use a full transaction method on a part-by-part basis. In such a system, you track everything via summation queries on an individual stock item basis. Each transaction either adds to available stock or removes that availability. A transaction would be something like "Supplier delivery", "customer sale", "lost to shrinkage", "customer return", "Sight inventory adjustment", etc. Negative inventory sums would be indicative of "back-order" status. Having a very large number of large items on-hand might be indicative that a discount sale would be in order, and so on. Then based on the transaction code, you would add stock to inventory ("delivery", "return") or remove ("sale", "shrinkage"). Just have the quantity and use the code to define the sign as + or - when performing the sum. There are lots of fine points in this but that is the simplest summary.

Because you have a sales component, you will have invoices. Normally, once a sale is made, you merely keep the invoices for record-keeping. However, the added wrinkle of holding for pickup simply means you have to track a little bit more on the invoice, perhaps a status code that indicates "assembling", "complete on dock", "customer has picked up", and other statuses as might be needed. Because the invoice will have detail-item codes associated with it, you don't need to continue to track the items at that point because the invoice as a whole will be tracked. That might simplify the single-item transactions by tracking the bundle - once it exists.

Since you probably expect a repeat business, I might have a customer-to-invoice junction table that has status codes and, since you are dealing with potential multiple locations, that junction entry would also include the location code for the customer bundle.

 

[tmyers]

To clarify, all the material is bought and paid for prior to it ever being entered into this. This is essentially to keep track of a where things are in a storage warehouse until the customer releases it for shipment, then removing it from the system to make the space available again.

I might be overcomplicating it (as I tend to) but all I am really after is just a nice system for when material comes in and is put away, we can look and see where its at or even search via locations and see what is in each location. Best comparison I can really think of is a library, but rather than books, were keeping track of pallets.

I would love if my work would just invest in some sort of QR code based system.

 

[arnelgp]

you might also need a "mapping" of your warehouse.
so you can pinpoint the "whereabout" (physically location on the warehouse) of each customer's product.
everytime there is a product "preparation" you check the map for available floor area where to put it for staging purpose.
when the product is withdrawn, you clear that area.

 

[June7]

So basically "asset tracking" - MS has sample template for that https://support.microsoft.com/en-us...template-6e99a32c-fbda-48a2-89b0-ef0d68b381ec. There is also a template for books - LendingLibrary.

Downloading templates used to be so easy right from Access app but I am still using Access 2010 and links no longer work.

 

[Minty]

I would love if my work would just invest in some sort of QR code based system.

A simple bar code reader and plain 2d bar codes would suffice for this I suspect and wouldn't cost much.

You can have a table of location ID codes that relate to an Aisle, Rack and Shelf location.
The Unique ID would be the bar code value/sticker. You display a concatenated version of the Aisle Rack Shelf for easy human consumption.

All pretty simple, and as stated you have a transaction table to book the stuff in, move it around and book it out.

Add a status field for "Sold Date", and you can easily see where the sold stuff is until it's collected, which would be another transaction removing it from stock permanently.

 

[Pat Hartman]

If you never break up a pallet for shipping, you can keep the different units of measurement because you are shipping "eaches". 1 pallet, 1 box. If you might break up a pallet or box and ship items from it, then you need to define conversions. 1 pallet = 20 "eaches". If pallets do not always hold the same quantity of things, then when you log a pallet, you need to specify the number of items. Then you can ship 1 pallet or 5 items.

Supporting this becomes very confusing as I discovered when creating a similar application for a wholesale florist. So we bought boxes but we sold bunches. For each different type of flower, the box might hold a different number of bunches so this would be defined when you defined a flower. So our inventory was in two parts. a count of boxes and a count of bunches. When a box was opened it was subtracted from the box count and the appropriate number of bunches was added to the bunch count. Then we sold the bunches. So the sum of (boxes * bunches) + bunches = total inventory for that item in bunches.

I'm assuming your company already has an inventory application for its unsold items. Is there some reason you cannot use that application to manage this inventory? You would use new productID's and perhaps a new "warehouse" if the existing app could support multiple physical locations. Much as I love to develop new apps, there are times when we can actually use the existing app if we think outside of the box.

 

[LarryE]

This seems very simple and straight forward. I'll put a quick model together and attach the file a little later on today.
A simple design like this. Of course you add your own fields as required. I just took a guess for now.

 

[Minty]

How would you know which locations were available without a locations table?

From the description in the OP's 1st post, I think the pallets can get split down, so you need qty's of material and to account for partial shipping.
If that isn't the case it does become much simpler.

 

[LarryE]

Yes, of course he could add a separate location table and quantities if necessary. If that is the case, then quantities go in the Materials table, but if they are split onto different pallets, then a separate pallet quantity field would go in the Warehouse table with the original material quantity staying in the Materials table. I suppose he will need to make that decision. At the very least he has multiple customers who may have multiple materials stored, which may be stored in multiple pallet locations. Also, I wonder if material could be moved from one pallet to another for some reason. Just guessing though. If pallet numbers are used, then I included that.

 

[tmyers]

I'm assuming your company already has an inventory application for its unsold items. Is there some reason you cannot use that application to manage this inventory? You would use new productID's and perhaps a new "warehouse" if the existing app could support multiple physical locations. Much as I love to develop new apps, there are times when we can actually use the existing app if we think outside of the box.

You are correct but the reason we cant use our companies system to keep track of it is because the material has already been sold AND invoiced, so the order is essentially closed which gives us no means to keep track of it beyond that. Our IT is working on integrating some kind of system for it, but we have been told it is likely years away.

How would you know which locations were available without a locations table?

From the description in the OP's 1st post, I think the pallets can get split down, so you need qty's of material and to account for partial shipping.
If that isn't the case it does become much simpler.

Also correct. To what both you and Pat had mentioned, the sizing of shipments can get really weird. Sometimes a pallet will contain a single, very large item, or a pallet could have multiple boxes that also have multiple items in each box that. So we could ship a pallet, a master carton off a pallet, or individual items from a master carton off a pallet. The later is rare though as it tends to create tons of problems keeping track of open boxes.

 

[LarryE]

If you have multiple quantities and need to keep track of individual quantities on each pallet as well as pallet locations. You might design it something like this:

With QuantityMeasurement meaning numbers of items or number of pounds or number of individual packages etc. So you could have split quantities of materials on different pallets in different warehouse locations. But it all comes together in the TblWarehouse table anyway.

 

[Pat Hartman]

Why don't you keep the inventory as items rather than pallets? Isn't that confusing the issue? In the real world, a pallet could contain different types of items.

You should be able to tie this inventory back to the order and you won't be able to if pallets can contain different types of items.