Determine if location is in use (1 Viewer)

[tmyers]

I have an app that keeps track of where material is in a warehouse but am having some difficulty in building a query to determine if a given space is available or is currently in use. Below are how my tables are currently setup for reference.

I am thinking that within tblPallet (i use pallet as my "container" or box if you will) I need a field that would concatenate the location IDs to form the full string (for argument sake, lets say Aisle 1, Bay 1, Shelf 1 would have a full string of 123). If the deleted field is set to true, then the full string would be 123T or 123F is it is false. If the query returns a single record that is 123F, that means that the location is in use and cant be selected on the form, but I am unsure how to practically use that as if I set it to only return that value, then when the location free then the query doesn't return anything, and I wouldn't know what to do with it. The opposite is if I search for all 123 values, if it does return a 123F, I once again don't know how to identify that there is a 123F and how to use it.

 

[The_Doc_Man]

If you can build a query to see where things ARE, look into a "NOT IN" sub-query as a way to determine where things aren't.

That means you might need to look up the ideas of a SubQuery and the "NOT IN" qualifier of WHERE clauses.

 

[tmyers]

I did not know that was a thing (the NOT IN). I will read up on it and try putting it to use as that is likely what is causing me to be stuck.

 

[Pat Hartman]

So, PalletID is the identifier for a location and Bay, Aisle, shelf are directions to that location? But the combination of Bay, Aisle, and Shelf is not unique so more than one PalletID can be allocated?

What is the piece that can't be subdivided? Pallet is a pretty specific term and it evokes a picture of goods wrapped in plastic and stored on a wooden skid that is moved around by forklift.

Bin is a more common name for the indivisible piece of a warehouse. Some bins might be large enough to hold a pallet. Others might hold multiple items of the same type.

Also, although a pallet is more likely to be a bunch of one type of item, it can be multiple items all wrapped together for delivery to a single location.

I don't like the way you are using the word pallet but if that is what your users want, then that is what you should use. Pallet is more of a transportation word than one used with final storage in a warehouse.

 

[strive4peace]

hi @tmyers

agree with @Pat Hartman's comments. Picking good names is one of the hardest things to do!

It seems that you're missing a Locations table. It would be kind of like your Pallet table except it wouldn't care what is in the space, just that the space can be used. It also seems that sometimes material might be big and take more space than other material.

If you want to list all available spaces, another option is to make a Totals query on tblPallet with Deleted records, and other query that shows all available Aisle, Bay, Shelf.

Then another query on top of what's available that isn't in the deleted query?

 

[Pat Hartman]

You could call the indivisible piece "Location" as Crystal has. You might also need a type that will define height and width if you application has to chose a bin or location based on the characteristics of the item being stored. A warehouse system such as what Amazon might use gets pretty complex.

 

[tmyers]

Naming is quite hard! I went with pallet as it made sense to me and helped wrap my head around the idea that it is a sort of container that holds many items (sometimes many of 1 item or many different items with different amounts) but Bin would honestly work and I am not so far in that I couldn't rename it to such.

I don't think it is needed, but just to add some clarification in case it is, the hierarchy goes Aisle > Bay > Shelf (Aisle 1, Bay C, Shelf 3) which when combined would make a location code of 1C3.

For this instance, PalletID is the unique identified (autonumber) that can be populated with various material (in tblMaterial), the combination of Aisle, Bay and Shelf is not unique as a PalletID can be assigned to the location, removed (deleted changed to true) and a new PalletID can have the same code that a previous ID had, hence my predicament in trying to identify which combination of aisle, bay, and shelf are actively in use.

I could likely figure it out if I had a master location table, but I was shown a while back that breaking each piece into its own table was easier as the amount of combinations can get pretty high (even more so when I add in spot 1-3 for a sub section of shelf).

I am multitasking a bunch of things currently, so I apologize if my thought process is disjointed.

 

[Pat Hartman]

Neither Crystal nor I like "pallet" in this context. What do your users call the "cell" that is the unique address of where the items are stored.

In a warehouse, a "bin" or "location" would only hold like items. Inventory would never be mixed. You won't find both bolts and washers for example in the same bin even though they are used together. As I said earlier. A "pallet" is what you pack things on to load them onto a transport vehicle and it can be single or mixed items.

I like "bin" rather than "location" because "location" has other meanings. I've never seen "bin" used for anything except inventory management.

Don't forget the unique index on Aisle + Bay + Shelf. You need to create this index using the indexes dialog. You can only create single field indexes using design view of the table.

 

[tmyers]

I am making the change to Bin rather than pallet. My mind was stuck on that word so it was what I had used, but considering the app is quite literally called "Bin Locations", Bin makes more sense.

