Getting in a muddle with relationships

[CodLiverOil]

Hi - Help!!

I want to add functionality to a database, I wan't to be able to split ordered items between several dispatches.

I have the following tables:

tblOrders

• OrderInternalID (AutoNumber, PK)
• OrderNumber (Indexed, Unique)
• Department
• StaffName

tblOrderItems

• OrderItemInternalID (AutoNumber, PK)
• QuoteNumber
• ItemRef
• ItemName
• QuantityRequired

tblOrders has a 1-to-many relationship to tblOrderitems using QuoteNumber


The above works fine, until we get to an Item where we want to send the QuantityRequired out in two or more deliverys, so to tblOrderItems I have have added QuantityDeliveredand added two new tables:

tblDeliveries

• DeliveryRef (AutoNumber, PK)
• DeliveryDate

tblDeliverItems

• InternalID (PK)
• DeliveryRef
• OrderItemInternalID
• QuantitySent

While I can see that tblDeliveries will have a one-to-many relationship with tblDeliveryItems using DeliveryRef, I can't fathom out how tblDeliveries will get it's relationship to the items being delivered.

How I wan't it to work is that when an Order is created, with many Order Items, the user clicks a button for delivery on that order, and is asked for a Delivery ref (tblDeliveries.DeliveryRef) and date (tblDeliveries.DeliveryDate) and then is shown each item in that order and asked how many of each are being delivered.

If I connect tblDeliveries to tblOrderItems then I think that will cause each item in an order to have a different DeliveryRef
If I connect tblDeliveries to tblOrders I can't see how it will be able to reference the objects in tblOrderItems

Am I making this too complicated for myself or over thinking it? head is now fried and I am quick running out of notepad paper trying to draw down relationships!! please send help.

[rpeare]

if you want to keep the deliveres/deliveritems table setup I would include the OrderInternalID in the tblDeliveries table, this would help you pick out which order items you delivered on a particular order. Other than that I don't see a reason your current setup wouldn't work. I might also encourage you to use the PK OrderInternalID from your table tblOrders in your tblOrderItems table if you aren't (I can't tell by your nomenclature).

[orange]

You may get some ideas from this free data model
and this one with Partial deliveries and returns.

[CodLiverOil]

I still can't get this to work:

This is my relationships view:


If I create a form frmOrders, with a subform frmOrderItems that works as expected, if I create three records in the table tblOrders I can cycle through them in the form and add records to the subform that are correctly linked to the main form.

But I cant get a form or query to work for me.

In English, what I (think) I have is this:

tblOrders contains the Order Number(it will also contain the staff member name and department but they are later)
tblOrderItems contains the individual items ordered
tblDelivery contains the delivery reference and date of a particular delivery
tblDeliveryItems contains the quantity of each item (from tblOrderItems) that is being sent in a particular delivery.

The form I want to create will show at the top, the order number (tblOrders.OrderNumber) Delivery Ref (tblDelivery.DeliveryRef) Delivery Date (tblDelivery.DeliveryDate) and in a subform, in datasheet view, the Order item deetails (tblOrderItems.ItemDetails, tblOrderItems.QuantityOrdered, tblOrderItems.QuantityDelivered and tblDeliveryDetails.QuantitySent)

So the user goes to their OrderNumber, enters the DeliveryDate and DeliveryRef and then in the subform, next to the items from the Order, they enter how many of each item they have had (that then updates the tblOrderItems.QuantityDelivered)

I think I know where I am going wrong, the table tblOrderItemDetails has a 'many' relationship to the table tblOrderItems so I can't show it on the same datasheet???

But as each order can have many order items, and each order can have many deliveries, how do I get around that??? head is well and truly muddled!!

[rpeare]

Try separating your Orders form/subform and your Deliveries form/subform. If you are going to try and cram them all on the same form (even with a tabbed control) you're going to experience quite a few problems.

I do not use bound forms so I do not know if this is possible but you could also try this:

Main Form Contains tblOrder Information
Subform OrderItems contains tblOrderItems information
Second Subform contains tblDeliveries (multiple deliveries per order)
A subform within the subform tblDeliveries (multiple items per delivery)

Just bear in mind that if you do this as subforms of your form that contains the tblOrders information everything you see relating to deliveries will be related to the order, in other words you would have to cycle through orders to try and find a specific delivery number or have a fairly complex 'search' function.

[orange]

See the free model here and see if it or parts of it can help resolve "the muddled-ness".
Also, review these business rules associated with the model.

An Order contains 1 or Many Items
A Delivery (or Shipment) contains Items which may or may not be for a complete Order
(This model allows for partial shipments/deliveries)

Also, get your tables and relationships set up to meet your business requirements; test the model and refine as necessary. When your model and test data are "working", then move to forms and interfaces.

Good luck.