Avatar of Scamquist
Flag for United States of America asked on

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

Avatar of undefined
Last Comment

8/22/2022 - Mon
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Works great!

Thank you very much.  The sample was a very unexpected, but nice touch.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck