MS Access query that includes a join to itself (using a subquery) and reports values that do not exist (null values)

Question

I have the following table named PRICES:

VERTEXID	TIMEID	PRICE
6001	20191231	104.3
6001	20181231	115.3
6001	20171231	138.3
6001	20161231	122.3
6002	20191231	102.3
6002	20190931	123.3
6002	20190631	167.3
6002	20181231	202.3
6002	20171231	402.3
6002	20170931	162.3

I want to write a query in MS Access sql that will find the top 5 TIMEIDs in the table ordered in a descending order and then will return the corresponding values for each VERTEXID I select in the WHERE clause. If the record does not exist I want the query to return the previous value that exists (this is preferred) or null. Here's an example of the output I need:

VERTEXID	TIMEID	PRICE
6001	20191231	104.3
6001	20190931	104.3 (or null)
6001	20190631	104.3 (or null)
6001	20181231	115.3
6001	20171231	138.3
6002	20191231	102.3
6002	20190931	123.3
6002	20190631	167.3
6002	20181231	202.3
6002	20171231	402.3

What I have so far is this:

SELECT P1.VERTEXID, P1.TIMEID, P1.PRICE
FROM PRICES P1
RIGHT JOIN (
                        SELECT DISTINCT TOP 5 P2.TIMEID
                        FROM   PRICES P2
                        WHERE   P2.TIMEID <= 20191231
                        ORDER BY P2.TIMEID DESC
                        ) P3
            ON P3.TIMEID = P1.TIMEID
WHERE P1.VERTEXID IN (6001,6002) AND P1.TIMEID <= 20191231
ORDER BY P1.VERTEXID, P1.TIMEID DESC

but it will not output what I need. Any help is greatly appreciated!

Answer 1

There you go:

SELECT J1.VERTEXID, J1.TIMEID, P4.PRICE
FROM (
    SELECT DISTINCT P1.VERTEXID, P3.TIMEID
    FROM PRICES P1,
            (SELECT DISTINCT TOP 5 P2.TIMEID
            FROM PRICES P2
            WHERE P2.TIMEID <= 20191231
            ORDER BY P2.TIMEID DESC
            ) P3
    ) J1
LEFT JOIN PRICES P4 ON J1.VERTEXID = P4.VERTEXID AND J1.TIMEID = P4.TIMEID
ORDER BY J1.VERTEXID, J1.TIMEID DESC

You have to add your filter conditions, as you need.

Basically you have to do the following:

• do a CROSS JOIN to get all combinations of VERTEXID and TIMEID (that's J1)
• then LEFT JOIN this result to the table itself and get the according PRICE values

Missing prices will be NULL.