How to handle part location infomation

[mjdemaris]

I still can't seem to nail this down. I have 3 storage areas, and each one has bin locations.
On a form, I would like to display to the user the area and bin in a combo box or list box.

Area1 Bin1
Area2 Bin2

I've tried lots of different ways including using a table as a junction amongst Part, Area, Bin and creating an Index with these fields.

tblJunction
ID
PartID
AreaID
BinID

This is the one thing that is really hanging me up, now. :banghead: Some users will only be able to view this info, others need to be able to change it.

Also tried tblLocations
ID
AreaID
BinName

Also used various queries...

Thoughts?

Mike

 

[James Deckert]

A junction table is to make a many to many relationship.
Can each area have more than one bin, or one only?
Can each bin have more than one location, or one only?

 

[smig]

I don't realy understand what you try to get.
Do you want one combo to update the list in the other one ? Is this what you want ?

 

[jdraw]

Can you describe what you are trying to do in plain English --no Access/database jargon?
Forget junction at the moment.
Can you show us an example of what you have and what you need?

 

[Libre]

It's hard to see what you're driving at.
I can't see any problem here.
What did you say the problem was?

One thing, I would have two separate combo or list boxes - one for Area and one for Bin - rather than trying to combine these two data fields.
But what you're trying to accomplish looks to me like a (pick one): walk in the park; piece of cake.

I can't divine what's hanging you up here.

 

[gemma-the-husky]

the mistake is to try to have both the bin and area in the same entity.

what you do is store the part in a bin, and forget about the area.
the bin is then related to the relevant area in the bin record


so

tblAreas (areaID, description)
tblBins (BinID, description, areaID)
tblParts (PartId, description)
tblStockRecord(PartId, BinId, Qty, Date)


the quantity of parts is sum of qty in tblstockrecord
the quantity of parts by bin is sum of qty by binid in tblstockrecord

if you need the area, then join the area table to the query on the areaID fields.

----

if you want to go the other way, then you need cascading combo boxes.

pick an area from an area combo box
requery the bin combobox based on the selected area
now pick the bin from the bin combobbox

 

[jdraw]

I agree with Dave ---it seems that Area is irrelevant. You have Bins to store Parts, and don't appear to use Area.
Perhaps you should consider naming the Bins to include Area if it's important to you.

AreaABin1, AreaABin2??

If we have misunderstood your intent, then maybe some examples will clarify your needs.

 

[mjdemaris]

Ok. So I have three areas to store parts, because of the setup of the buildings and departments

I define Bin as the specific aisle, section, and shelf that a part is on.

Each Area has many Bins. A Bin, 1A1 for example, can be in all three Areas: Area1/1A1, Area2/1A1.

Each Bin can have multiple parts (Bin only drills down to the shelf level, not a specific spot on the shelf).

So, I have about 250 bins in Area1, 50 in Area2, and 50 in Area3.

Dave, if I understand what you are saying - the only way a part is related to a Bin is by the tlbStockRecord? Don't I want a separate table for the part locations and for stock records?

Using your example, how would a query be able to show me where a certain part is?

To continue the setup:
users only need to be able to search, find, and sign out parts.
Techs (maintenance) do a monthly stock take.
The parts manager does all the rest. Just added this for clarity...don't know if it helps the big picture.

Hope I got enough info there for you all.

oh, as far as the combo boxes: all I need to do for the general user is display where a part is once they type in a search box (text box runs a query and displays results in a list box). Then they click on the specific part they want, which opens a form with the relevant data for that part.

On the other end, the manager needs to be able to do the same, as well as add new parts with a Bin location and edit existing part's locations when the stuff gets moved. So I was thinking a list box to display the part's locations (because it can be in more than one area - across the street, for example).

Disclaimer: I don't really know what the best way to label these part locations, just trying to work with how others have had it set up for years. I am open to ideas!

Thanks guys!

Mike

 

[jdraw]

Area, Aisle, Section, Shelf are "locator" attributes. Geographic type identifiers that seem to form a hierarchy.

You may use a common/repeated numbering/identification system when you say

A Bin, 1A1 for example, can be in all three Areas: Area1/1A1, Area2/1A1.

I don't believe that is physically possible, but you may have a Bin called A1A in each Area.
But the fully qualified Bin will have an Area identifier - Area1 Bin 1A1.

In modelling terms you have

1 Area can contain 1 or more Aisles
An Aisle has 1 or many Sections
A Section has 1 or many Shelves

It seems to me, and I acknowledge I know nothing of your system or business other than your post, that you have collapsed Aisle, Section and Shelf into a construct you have called "Bin".

 

[gemma-the-husky]

