Queries hurting my head!

[Guest]

Hi guys, I am trying to create a query collecting data from 4 tables as well
as calculating totals delivered but it's hurting my head trying to work out
all the joins can anyone help please?

The 4 tables are as follows:

Orders
=====
OrderNumber
CustomerName
CustomersRef

OrderDetails
=========
Order_Items_ID
OrderNumber
ItemNumber
ItemType
Description

Deliveries
=======
DeliveryNoteID
OrderNumber
Date

DeliveryDetails
==========
DeliveryDetailID
DeliveryNoteID
ItemNumber
QtyDelivered

The relationships are pretty straight forward, OrderNumber on Orders and
OrderDetails are linked as are DeliveryNoteID on Deliveries and Delivery
Details.

I want the query to list every single item on an order as well calculating
how many of that item have been delivered.

So I want to create a report that pretty much just displays:

Orders.OrderNumber - Orders.CustomerName - Orders.Customer_Ref -
OrdersDetails.ItemNumber - OrdersDetails.ItemType -
OrdersDetails.ItemDescription - Deliveries.DeliveryNoteID - Deliveries.Date -
DeliveryDetails.TotalQuantityDelivered

 

[John Spencer]

PERHAPS the following untested query will give you all the relevant rows.
Assumptions:
ORDERS always have OrderDetails
There is a maximum of 1 delivery date for any one item in an order.


SELECT Orders.OrderNumber, Orders.CustomerName, Orders.Customer_Ref,
OD.ItemNumber, OD.ItemType, OD.ItemDescription,
D.DeliveryNoteID, D.Date,
DD.TotalQuantityDelivered
FROM ((Orders INNER JOIN OrderDetails as OD
On Orders.OrderNumber = OD.OrderNumber)
LEFT JOIN Deliveries as D
ON O.OrderNumber = D.OrderNumber)
LEFT JOIN DeliveryDetails As DD
ON D.DeliveryNoteID = DD.DeliveryNoteID
AND OD.Itemnumber = DD.ItemNumber

IF that works then perhaps you can do the calculations. Your problem is
going to be if there can be multiple deliveries of the same item on
different dates.

 

[Guest]

I also forgot to add that there is a field called Qty in OrderDetails and I
want to be able to subtract total quantity delivered from Qty to give a qty
remaining figure.

 

[Guest]

Hi John, thanks for your reply, however in this particular example there may
well be multiple deliveries on different dates so I need to account for that
also.

I'll try your SQL tomorrow and see if my head has cleared somewhat by then.