【译文】在一个日期范围内匹配记录
时 间:2012-04-21 15:08:52
作 者:周芳 ID:24526 城市:上海
摘 要:将一个表中的记录和另一个有日期范围的表相匹配
正 文:
原作者:Juan Soto 翻译:周芳
一天,我叫Ben帮我解决一个棘手的问题,我需要将一个表中的记录和另一个有日期范围的表相匹配。这是我要更新的表,表名为tblWidgets.
WidgetID DateProduced WidgetWeekID
------- ------------ ------------
1 1/2/11
2 1/16/11
3 1/31/11
WidgetWeekID是另一个表名为tblWidgetWeeks中的字段:
WidgetWeekID StartDate EndDate
------------ -------- -------
1 12/29/10 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
我需要一个查询来找到StartDate和EndDate之间的DateProduced ,然后用正确的编号更新WidgetWeekID。这是他想出的解决方案:
Update tblWidgets AS w
INNER JOIN tblWidgetWeeks AS wwk
ON w.[DateProduced] >= wwk.StartDate AND w.[DateProduced ] <= wwk.EndDate
SET w.WidgetWeekID = [wwk].[WidgetWeekID];
Ben用连接日期范围来解决的方法出色在哪呢,其实它是一个远比使用代码更好的解决办法。运行查询后显示是这样的:
WidgetID DateProduced WidgetWeekID
------- ------------ ------------
1 1/2/11 1
2 1/16/11 3
3 1/31/11 5
你还有什么其他不寻常的想法可以加入吗?下面请赐教。谢谢Ben。
另附示例:
【原文】
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:
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/10 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.[DateProduced] >= wwk.StartDate AND w.[DateProduced ] <= 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!
相关类似示例:
Access软件网QQ交流群 (群号:483923997) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access窗体导出PDF】...(04.08)
- 【Access窗体导出PDF】...(04.07)
- Access两种方式实现即时更...(03.01)
- Access隐藏与显示lacc...(01.12)
- 【Access高效办公】将每个...(12.23)
- Access21点游戏源代码(12.13)
- 【Access窗体导出Exce...(11.15)
- 【Access开发】Acces...(11.14)
- 通过Access宏录入数据到选...(11.10)
学习心得
最新文章
- 从另一个ACCESS数据库批量导入...(04.29)
- Access日期格式的数据导出Ex...(04.28)
- ACCESS精华集锦资料.CHM(04.25)
- Access VBA语句If Me...(04.24)
- 【Access修改记录示例】编辑选...(04.22)
- 【Access表名称命名建议】将A...(04.20)
- Access学习笔记--用Acce...(04.19)
- 【Access重复项查询示例】将A...(04.17)
- Access快速开发平台企业版--...(04.16)
- 【Access模块示例】通过模块代...(04.15)