It won't help having multiple bins all called 1A1. It will most likely be very confusing. As far as a computer system goes, you ought to add an autonumber key to uniquely identify that bin. (or use a composite key of bin +area). However, the problem is that if you have multiple bins called 1A1, then a BIN combobox will have multiple entries for 1A1, and you won't necessarily know which one to use. Anyway, ignore that for a moment

The atomic level of storage is the BIN, I assume. Products are allocated to BINs.
What you therefore need is table design that helps identify where the bin is located.

It may be sufficient to have a BIN located in an Area. Alternatively as JDraw said, if you have an Amazon size warehouse, you may need to pinpoint the bin within the warehouse, with a more complex structure.

Once you get a table design you can either "Zoom in " to a bin, by selecting more focussed sub-units of a warehouse.

warehouse, aisle, compartment, level, etc -----> BIN

if you want to start directly from a bin and work out then you need a bin reference that uniquely identifies the bin, and it would be better to have a system that did not have duplicate BIN references, in my opinion, for that reason.

But it's your company's system, and changing your BIN ID's would probably be a significant decision for someone to make.


----
ignoring the BIN Descriptions, let's assume you add an autonumber to your bin table, (and indeed all tables) and that Area A, Bin 1A1 has a system ID of 24876. (just a unique number). If you have read about autonumbers, you may realize that they are a very efficient way of relating records in different tables. Importantly the autonumber is distinct to the BIN description (or other entity description). So if Bin# 24876 refers to Bin 1A1 - you can actually change the description of 1A1 to a different value, without affecting any other data in your system, and avoiding the need for what are called cascading updates.

When you get a stock movement, you might decide to store 50 green widgets in BIN 24876. So you have a part record for Green Widget (Part# 12888). And you end up with an entry in your stock movement record table showing

BIN# 24876, Part# 12888, Qty 50, Date Sep 15 2015, Movement Record# 2345

So you end up with a table that looks like this

BIN PART Qty DATE DOCUMENT
24876 12888 50 15/09/2015 2345
24876 12888 12 18/09/2015 2346
24876 12888 -17 18/09/2015 3012
24876 12888 31 04/10/2015 3126
23679 12888 87 15/09/2015 2387 'same part in another bin

the total stock of part 12888 is 163 (sum of all the movements)

by bin, you have
Bin 24876 76
Bin 23679 87

If you want to issue 100 items for some reason, you can identify which bin(s) the picker needs to go to fulfil the issue requirement. Generally there is no need to store a running total of items in a bin. It is easier to sum the movement records. At some point (say during a stock take) you can collapse records historically to a single count record, and start again.

Hope this helps.

 

[spikepl]

This is just like people all living in towns having streets of the same name.

So you have someone at 2, Highstreet, Town1, and someone else at 37, Highstreet, Town2 etc.

The full address is City, Street, StreetNumber.

Then you could have a postal code, function-wise an autonumber for each number at each street of each town.

 

[mjdemaris]

It seems to me, and I acknowledge I know nothing of your system or business other than your post, that you have collapsed Aisle, Section and Shelf into a construct you have called "Bin".

You are quite correct.

Dave,

So, Should I just type all the location information into one table?

tblLocations
ID
Area
Bin

If I do it this way, will I be able to use a combo box for Area, so that when a part gets moved, I select the Area, which then limits me to the Bins only in that Area?

 

[jdraw]

There is an older tutorial video (actually 2 videos) from Datapig.
These will show you the cascading combo approach. I think it is relevant to you latest post and question. Good reference even if you decide not to use it.
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

 

[mjdemaris]

Well, I created one table that stores all the Bin locations, as described in my last post. The cascading combos work, now my question is how to use this to change the actual location?

So, I've got a table that stores the part number id, and the location id. How do I relate the values from my combos to the id for a certain part and its location/bin?

 

[jdraw]

Show us you tables and relationships? or post a copy of your database. (zip format).

Why don't you write down several scenarios for your testing.

Add XX of Part YY to Bin 4 in Area PL6.
Remove ww of Part xx from Bin X in Area B
Transfer 3X of Part 6A to Bin 5P from Bin 4Y
....

You can do these things on paper to get some understanding of the logic involved. Make sure your tables and relationships support what you are trying to do. Reconcile every anomaly. Then test again until it all works.
Once it all works on paper, then do your coding/programming.

See the links in my signature for debugging tips. Use lots of debug.print statements while you're developing. Step through the code, use the immediate window to interrogate the values of variables -even change them if needed to see/work a particular scenario.

Good luck.

 

[Zelna]

I have learned some basics of Ms Access. But I'm confused a bit. Why do we use query in MS access?

 

[smig]

I have learned some basics of Ms Access. But I'm confused a bit. Why do we use query in MS access?

What basics have you learned?


This question do not belong here, so please don't go on with this

 

[Zelna]

The basics which I learned so far are table creation, values inputting, data type, and making table relationships. Excuse me for posting in the wrong thread. I'll repost it as a new thread.