R
Richard Horne
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
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