troubleshooting Question

Access Query - Return Exact Join Value or Next Lower Value

Avatar of Scamquist
ScamquistFlag for United States of America asked on
Microsoft AccessSQL
3 Comments1 Solution417 ViewsLast Modified:
I am not sure if the title exactly explains what I am trying to do, but it is as close as I could figure out.

I have a table with an daily running on hand inventory value for a number of items.  Each time an item has a projected change to inventory (receipt or usage), a new record is created with the item number, InvDate and new quantity.

Item      InvDate            Qty
1234      7/7/12            12      
1234      7/15/12            27
1234      7/21/12            15
1234      8/1/12            29
3456      7/10/12            120
3456      7/23/12            210
3456      7/25/12            99
3456      8/1/12            87
3456      8/3/12            44
3456      8/15/12            15
6789      7/11/12            34
6789      7/12/12            23      
6789      8/4/12            10


I have another table with the ReqDate an item will be required.  

Item      ReqDate            Qty
1234      7/10/12            5      
1234      7/12/12            2
1234      7/27/12            11
3456      7/17/12            22
3456      8/13/12            41
3456      8/22/12            11
6789      7/16/12            22
6789      7/28/12            12      

I can join the Item number, but do not know how I could find the projected inventory for each required ReqDate

Item      ReqDate            Qty      Proj Inv
1234      7/10/12            5      12
1234      7/17/12            2      27
1234      7/21/12            11      15
3456      7/17/12            22      120
3456      8/13/12            41      44
3456      8/22/12            11      15
6789      7/11/12            22      34      
6789      7/28/12            12      23

If the required ReqDate = the projected inventory date (InvDate), the projected inventory would be the quantity on that date.  If they are not equal, the projected inventory quantity should look at the quantity that is available at the next earliest InvDate.

The first item 1234 on required date (ReqDate) of 7/10/12.  I do not have a projected inventory for that day, so the returned Proj Inv would be from (InvDate) 7/7/12, quantity of 12.

The third item (1234) has the ReqDate= InvDate of 7/21/12 so the Projected Invnetory Quantity would be 15

These two tables are created from an extract from my business system.  I could add additional fields if necessary.  I could possibly find a way to grow the table to include every day between today and the max day of DateReq and join by Item and Date.  If inventory level did not change, use the value from the previous day, but this would make the table quite large.

Is there a way to use the matched day, otherwise the next earliest date and quantity.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros