Stocktake table, how to query and use it for "ItemInStock"

[Aries via AccessMonster.com]

Hi, I have made a simple Inventory Control db and I would like to add a
stocktake table to better deal with missing items that is bound to happen in
the future.

tblProducts (ProductID "PK",ProductName, )

tblDeliveries (DeliveryID "Pk", DeliveryDate, Notes)

tblDeliveryDetails (DeliveryDetailID "PK", DeliveryID "FK", ProductID "FK",
Quantity)

tblReleases (ReleaseID "PK", ReleaseDate, Notes)

tblReleaseDetails (ReleaseDetailID "PK", ReleaseID "FK", ProductID "FK",
Quantity)


I already made a table for the stocktake containing these fields

tblStockTake (StockTakeID "PK", StockTakeDate, ProductID "FK", Quantity,
Notes)


My problem is I don't know how to integrate it with my current query for the
"ItemsInStock" which I had by simply subtracting the quantity released from
quantity delivered.can


Hope you can help me. thanks!

 

[Noëlla Gabriël]

Hi,

I've designed several warehousesystems for different businesses, and found
that the classical approach still works the best.

Simplified you have the followingtables:
---------------------------------------------
*tblCustomers: PK custID; other possible fields: name, adress, ....
* tblProducts: table with all stored products info, PK prodID; normally the
goods are linked to the customer, saying a bottle of Whiskey stored for
customer A is another product then the bottle of whiskey stored for customer B
* tblPurchaseOrders table with the order info for incoming goods PK: poID
(long) other possible fields: purchase date, customer, status, ...)
* tblPurchaseOrderLines detail lines of the incoming orders PK: polID, FK
polPO that relates to the poID field of tblPurchaseOrders, FK poProduct that
relates to the products table prodID, possible other fields: polDescription,
polQuantity, polWeight, polVolume, .....
* tblDeliveryOrders: table with the order info for the outgoing orders; PK
doID; other possible fields: doDate, doCustomer, doreference, doPickingDate,
doExpeditionDate, doStatus, ....
*tblDeliveryOrderLines: detail lines of the outgoing orders with a simular
structure as tblPurchaseOrderLines
*tblStock: this table contains the actual stocksituation of which article
and quantity is placed in which location. The stocktable is automatically
filled by triggers or code when a purchase order line is stored in the table,
and is automatically diminished when the article moves out the warehouse with
a purchase order line.

You could say you could calculate the current stock by subtracting the
outgoing orders from the incoming but this is a very time consuming query and
can be very troublesome when having to produce stockreports.

In real world warehouses there are a lot more tables (packing types,
locations, movements, ect.) but I hope this gives an idea how to handle
warehouse systems.

 

[Aries via AccessMonster.com]

Hi Noella,

Thank you for the suggestion. I would love to use a better approach than what
I'm doing so far but my limited knowledge with access and especially with the
"coding" makes it really hard and almost impossible for me at this point to
have a more sophisticated db design. maybe sometime when I get more
experienced with access will I try and improve my database by using more
advanced techniques in database creation, so for now, using my current db, a
stocktake table is what i see extremely useful and some help on "how" to do
it would be very much appreciated.


Regards,

-Aries.

Hi,

I've designed several warehousesystems for different businesses, and found
that the classical approach still works the best.

Simplified you have the followingtables:
---------------------------------------------
*tblCustomers: PK custID; other possible fields: name, adress, ....
* tblProducts: table with all stored products info, PK prodID; normally the
goods are linked to the customer, saying a bottle of Whiskey stored for
customer A is another product then the bottle of whiskey stored for customer B
* tblPurchaseOrders table with the order info for incoming goods PK: poID
(long) other possible fields: purchase date, customer, status, ...)
* tblPurchaseOrderLines detail lines of the incoming orders PK: polID, FK
polPO that relates to the poID field of tblPurchaseOrders, FK poProduct that
relates to the products table prodID, possible other fields: polDescription,
polQuantity, polWeight, polVolume, .....
* tblDeliveryOrders: table with the order info for the outgoing orders; PK
doID; other possible fields: doDate, doCustomer, doreference, doPickingDate,
doExpeditionDate, doStatus, ....
*tblDeliveryOrderLines: detail lines of the outgoing orders with a simular
structure as tblPurchaseOrderLines
*tblStock: this table contains the actual stocksituation of which article
and quantity is placed in which location. The stocktable is automatically
filled by triggers or code when a purchase order line is stored in the table,
and is automatically diminished when the article moves out the warehouse with
a purchase order line.

You could say you could calculate the current stock by subtracting the
outgoing orders from the incoming but this is a very time consuming query and
can be very troublesome when having to produce stockreports.

In real world warehouses there are a lot more tables (packing types,
locations, movements, ect.) but I hope this gives an idea how to handle
warehouse systems.

Hi, I have made a simple Inventory Control db and I would like to add a
stocktake table to better deal with missing items that is bound to happen in

[quoted text clipped - 22 lines]

Hope you can help me. thanks!

 

[Aries via AccessMonster.com]

Anyone? I tried lots of things but I failed. Hope someone can help me. thanks!