HELP WITH DESIGN BASED ON MOVEMENT OF ITEMS

[Guest]

Hi All,
I have cross referenced this question in general section but it has not been
answered!

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 - HEAT
TREATMENT
500 500
-shipped out

QOH = sum(2000-1500) = 500

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.

 

[Guest]

I did just a fast read on your post and was confused. I do have a lot a
background in both the DB and engineering /
operations side of what you describe. Here's just a few notes / thoughts:

Most implementations have a main table where the data element is the item.
I.E. all identical parts have a single part number and that is one record in
that table. Current inventory (quantity) can be and usually is a field in
it.

And another table is transactions which records transactions, a record for
each instance of a addition or removal of an item. And, when executed (a
one time event) they modify your inventory level.


So, your table where you put a PO # (which is a transaction) as a field in
your item table would be a "violation" of this concept.


Your business might also need an "order" table which is groups of
transactions if such is the case and you need to record the.

When you execute a process (machining, welding, assembly etc.) that modifies
an item, you are changing it into a different item = a different part number.
And said execution is also a transaction which "uses up" (removes) an item
and creates (adds) the new item.



Your "Projects" sound like they are (for the purposes here) groups of
transactions. That's probably another table linked to the transactions on
(e.g.) the project number.

A Bill of material (that's a whole 'nother story) is a record of which
item(s) is/are used to create which items. It can become a part of a DB
process which automatically relieves and adds to inventory when that process
is executed.