Inventory Control, Location Table (1 Viewer)

[mjdemaris]

Hey folks,
I am attempting to build an Inventory Control database, and I have a question regarding Location info.

At our plant, we have three separate storage locations, I'll call them Warehouse1, Warehouse2, and PIT (for Powered Industrial Trucks - for the PIT mechanic).
In WH1 & 2, our current labeling system is like:
Aisle, Section, Shelf. So it looks like 1,A,1...15,D,7.
Or CAB A, CAB FUSE...
I started to create a Locations table, but I would have to type in EVERY single location.
But, I need a simple way of allowing the Parts Manager to select the Location every time he adds/moves parts.
Should I continue with the Table I started or somehow use separate fields/controls for Aisle, Section, Shelf?
So, like a combo box with a listing: 1A1...CAB J? Or allow each field to be selected individually?
Or some other idea?

thanks.

 

[jdraw]

I recommend you work through one or more of these tutorials from RogersAccessLibrary.
You will learn a method to work from a well described problem/opportunity to a well structured database with designed tables and relationships.
You have to work through them, but you will learn basic concepts and a method that you can use with your own situation.

---ERD
---Catering
---Widgets

Start by getting a clear, complete description of the problem/opportunity is key to a well designed database.
Good luck.

 

[JLCantara]

My advise is to use separate fields: eventualy, you will learn that the query feeding the location combo can merge the 3 fields.

You will not have to type in all the locations!!! VBA can do a good deal of the job.

 

[RainLover]

My advise is to use separate fields: eventualy, you will learn that the query feeding the location combo can merge the 3 fields.

You will not have to type in all the locations!!! VBA can do a good deal of the job.

This with the use of DMax should be what you need.

 

[JLCantara]

@JDraw: your proposal is excellent; the problem is this location issue is part of a project called Inventory control in the general tab. This project is quite advanced but, as you certainly know, an inventory control app is a complex thing and should be handled by experienced people.

@deMaris: when you post in different forums, you should mention the other post...

 

[jdraw]

JL,

I agree --Inventory Control is not a trivial subject. And in my view the OP is having some issue with database concepts, and some difficulty with how to do whatever is required in Access. My suggestion is that he/she do some refresher or database concepts review before attempting to "design on the fly".
Also, I'm not suggesting the OP doesn't know the concepts, it just isn't clear to me what the root problem is, so start with a few tutorials, learn a proven process and apply it as required. Do not jump into Access without a clear understanding of your business, and database.

If I have misunderstood the OP, and he/she is intimately familiar with database concepts and has a clear definition of the business, then, perhaps he/she could show us the requirements/specifications and we can focus our responses.

 

[RainLover]

What is well understood can be stated clearly
And the code to do it comes easily!

It would be hard to find a truer statement.

 

[RainLover]

mjdemaris

Can we define the problem a little better.

If so are you satisfied that you can write an Inventory Control database then is your problem simply that you want a simple to use method that would give all locations their own identification that is not only unique but is also simple enough that everyone understands.

Namely that if a worker was told that the location was 1,A,1; then every worker would know that this applied to Aisle 1, Section A, and Shelf 1.

Not only that but also the identification system must be simple to design and simple to interoperate.

 

[mjdemaris]

OK,
You are correct that I should not attempt to develop a database on the fly. And, no, I am not experience yet. I was commissioned to assist with this project and have been doing 90% of the work, due to the work load of my cohort.
So, after giving the situation a little more thought, I decided that I need a better definition of the problem, a detailed analysis of the whole process, which is going to include an ordering system as well.

So, I will not be developing much in the future as I define the problem(s) and create a structure on paper.

As for my other posts, apologies!

As for Inventory Locations:
It's like this:
Warehouse: 1, 2 or PIT
within the warehouse: Aisle #, Section #, Shelf #.
And again, some of the Aisle locations are labeled as "CAB" for cabinet, A, B, etc.

Mike

 

[mjdemaris]

Perhaps we need to change our location labeling, I don't know. Just because we do it one way now, does not mean that this is the best or only way to do it. I am open to suggestions if you get a light bulb!

Mike

 

[jdraw]

Mike,

Here is a tutorial that may be useful to you. It is from RogersAccessLibrary and starts with a clear description and includes a process to identify and design tables and relationships.
The process can be used with any database. The tutorial includes a solution.

Details on Normalization can be found here.


See these links for additional info re modelling.
http://www.access-programmers.co.uk/forums/showpost.php?p=1368553&postcount=17
http://www.access-programmers.co.uk/forums/showpost.php?p=1368580&postcount=20

Good luck.

 

[RainLover]

The attached is a sample based upon Allen Browne.

 

[jdraw]

Rain,

Great example!!

 

[RainLover]

Thanks Guys.

 

[mjdemaris]

Rain, what is the purpose of separating the Sale and Acquisition dates?

 

[RainLover]

The simple answer is that they are different so store them differently.

I would think that you could come up with reasons to combine the tables but that simply creates surplus code.

Do you have something special on your mind other than what others are telling you.

 

[AlexN]

Sorry to intefere but,

@RainLover please tell me if I'm wrong, in your sample, based on Allen Browne's code, stock take is a manual operation. You have to manually input the desired date for the procedure to calculate stock in hand.
What if we had over 200 different products, multiple warehouses, and need to perform stock take each day, for each warehouse separately. Is there a way of doing it automatically?

Thanks.

 

[namu]

Hey folks,
I am attempting to build an Inventory Control database, and I have a question regarding Location info.

At our plant, we have three separate storage locations, I'll call them Warehouse1, Warehouse2, and PIT (for Powered Industrial Trucks - for the PIT mechanic).
In WH1 & 2, our current labeling system is like:
Aisle, Section, Shelf. So it looks like 1,A,1...15,D,7.
Or CAB A, CAB FUSE...
I started to create a Locations table, but I would have to type in EVERY single location.
But, I need a simple way of allowing the Parts Manager to select the Location every time he adds/moves parts.
Should I continue with the Table I started or somehow use separate fields/controls for Aisle, Section, Shelf?
So, like a combo box with a listing: 1A1...CAB J? Or allow each field to be selected individually?
Or some other idea?

thanks.

Hi,

You may want to consider using a cascading combo boxes (you may also search the topic for this). What it does is, when you select WH1 in the first combo box, it will limit the values (or in your case the locations/shelvings) that a user can choose on the next combo box.

 

[RainLover]

Sorry to intefere but,

@RainLover please tell me if I'm wrong, in your sample, based on Allen Browne's code, stock take is a manual operation. You have to manually input the desired date for the procedure to calculate stock in hand.
What if we had over 200 different products, multiple warehouses, and need to perform stock take each day, for each warehouse separately. Is there a way of doing it automatically?

Thanks.

Too hard to say when my knowledge is limited.

You can default today's date but other than that I don't see any more.

What specific problem do you have.

 

[AlexN]

There http://www.access-programmers.co.uk/forums/showpost.php?p=1442526&postcount=1 lies my problem.

Be kind to read the whole thread, as I later explain with more details.

Thank you for helping. Last chance for me !