FIFO Query Question

[mmogul]

I need some advice on the best way to solve this issue – whether it makes sense to address it with a query or better to write a vba function.

I have a table with orders and requested delivery dates. Assume that all orders are for the same item number. An order can have multiple delivery quantities and dates. The table would look like:
[pre]Order_ID Delivery_ID Quantity Expected_Date
101 1 1000 1/1/2017
101 2 2000 3/1/2017
101 3 2000 4/1/2017
201 1 6000 3/15/2017
[/pre]

There is another table that shows actual deliveries to date by Order_ID. The quantity delivered is applied to the outstanding orders on FIFO (first in, first out) basis:
[pre]Order_ID Quantity_Delivered
101 1200

[/pre]
I want the result to show the Orders with outstanding quantities by Expected_Date. So with the above data, the result would be:

[pre]Order_ID Delivery_ID Quantity Expected_Date
101 2 800 3/1/2017
101 3 2000 4/1/2017
201 1 6000 3/15/2017

[/pre]

Can this be handled with a standard query? Or is it best to create some temp tables and use vba to loop through the data to determine which items are completely shipped?

Thanks.

 

[SkipVought]

mmogul, you've been knocking around here at Tek-Tips for some 14 years, but not using the display tools developed here for members to more clearly state their questions like...
[pre]
Order_ID Delivery_ID Quantity Expected_Date
101 1 1000 1/1/2017
101 2 2000 3/1/2017
101 3 2000 4/1/2017
201 1 6000 3/15/2017
[/pre]

So I'd use VBA.

But why wouldn't you also have a Shipped_Data and a Delivered_Date in your Deliveries Table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mmogul]

I've been appropriately slapped on the hand. I did a cut / paste without thinking. Sorry about that.

Thanks for the response. Skip - I don't have any control over the tables in this app. This is a package from another company - so I'm working with what I've got. Appreciate your opinion regarding VBA. I think that is the way to go.

 

[SkipVought]

Use [ignore][pre]...[/pre][/ignore]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Without vba, in ms access, assuming that Delivery_ID numbers orders, your input tables are named Tbl_1 and Tbl_2:

1) support [tt]CumulatedDelivery[/tt] query:
[pre]SELECT Tbl_1_1.Order_ID, Tbl_1_1.Delivery_ID, Tbl_1_1.Quantity, Tbl_1_1.Expected_Date, Sum(Tbl_1_2.Quantity) AS Quantity_Cumul
FROM Tbl_1 AS Tbl_1_1 INNER JOIN Tbl_1 AS Tbl_1_2 ON Tbl_1_1.Order_ID = Tbl_1_2.Order_ID
WHERE ((([Tbl_1_2].[Delivery_ID]<=[Tbl_1_1].[Delivery_ID])=True))
GROUP BY Tbl_1_1.Order_ID, Tbl_1_1.Delivery_ID, Tbl_1_1.Quantity, Tbl_1_1.Expected_Date;[/pre]

2) output [tt]RemainingDelivery[/tt] query:
[tt]SELECT CumulatedDelivery.Order_ID, CumulatedDelivery.Delivery_ID, IIf([Quantity_Cumul]-[Quantity]>=Nz([Quantity_Delivered],0),[Quantity],[Quantity_Cumul]-Nz([Quantity_Delivered],0)) AS Quantity_Remaining, CumulatedDelivery.Expected_Date
FROM CumulatedDelivery LEFT JOIN Tbl_2 ON CumulatedDelivery.Order_ID = Tbl_2.Order_ID
WHERE (((IIf([Quantity_Cumul]-[Quantity]>=Nz([Quantity_Delivered],0),[Quantity],[Quantity_Cumul]-Nz([Quantity_Delivered],0)))>0));[/tt]

The same calculations can be done in excel Power Query, built-in feature in 2016 (as Get & Transform) or free ms add-in for earlier versions.


combo