updating table using user value

[Guest]

Hi all,

I am trying to design a system wherein based on the the po no. each line
item gets recorded in the database.

If there is a partial shipment for the line 1 shipment than when the item is
being received by the same po no again same line no. it should add up the qty
received value.

eg. if total shipment qty = 100

first shipment qty received = 50 in line 1
second shipment qty received = 50 in line 1
shipment complete.

one po order can have more than 50 line items and i have to keep record of
each item issued in and issued out.
any help on how this can be done.

 

[Guest]

It is always good to keep this information in a separate table so that
reporting/verification can be done at a later date. Normally, you would not
want a calculated field saved in your table since the value will have to be
updated "manually". Having said that, I do have some cases where I do not
want to hit the shipment table to view current quantity amounts and I do save
the remaining quantity in my header table.

Use an Update Query where you are restricted by PO number and line number
then make the update value of the Quantity field to be
[Quantity]+[QtyReceived]

If you have multiple receipts for the same line you will have to do a Make
Table query to sum QtyRecevied by PO # and Line. Then link the new temporary
table you made to the header table in the update query
[Quantity]+[TotalQtyReceived]

Make sure you have some sort of flag in your shipments table to say it was
posted so that the same shipment record is added again.

Hope this helps.
Jackie