Sales orders and Delivery notes

In my Sales order database one customer can have many orders. One order can
have many products. These products are often delivered in batches, so I
need multiple deliveries to one sales order. Can you help?

The connection between orders and deliveries is not a straightforward one.

Consider cases such as:

a) Items from one order can delivered in several stages (partial backorders)

b) A delivery could contains items form multiple orders (back order +
current order in one delivery.)

c) Some ordered items may never be delivered (partical delivery of
discontinued items)

d) Some ordered items may be replaced with other items (substitutes)

e) Delivery could include some unordered items (e.g. free samples)

f) An order (or part of an order) will never be fulfilled (e.g. bad debtor.)

If your system needed to cope with all of that, you would be looking at a
many-to-many relation between line items of the order and line-items of the
delivery. That can get messy to interface.

You may be able to simplify this with just a one-to-many relation between
order line-items, and delivery-line items. This doesn't cope with (d) or
(e), and requires additional handling for (c) and (f), but does cover the
majority of cases.

With this structure, the Delivery table contains fields such as:
DeliveryID Primary key
DeliveryDate Date/Time
SupplierID foreign key
and the DeliveryDetail table has fields:
DeliveryID foreign key to Delivery.DeliveryID
OrderDetailID foreign key to OrderDetail.OrderDetailID
QtyRecd Number (how many received)

So, for each line of an order, it has been fully delivered when the quantity
ordered matches:
DSum("QtyRecd", "DeliveryDetail", "OrderDetailID = 999")
where 999 represents the OrderDetail.OrderDetailID.

To cope with (c) and (f), you might add a yes/no field to the OrderDetail
table to indicate when the delivery is complete. This gives you the
flexibility to mark an order row as complete even if it is not delivered.

Hope that's helpful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jackie M" Jackie wrote in message
...
In my Sales order database one customer can have many orders. One order
can
have many products. These products are often delivered in batches, so I
need multiple deliveries to one sales order. Can you help?