Go to Top

Matching records using a date range

The other day I asked Ben to help me with a thorny problem, I needed to match records in one table with records in another table using a date range. Here’s the table I needed to update, called tblWidgets:

WidgetWeekID comes from another table called tblWidgetWeeks:

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

Click for a free quote.

I needed a query that would find DateProduced between StartDate and EndDate and update WidgetWeekID with the right number. Here’s the solution he came up with:

What’s brilliant about Ben’s solution is the join on the date range, a far better solution than using code. Once I ran the query the destination table looks like this:

What other unusual joins have you done? Please comment below. Thanks Ben!

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He’s a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

7 Responses to "Matching records using a date range"

  • Mark Justice
    November 19, 2015 - 11:14 pm Reply

    I wrote this in SQL view. It won’t represent in Design view, which is ok, but when I run it, it takes forever and returned inaccurate results. I then went back into SQL view and my coding was completely gone.
    Here is the statement:
    Inner join [Date Table] on ([MC Daily Rslts].[Activity Date] >= [Date Table].[Time Period Start] and [MC Daily Rslts].[Activity Date] <=[Date Table].[Time Period End] and isnull( [Date Table].[PP])

  • Gilad
    July 14, 2012 - 9:40 am Reply

    Thanks for the interesting post.
    Wouldn’t the same result also be possible using criteria in the Where clause instead of in the Join?
    That way you could also see it visually in the query grid.

    • Juan Soto
      July 16, 2012 - 9:46 pm Reply

      That’s the beauty of queries: many people can come up with many different solutions, I would welcome a version from you that uses the where clause.

      Thanks!
      Juan

    • Juan Soto
      October 11, 2011 - 5:09 pm Reply

      Great group! Pat Wood and you both recommended it to me. Thanks!

  • grovelli
    October 11, 2011 - 2:31 pm Reply

    Hi Juan,
    Did you mean to write
    UPDATE tblWidgets AS w
    INNER JOIN tblWidgetWeeks AS wwk
    ON w.[Date] >= wwk.StartDate AND w.[Date] <= wwk.EndDate
    SET w.WidgetWeekID = [wwk].[WidgetWeekID];
    ?

    • Juan Soto
      October 11, 2011 - 5:02 pm Reply

      Yes I did! Thanks for pointing it out. Post updated.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]