Inventory Management

[LarryE]

I ended up getting busy near the end of the day so sorry for the late reply!

Attached is a trimmed down version so it plays a little nicer (it kept locking up for me). Here are the details:
Row in the context of this file is what I am referring to as Aisle.
Location is what I am calling bay, row and shelf. I just chose to break it down a little more whereas they combined it into one code.
Color is simply the color of the paper label they taped to the material for easy identification (they might have some sort of color coding system with that, but if they do, I am not aware of it)
Customer and job name are self explanatory.
The description is simply what is on that pallet, so in this context, the pallet is a container of those listed items. Sometimes it has multiple of a single item, other times it can have a bunch of various items and it looks like they don't keep track of master cartons, just the overall quantity of an item on said pallet.

Answers to 1 and 2 are above. For 3, yes. A single customers can have many jobs, but a job only belongs to a single customer. For 4, the location is as I stated above, it is the location within the given aisle (which in this excel file, they refer to as row).

1. Correct
2. Correct
3. Incorrect, currently we do have multiple locations but everything is being consolidated into one warehouse (I deleted out the other tabs in the file so it ran smoother and to not cause confusion). So for intent of this particular app, multiple warehouses is not needed.
4 & 5. Customer may have material on multiple pallets in multiple locations within a single warehouse.


I can export excel/csv files from our primary system, but more or less yes, the entry will sadly be manual.


I am not entirely sure either lol. I always just thought of a pallet as a container of multiple items, whether all of the same item or different types of items. I guess we would not need pallet as the location itself would technically be the "container". However, how would you handle items that take up 2 spaces? So if a row in a bay has technically 2 spots, one item could take up both due to size.

Hopefully I answered everyone's questions!

OK that simplifies things. You don't need a separate warehouse table if there is only one.

 

[LarryE]

Based on this new information then, your design might look like this with:

1. Multiple customers
2. Which may have multiple job names
3. Which may have multiple materials associated with each job
4. Which may be stored in multiple locations in the warehouse

Like this:

Correct?

 

[mike60smart]

Hi

Not following :- "Customer A has job Orthopedic One and that job can have material in multiple locations."

My interpretation was that Customer A has job Orthopedic One and that job can have 16 x R2, 86 x EX1 etc.
or a number of different items with varying quantities.

 

[mike60smart]

Correct. Customer A has job Orthopedic One and that job can have material in multiple locations.

If Customer A has Orthopedic One with the following Pallet contents Description:-
(16)-R2, (86)-EX1, (2)-W4-1, (1)-W4-2, (10)-R10, (6)-ETS20 DR M20 (ETS)

Would there ever be a need to find where ETS20 is located??

 

[tmyers]

If Customer A has Orthopedic One with the following Pallet contents Description:-
(16)-R2, (86)-EX1, (2)-W4-1, (1)-W4-2, (10)-R10, (6)-ETS20 DR M20 (ETS)

Would there ever be a need to find where ETS20 is located??

I have fully come to agree with the others and have ignore "pallet". The given location is the "container" not a pallet, so say made up location 2ab3 contains that list of material for Orthopedic One. The customer might only release those (6) ETS20's for shipment, so we would need to know where they are located so pull those from. If they are located in multiple spots, we could potentially pull all of them from both locations, all from only one location, or maybe all from one location and partial from another.

So location 2AB3 contains (16)-R2, (86)-EX1, (2)-W4-1, (1)-W4-2, (10)-R10, (6)-ETS20 DR M20 (ETS) and say location 4BD3 contains just (100) ETS20's. Customer releases 10 for shipment, so we could potentially pull all 10 from 4BD3 or all 6 from 2AB3 and 4 from 4BD3. It would kind of be up to the person who is pulling them to decide, but obviously they should deplete all stock from location before using stock from another. So we would have to know all locations where ETS20 is stored for Customer A's job Orthopedic One.

Hope that clears it up a bit for you Mike.

Larry:
That does look and sound correct yes. I will tinker with it.

 

[mike60smart]

Hi

The following is one way of doing Data Input for Containers Received.

This is showing all data for Container Nr 1

 

[tmyers]

Mike, that is almost to the T what I am trying very, very poorly to describe.

 

[mike60smart]

Mike, that is almost to the T what I am trying very, very poorly to describe.

It is only very roughly put together and there is no validation.

Here with a copy of the Db

 

[tmyers]

Cool. Ill tinker with it and see what can be done.

 

[Pat Hartman]

So for intent of this particular app, multiple warehouses is not needed.

It is very little trouble to include warehouse support in the initial design. It is much harder to add it after the fact if it is ever needed.

 

[tmyers]

It is very little trouble to include warehouse support in the initial design. It is much harder to add it after the fact if it is ever needed.

That is very fair. I will make sure to keep add/keep the ability to handle multiple warehouses.

 

[tmyers]

Mike, I am going over the example you provided now and am confused how you managed to link one of the subforms to a text box control. I have personally never seen that done and when I try to replicate it, it gives me the parameter pop up.

 

[LarryE]

I agree with Pat that you should keep the multiple warehouse option open. Here is a model that will do that. I didn't create a separate table for warehouses, but you can enter the name in the location table anyway. You will also notice that Customer A has 2 Jobs entered. I entered a test Job to show how it looks if a single material type or description is located at two different locations. You can enter how many at each location. In this case, 5 at two locations for 10 total. Just delete that Job for Customer A after you look at it if you wish. I'll leave it up to you to enter the quantities received and dates.

 

[mike60smart]

Mike, I am going over the example you provided now and am confused how you managed to link one of the subforms to a text box control. I have personally never seen that done and when I try to replicate it, it gives me the parameter pop up.

Hi

It is a standard Forms Reference:-

=[Forms]![Name of Main Form]![Name of Subform].[Form]![Name Of Control on Subform]

 

[tmyers]

I see. I did not know you could do that!

 

[stefanrobin]

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?

Hi,

I am looking to create a simple inventory tracking automation. I can’t seem to get it working.

I have an Item in Board 1 that uses 1 unit of stock in a process. Once that process is marked as status complete i want the Inventory item In Board 2 to decrease the total stock amount by 1 unit (Unit amount used in item process of board 1). For example:

When Status changes to Complete in an item of Group 1 in Board 1, decrease number value of Item’s Inventory column value of "Board 2 " in Group 1

I hope my outline above makes sense. Is this possible? any help would be greatly appreciated. I am not looking to spend extra on external apps and integration software

 

[mike60smart]

Hi
It is best that you start a new thread rather than hijacking this thread.