Table & Form design - Stock Transfer Multiple Locations

[Nika]

I am a student and making a project of multiple warehouse locations transfer.

How i will make table and form design for inventory transfer

From Warehouse:
To Warehouse:

I have attached the table structure which i am following and form design which i want to make as like that.

Kindly guide me what are changes required in the table design.

Thanks

 

[Gasman]

Pretty much the same question was just asked the other day.

Transfer stock to location

Hi all, I am struggling to get this one going and was hoping to get some assistance I have a table where all qty's of products are recorded for stock procured and stock sold I want to be able to transfer manufactured stock between different locations So what I need to do is have a form where...

www.access-programmers.co.uk

 

[Nika]

I am little confuse and have the following questions

It's mean that i have to add two columns in transactions table, i.e

FromWarehouse
ToWarehouse

Is it ok to give reference two times from the same table i.e Warehouse?
How the relationship would be made?
What is the name of relationship would be called?

If possible kindly attach any database relationship image or send reference.

 

[CJ_London]

Is it ok to give reference two times from the same table i.e Warehouse?

yes - not a problem

How the relationship would be made?

in the relationships window, drag the warehouse table onto the relationships window a second time, it will be aliased with a _1 suffix. Then make your join from the other field

Note that the relationships window may reinterpret this differently when reopened - but the relationship will be OK

What is the name of relationship would be called?

Happens automatically - see the msysRelationships table

 

[gemma-the-husky]

I see what you mean.
Technically it might be better to treat it as two transactions, which would simplify your inventory movements table

WarehouseID Product Qty
Stevenage 1234 -500 (from)
Nottingham 1234 +500 (to)

or even
WarehouseID Product Qty
Stevenage 1234 -500 (from)
Nottingham 1234 +200 (to)
Derby 1234 +300 (to)

the entire adjustment just needs to sum to zero

It's similar to a double entry ledger journal, and you would never do that with a from and to column. (unless it's like the admiral in HMS Pinafore - what never? well, hardly ever)

Even you have a form showing from and to warehouses, I think you would want to process the entry as 2 transactions. You wouldn't want a table showing a history of from and to records, I don't think.

 

[CJ_London]

It is a common debate as to whether to use from/to fields in one record or use two records. Using two fields in one record is only valid if the transaction will never be split or combined - for example warehouse 1 orders 20 of product A from central stores, then a bit later 30 more. Central stores cannot fulfil by sending 50 as one record, they need to use two records. If using a record for each side of the transaction, then you can send one consignment which requires 3 records.

Having two fields does makes calculating inventory more complex since the inventory calculation needs to account for whether the warehouse is in the from field or the to field. With the two record method, not a problem, but then you do need to create the second record

So a lot depends on how your other systems work and the reality of processing transactions (manual input? import from other systems?) as to which method is most appropriate for your situation. The two record method is more flexible so if in doubt my advice is use the two record method.

What you don't want to do is store inventory - calculate from transactions as required. To maintain this table becomes very onerous when corrections need to be made

Appreciate this may be beyond your project brief but the transactions table (or perhaps a related table/s) needs to store all transactions, not just stock in/out but also stock adjustments, wastage etc. It may also need to store status - for example stock reserved for a specific customer, stock on hold (e.g. waiting on quality assessment) and things like bin location/s - easy for the 2 record method, not so easy for the two field method.

 

[Nika]

Thanks a lot all of you,

@CJ_London, if we will implement two record method then what would be the table structure and form for the transferring of stock from MWH to WH1.

 

[CJ_London]

Difficult to answer as there is a lot of detail missing which would have an impact. Off the top of my head, it might look something like this - just a guide

TransactionPK
ProductFK - link to product
TransactionType - Transfer/Recd/despatched/StockAdj/etc - could store in a separate table
WarehouseFK - link
ReferenceFK - link to order/transaction header/etc
TransactionDate? - might store here, but more likely the reference table
Qty

first record qty would be positive, warehouseFK the receiving warehouse, second record (autocreated) qty negative, warehouseFK would be the sending warehouse all other fields except transactionPK the same (or you might use rec'd/despatched instead of transfer)

I know this is only a project, but your app is dealing with regions and countries etc in a simplistic way that does not translate to reality. Goods do not miraculously transfer from one warehouse to another in the blink of an eye (unless this is Star Trek ), it takes a day or even weeks. So at any one time potentially you will have an enormous amount of goods in transit, so neither at one warehouse or another.

You need to look at the order of events and potential consequences, eg:

Warehouse2 places an order on warehouse1
Warehouse1 receives the order
Warehouse1 despatches the goods (or perhaps an equivalent product if the ordered product is not available and the order is urgent)
Warehouse1 marks the order closed (or perhaps not because they have only despatched part of the order)
Warehouse2 receives the goods
Warehouse2 books the order
Warehouse2 marks the order closed (or perhaps not as above)

So for forms you might have several, each one completed by a different user in different locations. Or perhaps one form which shows/hides various fields depending on the action being taken.

Also depends if your project is just mapping the transfers which are managed by other systems/methods or is supposed to manage the process

 

[Nika]

Difficult to answer as there is a lot of detail missing which would have an impact. Off the top of my head, it might look something like this - just a guide

TransactionPK
ProductFK - link to product
TransactionType - Transfer/Recd/despatched/StockAdj/etc - could store in a separate table
WarehouseFK - link
ReferenceFK - link to order/transaction header/etc
TransactionDate? - might store here, but more likely the reference table
Qty

first record qty would be positive, warehouseFK the receiving warehouse, second record (autocreated) qty negative, warehouseFK would be the sending warehouse all other fields except transactionPK the same (or you might use rec'd/despatched instead of transfer)

I know this is only a project, but your app is dealing with regions and countries etc in a simplistic way that does not translate to reality. Goods do not miraculously transfer from one warehouse to another in the blink of an eye (unless this is Star Trek ), it takes a day or even weeks. So at any one time potentially you will have an enormous amount of goods in transit, so neither at one warehouse or another.

You need to look at the order of events and potential consequences, eg:

Warehouse2 places an order on warehouse1
Warehouse1 receives the order
Warehouse1 despatches the goods (or perhaps an equivalent product if the ordered product is not available and the order is urgent)
Warehouse1 marks the order closed (or perhaps not because they have only despatched part of the order)
Warehouse2 receives the goods
Warehouse2 books the order
Warehouse2 marks the order closed (or perhaps not as above)

So for forms you might have several, each one completed by a different user in different locations. Or perhaps one form which shows/hides various fields depending on the action being taken.

Also depends if your project is just mapping the transfers which are managed by other systems/methods or is supposed to manage the process

Thanks a lot for your efforts and advice.