However I think I am finally following what you are explaining to me (I think). Rather using the current setup I have, change tblPallet to tblLocation and populate the location itself, not an arbitrary middle man container which I think would solve the problem I have with trying to figure out what location is in use already.

 

[Pat Hartman]

Don't call the table tblLocation if you are going to call the ID Bin? Call the table tblBin. It helps to be consistent. Or you could call the table tblBinLocation. Now when you add something, you add it to a BinID and when you remove something, you remove it from a BinID. tblBinLocation is a good name for the table because you are defining the location of a bin by having the Aisle, Bay, and Shelf attributes. And using BinID as the PK doesn't conflict with that concept. I would use an autonumber as the PK rather than "1C3". This would save a conversion down the line if there was a reorganization. Although technically, storing "1C3" as Bin duplicates the contents of Aisle, Bay, and Shelf, It isn't terrible since rows would not be added to tblLocation except via your form so you can ensure that there is no conflict. I look at is as a user-friendly version of the PK. However, if your user is OK with always using three controls to enter the Bin, that would be the ideal solution. Try to get the user to do it that way and only store the concatenated value if you can't convince the user to enter the three parts separately.

I alluded to the problem of "what location is in use already" earlier. Do you need to know the limit of what a Bin can hold. Are there Bins of different sizes? Do all "bins" hold a pallet? Are you trying to figure out if there is room in a "bin" to hold what you want to add to it?

 

[strive4peace]

@tmyers, on the different warehouses ... how many warehouses are there? If lots, do you still use same IDs for Aisle, Bay, and Shelf, or are they specific for a warehouse? In other words, are they numbered so you don't have to know dimensions for each warehouse or will you need to know that too? I can imagine that Bins might need to be dynamic based on bin type (actual bin holding parts or a pallet of stuff) so I imagine they might change depending on how the space is being used.

I see the beginning of a table for receiving but its called Receiver so need to know how this inventory is being changed and packaged -- obviously this needs to be expanded and probably needs a related table. Don't you also need a way to move stuff like for selling and to unpack pallets? And a way to distribute inventory other than marking a pallet as Deleted (good start but I suspect this is just the tip of the iceburg)

What kind of parts/products are these?

Parts to make into finished products? And then possibly combine for packaging?

Are you a manufacturer, a wholesaler, a retailer?

Lots to think about. Rest assured though, Access is a good place to figure this out!

 

[tmyers]

With both of your advice, I took a step back and reevaluated what I had currently built and made some changes.
tblPallet has become tblBinLocation and fields adjusted accordingly.

tblWarehouse (which is just out of view to the left) has been better implemented, although it was more for future proofing then current use as the only use I see for it currently is for sub-warehouses within our main warehouse, but better to have it in place now rather than the deal with the headache of adding it later.

Per your observation Crystal, tblReceiver was just a table that held dates and notes. I had not designed it to do anything else as the actual receiving function is handled by my companies software. When I took the snip, it was disconnected and no longer in use as I didn't feel it was needed since the info could be found in our other software but have not yet decided on fully removing and deleting it just yet.

As for figuring out the original problem of knowing what bins are currently in use and which are not, I thinking about keeping the controls on the form separate as to the selection of aisle, bay and shelf (with possibly space added as well) and storing the concatenated value only rather then the various bits. I am thinking of a control that will update as the user works their way down the hierarchy, displaying what locations are in use (or not in use, which would likely be better visually).

To just provide a reference in what I am basing work off of, attached is a super trimmed down Excel file that my work currently uses (the working copy is 2kish lines long and worked in by multiple people). We typically dont care about the actual product catalog number, just the type designation for that given project, which is why my material table is super slim. If exact detail is needed, my companies primary software would be used instead.

Hopefully I provided clarity and am working in the direction you two are trying to lead me in

 

[Pat Hartman]

Using the mushed location for searching is a mistake because strings comprised of numbers and letters sort as strings, NOT as numbers. You are looking at too small a sample of bins to see this problem. For example 10A1 will sort before 2A1. And 30B20 is going to sort before 30B4. Is that going to make sense to the users?

You need to work with the three fields separately if you want to pick a location efficiently.

I don't understand why you posted a spreadsheet. I thought we were working on a database.

 

[tmyers]

I just wanted to provide an example of the file I am basing the database off of is all, but in hindsight, it wasn't needed and muddied the waters more. I apologize.

 

[Pat Hartman]

Do you understand what I said about sorting a string that contains "meaningful" numeric parts? THAT is the reason for not using the mushed field for searching.

 

[tmyers]

Yup! I am glad you said that because I wouldn't have realized it until it was too late.

 

[Pat Hartman]

You're welcome