Inventory managing backorders..need advice

[Dale]

Hello,
I have an inhouse Access 2K inventory db where users download items
w/purchase orders to reconcile the order qty with the rec'd qty. My design
was pretty simple (HA!), if the order qty = rec'd qty, the order was flagged
as complete. If the rec'd qty < order qty the order is flagged as
backordered. The client has since decided they want to see a historical
lookback of the purchase order based on qty rec'd.

Since there is only one physical download of the original purchase order,
the only way I can think of doing this is to recreate the purchase order for
each back order entry in the form used to update the purchase order info.
For example the downloaded purchase order 123 has an order qty of 5, only 3
items are rec'd, recreate a new record duplicating the purchase order and
the outstanding order qty of 2?

Reporting all this has me at bit apprehensive as the client wants reports
showing completed PO and backordered PO. I'll have to create a field to
flag a purchase order as complete once all the "child" records are complete.

any suggestions as how to best manage this are most welcome and thanks in
advance...D

 

[Allen Browne]

This can get messy, as there can be a many-to-many relation between purchase
orders and deliveries.

Say, you ordered 100 widgets. They supplied 90, so the other 10 are on
backorder. The widgets sell well, so you now place another order for 80
more. Next week, the 90 arrive (80 from new order and 10 from backorder.) In
this case:
- one order arrives in many deliveries;
- one delivery covers many orders.
That's many-to-many.

The standard way to design that would be with a junction table between the
2, but you may be able to get away without that if this design sounds okay:

PO table (one record for each purchase order):
POID AutoNumber primary key
PODate Date/time
SupplierID who this purchase order was sent to.
...
PODetail table (line items of the purchase order):
PODetailID AutoNumber primary key
POID relates to PO.POID Required.
ProductID relates to your Product.ProductID
Quantity number
...

Delivery table (one record each time a delivery is received.)
DeliveryID primary key
DeliveryDate date/time
SupplierID who this delivery came from
...

DeliveryDetail table (one record for each type of product in a delivery)
DeliveryDetailID primary key
DeliveryID relates to Delivery.DeliveryID
PODetailID which Purchase Order line item this fills
QtyRecd how many received
...

This arrangement means that one PODetail entry can turn up in many
DeliveryDetail entries. For the example above, where the 2nd delivery of 90
covered 2 purchase order detail lines, you would need to enter 2 rows (one
for quantity of 10 for the first PO detail, and a second row for the
quantity of 90 for the 2nd PO detail.)

One way to interface this is with a main form bound to the PO table, and a
subform bound to the PODetail (the line items in the purchase order.) Beside
that subform is another subform, showing the actual deliveries of the
selected row in other subform: delivery number (foreign key), and quantity
received.

Alternatively, you could use a main form bound to the Delivery table, and in
its subform you enter the PODetailID that identifies what was delivered in
that line.

Your suggestion of a check box to manually flag a PO (or line item) as
complete is probably still worthwhile. There will always be cases where an
order cannot be fulfilled, so if you don't have such a checkbox the software
will continue to offer that row as "unfulfilled."

 

[Dale]

Thanks for your input Allen...sounds like I'm on the right track then, yes
it is getting messy! I didn't even mention the part about lot numbers...I'll
save that goodie for later.