Skip to content
Advertisement

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

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:

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

Advertisement

Answer

There you go:

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement