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:
WidgetID DateProduced WidgetWeekID
------- ------------ ----
1 1/2/11
2 1/16/11
3 1/31/11
WidgetWeekID comes from another table called tblWidgetWeeks:
WidgetWeekID StartDate EndDate
------------ -------- --------
1 12/29/11 1/5/11
2 1/6/11 1/12/11
3 1/13/11 1/20/11
4 1/21/11 1/28/11
5 1/29/11 2/5/11
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:
UPDATE tblWidgets AS w
INNER JOIN tblWidgetWeeks AS wwk
ON w.[Date] >= wwk.StartDate AND w.[Date] <= wwk.EndDate
SET w.WidgetWeekID = [wwk].[WidgetWeekID];
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:
WidgetID DateProduced WidgetWeekID
------- ------------ ----
1 1/2/11 1
2 1/16/11 3
3 1/31/11 5
What other unusual joins have you done? Please comment below. Thanks Ben!
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])
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.
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
If you think you can spare some time for the Access cause, why not join the star-studded MVP roster here? http://tech.groups.yahoo.com/group/MS_Access_Professionals/
It’s a cosy, friendly group 🙂
Great group! Pat Wood and you both recommended it to me. Thanks!
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];
?
Yes I did! Thanks for pointing it out. Post updated.