Filling out a date range with values based on most recent

Posted on 2014-12-01
Last Modified: 2014-12-01
I have a table like this:
Date     Value
1/1           3
5/1           5
7/1           8

And another like this:

I want to combine these tables into this
Date  Value
1/1    3
2/1    3
3/1    3
4/1    3
5/1    5
6/1    5
7/1    8
8/1    8

So in other words, it's like a left join from tblDates on tblValues where nulls yield that last (i.e. most recent) change value.  Restated, I want to know what change value is in effect for each date in the list.  If nothing matches, take the most recent.

This could be done with Domain functions (like DLast), but that's very cumbersome.  Is there a better way?
Question by:shacho
  • 3
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473287
It may be cumbersome, but it's probably the best way.

If this was SQL Server, then you could use the LAG function, but that's not present in Access.

Outside of DLast, you would have to do some JOINs with non-equal comparisons, and if you have a big table, that would get slow - so Domain functions are probably the best for Access.
LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40473343
Here's the SQL for the query with non-equal comparisons. It might not be as slow as I was fearing:

SELECT qryRelevantDate.Date, tblChanges.Value
FROM (SELECT tblDates.Date, Max(tblChanges.Date) AS MapDate
FROM tblDates INNER JOIN tblChanges ON tblDates.Date >= tblChanges.Date
GROUP BY tblDates.Date) as qryRelevantDate INNER JOIN tblChanges ON qryRelevantDate.MapDate = tblChanges.Date;

Open in new window

LVL 24

Expert Comment

by:Phillip Burton
ID: 40473344
This is the output:

Date	Value
01/01/2014	3
02/01/2014	3
03/01/2014	3
04/01/2014	3
05/01/2014	5
06/01/2014	5
07/01/2014	8
08/01/2014	8

Open in new window

LVL 49

Expert Comment

by:Gustav Brock
ID: 40473443
Why not pick the values directly:

    (Select Max(tblChanges.Value)
    From tblChanges
    Where tblChanges.Date <= tblDates.Date) As [Value]


Author Comment

ID: 40473920
Yep, that does the trick, Phillip.
Thanks all for your comments!


Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now