# Access Query - Return Exact Join Value or Next Lower Value

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

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.