If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Sales orders and Delivery notes
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? |
Thread Tools | |
Display Modes | |
|
|