Generating a query using 4 tables and totalling one of the columns :S

[HairyArse]

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
Qty
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 for all orders as well calculating
how the quantity remaining to be delivered based on deliveries so far and Qty ordered.

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

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

Can this be done as one query or do I need to join two queries together?

It's hurting my head

 

[MStef]

Hello Hairy!

I think I have guesst what you need.
Look at "Demo4tTotalA2000.mdb"