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!