Access SQL - Get last available value

Question

scenario:

Table 1 (dates)

01/01/12
02/01/12

Table 2 (counts)

01/01/12 | Item 1 | 10
01/01/12 | Item 2 | 15
03/01/12 | Item 1 | 5

I have to join the two tables, not leaving gaps in dates: An OUTER JOIN did the trick.
I want to have the last available number if that date is missing in Table 2. E.g.

02/01/12 | Item 1 | 10
02/01/12 | Item 2 | 15

I am struggling on this. Any hints?

Could you please update your post with the SQL query that you are currently using? — James, Oct 18 '13 at 17:27
SELECT Table1.Date, Table2.Item, Table2.Qty FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Date = Table2.Date — user2139366, Oct 18 '13 at 17:28

score 0 · Answer 1 · answered Oct 18 '13 at 17:33

0

A query can transform, and join data, but it cannot generate new data (rows) that were not there to begin with. In your case if the 02/01/12 data row is missing you aren't going to be able to create rows on the fly to fill in the gaps in your query results.

To fix, create a 3rd table or sub-query that contains all of the lookup date segments that you need in your results, and include that in your outer join on the outer query.

answered Oct 18 '13 at 17:33

James

12,636
12
67
104

Not sure I got it. My query returns the 02/01/12 row, but it obviously has two empty columns for Item and quantity. My target is having those columns filled with last available quantity per item. – user2139366 Oct 18 '13 at 17:53

score 0 · Answer 2 · answered Oct 18 '13 at 18:12

Okay, so we have [Table1]

Date      
----------
2013-01-01
2013-02-01
2013-03-01

and [Table2]

Date        Item    Qty
----------  ------  ---
2013-01-01  Item 1   10
2013-01-01  Item 2   15
2013-03-01  Item 1    5

We can start by creating a saved query in Access named [DesiredRows] to give us the complete set of Date/Item values we want to see:

SELECT Table1.Date, i.Item
FROM
    Table1,
    (SELECT DISTINCT Item FROM Table2) AS i

...returning

Date        Item  
----------  ------
2013-01-01  Item 1
2013-02-01  Item 1
2013-03-01  Item 1
2013-01-01  Item 2
2013-02-01  Item 2
2013-03-01  Item 2

Now we can use that query as part of a saved query named [LastAvailableDates] to give us the last available date in Table2 for each of those pairs

SELECT 
    DesiredRows.Date, 
    DesiredRows.Item, 
    MAX(Table2.Date) AS LastAvailableDate
FROM 
    DesiredRows 
    INNER JOIN 
    Table2 
        ON Table2.Item = DesiredRows.Item 
            AND Table2.Date <= DesiredRows.Date
GROUP BY DesiredRows.Date, DesiredRows.Item

...returning

Date        Item    LastAvailableDate
----------  ------  -----------------
2013-01-01  Item 1  2013-01-01       
2013-01-01  Item 2  2013-01-01       
2013-02-01  Item 1  2013-01-01       
2013-02-01  Item 2  2013-01-01       
2013-03-01  Item 1  2013-03-01       
2013-03-01  Item 2  2013-01-01

Finally, we can use that query in a query to retrieve the final results

SELECT 
    LastAvailableDates.Date, 
    LastAvailableDates.Item, 
    Table2.Qty
FROM 
    LastAvailableDates 
    INNER JOIN 
    Table2 
        ON Table2.Date = LastAvailableDates.LastAvailableDate 
            AND Table2.Item = LastAvailableDates.Item

...returning

Date        Item    Qty
----------  ------  ---
2013-01-01  Item 1   10
2013-01-01  Item 2   15
2013-02-01  Item 1   10
2013-02-01  Item 2   15
2013-03-01  Item 1    5
2013-03-01  Item 2   15

Great! With little tweaking it is exactly what I was after! Thanks mate! — user2139366, Oct 18 '13 at 23:37

Access SQL - Get last available value

2 Answers2