INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

FIFO Query Question

FIFO Query Question

(OP)
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:
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
 

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:
Order_ID	Quantity_Delivered
101	          1200

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

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

 

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.

RE: FIFO Query Question

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...
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
 

So I'd use VBA.

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: FIFO Query Question

(OP)
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.

RE: FIFO Query Question

Use [pre]...[/pre]

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: FIFO Query Question

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

1) support CumulatedDelivery query:
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; 

2) output RemainingDelivery query:
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));


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close