Inventory for multiple locations

[Testate]

I have been working to get a database set up which controls inventory (in and out) at multiple warehouses. I can't get the table/query set up properly to work when it is multiple locations.

Has anyone seen or made a database template which fits this scenario? Could anyone please point me in the right direction?

 

[MarkK]

Hi and welcome to the forum

This is a very hard problem. I recommend you start by communicating what your table design looks like, and people here might give you feeback on that first. Can you take a screen shot of your table relationships?

 

[ByteMyzer]

I have seen Inventory databases that managed inventory grouped by Part, Warehouse and Storage Location. Here is some sample table/query architecture that may help you with your approach:

tblWarehouse
---------------
whCode
whDescription

whCode | whDescription
-------+------------------
MAIN   | Main Warehouse
ADDWH1 | Additional WHSE 1
ADDWH2 | Additional WHSE 2

tblLocation
------------
whCode
lcCode
lcDescription

whCode | lcCode | lcDescription
-------+--------+--------------
MAIN   | BAY1A  | Bay 1A
MAIN   | BAY1B  | Bay 1B
ADDWH1 | SHLF01 | Shelf 01
ADDWH1 | SHLF02 | Shelf 02

tblInventory
--------------
partNumber
whCode
lcCode
Qty

partNumber | whCode | lcCode | Qty
-----------+--------+--------+----
123-1234   | MAIN   | BAY1A  | 100
123-1234   | MAIN   | BAY1B  | 50
456-7890   | MAIN   | BAY1B  | 70
456-7890   | ADDWH1 | SHLF02 | 130

An example query for total inventory by Part Number and Warehouse Code:
SELECT tblInventory.partNumber, tblInventory.whCode, SUM(tblInventory.Qty) AS Qty
FROM tblInventory
GROUP BY tblInventory.partNumber, tblInventory.whCode;

partNumber | whCode | Qty
-----------+--------+----
123-1234   | MAIN   | 150
456-7890   | MAIN   | 70
456-7890   | ADDWH1 | 130

 

[Testate]

Re: Hi and welcome to the forum

This is a very hard problem. I recommend you start by communicating what your table design looks like, and people here might give you feeback on that first. Can you take a screen shot of your table relationships?

I have attached my simple start to the database, because I dont know how to put a screen shot in this message.

 

[Testate]

@ByteMyzer
I believe what you wrote is very similar to what I have done, but the problem arises when the inventory transactions (inventory in and out) come in to play.

 

[MarkK]

Yeah, you want to track the dated movement of stuff. What I would expect to see is a transfer order with a date and an origin and destination location. Then each transfer order has many TransferOrderDetail records, each of which records the quantity and ProductID moved.

But it depends how stuff can enter and leave the system too. Maybe you need SalesOrders, PurchaseOrders and TransferOrders, and if so then maybe you want one Order object, with various types.

You could also model this after double-entry bookkeeping, in which every Order must balance as you debit and credit quantities to and from locations.

But it's not a trivial task to model this, and there are many approaches you can take.

 

[jdraw]

Here are some links that may help you with your project.

This is a video on development and operation of an actual stock management database.

But there are many youtube videos/tutorials on Inventory Management and related topics. Lots of info even from vendors like Oracle/JDEdwards, logistic companies etc.

There are several free data models at Barry Williams' site.
This is a generic model for Inventory control at a retail store.
This one deals with warehouse inventory management.

Here is an article by Allen Browne re Stock on Hand.
Search this forum for Inventory Control -- the responses may help with analysis and design .

Just to add to the comments form the others --inventory management is not a simple subject. Do your homework (solid analysis); build and test a data model against your specifications and business rules; get yourself a model that has been "bounced off management and those expected to use it" before getting too deep into coding.

Here are some comments re testing your data model.

Good luck.

 

[Testate]

@jdraw
Thanks for the links. Believe me, I have looked high and low for solutions before resorting to posting for help. It always comes down to examples and info being inventories only at one locations.

I will read those links. If I can find a solution from those links, I will definitely post the solution because I could not find any through my searches.

 

[Testate]

Solved.

Unfortunately, after reading all the linked information, the answer still eluded me. Had some help from an individual elsewhere. Modified their response to get the answer. It was a modification (...well, completely new...) of the query.

SELECT InvTrans.WID, InvTrans.PID, Sum(IIf([ITTID]=1,[qty],0)) AS StockIn, Sum(IIf([ITTID]=2,[qty],0)) AS StockOut, Sum(IIf([ITTID]=1,[qty],0)-IIf([ITTID]=2,[qty],0)) AS Stock
FROM InvTransType RIGHT JOIN InvTrans ON InvTransType.ITTID = InvTrans.ITType
GROUP BY InvTrans.WID, InvTrans.PID;