You probably already have tables for PurchOrder (the purchase order header
record), and PurchOrderDetail (the line items on a purchase order.)
Now you need 2 more tables to record the actual deliveries to record the
actual acquisition into stock. The Acquisition table will contain fields
such as:
AcquisitionID AutoNumber primary key
SupplierID Number who this delivery was from.
AcquisitonDate Date/Time when the delivery was received
You will also have the detail table that records the items actually
delivered:
AcquisitionDetailID AutoNumber primary key
AcquisitionID Number which delivery this line belongs to.
Quantity number how many were received.
ProductID number which product was received.
PurchOrderDetailID Number which purchase order line item this delivery
is in response to.
When you receive a delivery, you create a record in the Acquisition table.
Now the database knows which supplier sent the stuff, so if you want to
write some code you can have it lookup which purchase orders from that
supplier have outstanding items, and automatically assign those items to
this delivery. You can then go through and delete the lines for the products
that did not make it yet, or decrease (or increase) the Quantity of the
lines where only part of the quantity was delivered.
To automate that process, you would need to know which purchase order detail
lines were (fully or partially) delivered in any acquisition detail record.
That's the reason for the last field listed above. A PurchOrderDetail record
can have multiple entries in the AcquisitionDetail table (if it is delivered
in dribs and drabs.) When the quantity actually delivered equals or exceeds
the quantity originally ordered, that item has been fully delivered.
In addition to that, you probably want to add a yes/no field to your
PurchOrder table to indicate if the order is complete. This allows you to
mark an order as complete even if not all items have been delivered (e.g.
where an ordered item is no longer being manufactured.)
