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!