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:

MS Access Consulting

WidgetWeekID comes from another table called tblWidgetWeeks:

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 the Author:

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.


  1. Mark Justice November 19, 2015 at 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])

  2. Gilad July 14, 2012 at 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 at 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.


  3. grovelli October 11, 2011 at 3:17 pm - Reply

    If you think you can spare some time for the Access cause, why not join the star-studded MVP roster here?
    It’s a cosy, friendly group 🙂

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

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

  4. grovelli October 11, 2011 at 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 at 5:02 pm - Reply

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

Leave A Comment


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.