Delivery Notes - puzzled.

[Guest]

Hi, I already tried asking this question in another thread but I'm not sure I
was understood correctly.

I am creating an ordering system for which there are at present two main
tables.

Orders and Order_Details

Obviously, Orders contains the main details of an Order, whilst
Order_Details contains each item in that order. Now I need to be able to
create a delivery note, which will display the fields from Orders as well as
which ever items are to be sent in that particular delivery.

To solve this I am creating a third table called Delivery_Notes which is
linked to a field called Orders_Items_ID in Order_Details and my thinking is
that Delivery_Notes will generate a delivery note number which i can use to
refer back to delivery notes. Is this the best way to do this or can anyone
think of a better way of doing this?

Basically for any one order there could potentially be multiple delivery
notes, but each delivery note could also have multiple items on it. So I need
to generate a delivery note number but I need to be able to attach the same
number to multiple items.

My system is getting complex now and my head is hurting so can anyone
explain in clear english what I need to do rather than just giving me loads
of code and not telling me where to apply it

 

[Nikos Yannacopoulos]

Richard,

Basically for any one order there could potentially be multiple delivery
notes, but each delivery note could also have multiple items on it.

says it all. I believe you need a Delivery_Notes and a Delivery_Details
table.

Assuming a delivery would only ever be linked to a single order, you could
use either of two schemes to match Deliveries with Orders:
1. An OrderID field in the Delivery_Notes table, or
2. A separate table with two fields, OrderID and DeliveryID
I guess I would opt for 1, to keep things simpler.

If, on the other hand, a delivery may be made for (parts of) multiple orders
(from the same customer, obviously), then even option 2 above would not do
it, you would need to store OrderID at line item level in the delivery, by
means of an extra field in the Delivery_Details table.

In any case, you would need a Complete Yes/No field in your Orders table.

A query showing balance outstanding to be shipped per item for incomplete
orders would also come in handy, I suppose.

I hope these thoughts are helpful.

Regards,
Nikos

 

[Guest]

Nikos thanks for your reply. I think I can see where you are going with
suggesting two tables for delivery notes, but my head is in a real mess with
this and i'm trying to work out which fields would go in which table? Would
Delivery_Notes simply contain a reference number with Delivery_Details
containing the details of each item? Also would the info for Delivery_Details
be looked up in a query?

AAAAH my head is gonna explode.

(In this system as well as the ordering system i'm working on now, I have a
purchase order system, a quotation system, a telephone call log, a customer
database and a product catalogue - hence it's driving me mental!?!?!?)

 

[Nikos Yannacopoulos]

Richard,

The way I envisage it, the absolute minimum fields per table would be:

Delivery_Notes table:
DelivID (Primary Key)
OrderID (Foreign Key on Orders table)
DelivDate

Delivery_Details table:
DDID (PK, autonumber)
DelivID (Foreign Key of Delivery_Notes table)
ProductID (Foreign Key on Products table)
Quantity

The above assumes a delivery can only be related to a single order. In case
you are considering the multi-order delivery scenario, you should move the
OrderID field from Delivery_Notes to Delivery_Details.

In either case, all other data is pulled from the order, e.g. customer from
order header, prices - if applicable - from order details etc. You may
have to add other fields, for instance if you need additional functionality
such as different pricing for different deliveries within the same order
etc.

By the way, if you are working on all those projects, you might as well
share common tables between apps, e.g. the sales orders db could well be
linked to customers in the customers db and products in product catalogue,
so you avoid data duplication and make maintenance easier and the whole
thing more robust.

HTH,
Nikos

 

[Guest]

Hi Nikos,

Thanks again for your reply. I will see if I can get my head around this
today. It's much appreciated.

And in answer to your question - Yes I have linked all my tables throughout
all my other systems - you should see my relationships diagram!

Thanks
Richard