Database design for tracking tracking deliveries against orders

[TimTDP]

I need to track deliveries from suppliers against purchase orders.
i.e I generate a purchase order on a supplier. The supplier may deliver:
* all products in one delivery
* products over a couple of deliveries
* may substitute product with an alternative

I am racking my brains on how to set the tables and the forms.

Can anyone suggest the correct structure for this? Perhaps know of a sample database to get my grey matter working?

Thanks

 

[gemma-the-husky]

this sort of structure maybe

tables

Suppliers
PurchaseOrders (suppliers 1-many purchase orders)
POItems (purchase orders 1 - many POItems)
DNHeader (- delivery note - supplier 1 - many DNs)
DNItem (- deliverynote items - DNHeader - 1 - many DNItems)

so, at this point the DNItem also needs to be linked to the relevant POItem. You need to store the POItem as a FK in the DN items table, to allow for multiple deliveries against a single order line.

you need to consider how to manage the substitute products. I suspect you would be best adding an item to the POrder items to reflect the substitute, and maybe updating the order quantity of the original item to reflect that change.

PO control can end up being a little fuzzy depending on the industry as over/under supply may be customary in some industries, as is price change during the life of an order.

hope this helps

 

[jdraw]

In addition to Dave';s comment, there are some free data models at

http://www.databaseanswers.org/data_models/index.htm

This one may have some structure info you could adapt
http://www.databaseanswers.org/data_models/customer_deliveries/index.htm
and/or this
http://www.databaseanswers.org/data_models/customers_and_partial_deliveries/index.htm

 

[Cody.K]

Which version of MS Access are you using? I'm fairly certain that there are templates for that on Office.Microsoft.com.


http://office.microsoft.com/en-us/templates/results.aspx?qu=access&av=zac