V
vandy
Hi All,
I have a database in Access 2003 which keeps track of in and out of items
from the store inventory.
items are grouped belonging to a specific project and the report calculates
how much was purchased and how much was used QOH (qty on hand) tracked.
tblprojects
projectid- autonumber-PK
pno-Project no
Pname- project name
tblitems
ItemID-Autonumber-PK
Itemno- item no
ItemDesc - description
UOM - unit of measure
PoNo.- purchase order no
StockNo.-stock no of item
tbltransaction
TransactionID-autonumber-pk
PID~ProjectID - Foreign key linking project table-FK
TranItemID~ItemID - Foreing key linking tblitems-FK
Location - storage of item
Units- Qty
DOT - Date of Transaction
Type- Issued -1 , Received +1 (yes/no) feild
IssuedTo - Person issued to
Current Inventory - sum (Units*Type)
Process: I should link the item to the project and calculate the amont
received and the amt issued and qoh .
Everything was working fine until boss wanted item movement to be tracked.
What this means is :
Some items are buy items which means they are received by a po against a
project and shipped out. My system accurately captures this.
some Items have to go through a machining process before getting shipped out.
This has to be captured. The data which is fed in for this process is
Eg. Item AB has to undergo welding, heat treatment and go to the store to be
shipped out.
Project item Purchase Order Received Issued
QOH
AA AB POAB 1000
500-welding 500
500
500-treament
500
500 -shipped out
QOH = sum(2000-1500) = 500
Since Receiving and Issuing is summed and the QOH is calculated.
When receiving a buy item
Project item Purchase Order Received Issued
QOH
GG GH GH3 1000 500
500
Here since there is only one transaction and the item is purchased and
shipped out there is no process involved it calculated the QOH accurately.
Question. How to keep track of the item undergoing different process changes
and also keep the receiving qty accurate . In the above eg. The Receiving qty
is recorded as 2000 and the issuing as 1500 which is not the case. Do i have
to redesign my table do i have to back calculate from the total received. Any
pointers and help in the right direction will be higly appreciated.
thanks in advance for your patience in reading such a lengthy post.
I have a database in Access 2003 which keeps track of in and out of items
from the store inventory.
items are grouped belonging to a specific project and the report calculates
how much was purchased and how much was used QOH (qty on hand) tracked.
tblprojects
projectid- autonumber-PK
pno-Project no
Pname- project name
tblitems
ItemID-Autonumber-PK
Itemno- item no
ItemDesc - description
UOM - unit of measure
PoNo.- purchase order no
StockNo.-stock no of item
tbltransaction
TransactionID-autonumber-pk
PID~ProjectID - Foreign key linking project table-FK
TranItemID~ItemID - Foreing key linking tblitems-FK
Location - storage of item
Units- Qty
DOT - Date of Transaction
Type- Issued -1 , Received +1 (yes/no) feild
IssuedTo - Person issued to
Current Inventory - sum (Units*Type)
Process: I should link the item to the project and calculate the amont
received and the amt issued and qoh .
Everything was working fine until boss wanted item movement to be tracked.
What this means is :
Some items are buy items which means they are received by a po against a
project and shipped out. My system accurately captures this.
some Items have to go through a machining process before getting shipped out.
This has to be captured. The data which is fed in for this process is
Eg. Item AB has to undergo welding, heat treatment and go to the store to be
shipped out.
Project item Purchase Order Received Issued
QOH
AA AB POAB 1000
500-welding 500
500
500-treament
500
500 -shipped out
QOH = sum(2000-1500) = 500
Since Receiving and Issuing is summed and the QOH is calculated.
When receiving a buy item
Project item Purchase Order Received Issued
QOH
GG GH GH3 1000 500
500
Here since there is only one transaction and the item is purchased and
shipped out there is no process involved it calculated the QOH accurately.
Question. How to keep track of the item undergoing different process changes
and also keep the receiving qty accurate . In the above eg. The Receiving qty
is recorded as 2000 and the issuing as 1500 which is not the case. Do i have
to redesign my table do i have to back calculate from the total received. Any
pointers and help in the right direction will be higly appreciated.
thanks in advance for your patience in reading such a lengthy post.