I've a table tDeliveries and a table tOrders. tDeliveries has the fields DeliveryID, Delivery_Date and ItemID, and tOrders has the fields OrderID, Order_Date and ItemID.
I want to create a query that displays, for each ItemID, a list of all deliveries from the tDeliveries table and their associated orders created within a few days (for example, five days) before the delivery, where the order date is closest to the delivery date.
I've attached a test database with the mentioned tables and two queries that I've tried, but none of them return the desired results.
I want the query to display the results as they appear in the table "Example Query Results" in the attached test database.
Any suggestion on how I can create a query that will provide the desired display?
I'd also like to know how to complete the query if I would like it to also compare ordered and delivered quantities of the same product (ItemID).
I want to create a query that displays, for each ItemID, a list of all deliveries from the tDeliveries table and their associated orders created within a few days (for example, five days) before the delivery, where the order date is closest to the delivery date.
I've attached a test database with the mentioned tables and two queries that I've tried, but none of them return the desired results.
I want the query to display the results as they appear in the table "Example Query Results" in the attached test database.
Any suggestion on how I can create a query that will provide the desired display?
I'd also like to know how to complete the query if I would like it to also compare ordered and delivered quantities of the same product (ItemID).