将日期转换为“月”在查询中 [英] Converting Dates to "Month" in a Query
问题描述
大家好，首先让我先说，我非常喜欢这个网站。我有一个访问数据库，记录键入包括日期字段。即3/4 / 07,3 / 07 / 07,4 / 10 / 07,4 / 18 / 07,5 / 4/07 ......并使用要求开始和结束日期的参数查询进行查询。即2007年1月1日，2007年5月1日。这些结果的形式是那个时期的总数。
这一切都运作良好。现在我被要求在报告上创建一个条形图，该报告将显示逐月进展，即1月至6月。实现的效果：
Jan 2月 3月 4月 5月
40 30 28 20 25
然后我可以使用条形图来绘制数字，并将月份用作X。轴。
如果用户希望图表显示他们选择的月份范围，例如3/4，如何将日期字段转换为参数查询中的月份名称/ 07,3 / 20 / 07,3 / 27/07以3月的形式出现？
我对SQL的了解有限，所以请善待并详细说明您的回复。
非常感谢您提前。
Nick
大家好，首先让我先说，我非常喜欢这个网站。我有一个访问数据库，记录键入包括日期字段。即3/4 / 07,3 / 07 / 07,4 / 10 / 07,4 / 18 / 07,5 / 4/07 ......并使用要求开始和结束日期的参数查询进行查询。即2007年1月1日，2007年5月1日。这些结果的形式是那个时期的总数。
这一切都运作良好。现在我被要求在报告上创建一个条形图，该报告将显示逐月进展，即1月至6月。实现的效果：
Jan 2月 3月 4月 5月
40 30 28 20 25
然后我可以使用条形图来绘制数字，并将月份用作X。轴。
如果用户希望图表显示他们选择的月份范围，例如3/4，如何将日期字段转换为参数查询中的月份名称/ 07,3 / 20 / 07,3 / 27/07以3月的形式出现？
我对SQL的了解有限，所以请善待并详细说明您的回复。
非常感谢您提前。
Nick
嗨尼克，
如果您发布当前使用的查询的完整SQL，我们可以使用Month（）或MonthName（）函数调整它以显示月份。
Mary
嗨Mary，下面是三个查询。第一个和第二个我创建了两个不同表中的总和，然后第三个给了我前两个总和。这是我需要使用从日期字段转换的月份来查询的总数。
SELECT
总和（[环境回合]。[浴室扶手]） AS [SumOfBathroom扶手]，
总和（[环境回合]。[紧急拉站]）AS [SumOfEmergency Pull Stations]，
[SumOfBathroom扶手] + [SumOfEmergency Pull站点] AS Total_A
来自[环境回合]
WHERE（（（[环境回合]。日期）[输入开始日期]和[输入结束日期]之间）） ;
SELECT
总和（[环境回合医疗设备]。[设备维修良好]）AS [SumOfEquipment in good repair]，
总和（[环境回合医疗设备]。[工作人员知道如何申请设备维修]）AS [SumOfStaff知道如何申请设备维修]，
总和（[环境回合医疗设备] 。[垂直百叶窗/窗帘处理维修良好]）AS [SumOfVertical百叶窗/窗户处理维修良好]，
[SumOfEquipment in good repair] + [SumOfStaff知道如何申请设备维修] + [SumOfVertical百叶窗/窗帘处理维修良好] AS Total_B
来自[环境回合医疗设备]
WHERE（（（[环境回合医疗设备]。日期）[输入开始日期]和[输入结束日期]之间））;
SELECT TotalGenRepair_A.Total_A，TotalGenRepair_B .Total_B，TotalGenRepair_A.Total_A + TotalGenRepair_B.Total_B AS GrandTotalGenRepair
来自TotalGenRepair_A，TotalGenRepair_B;
当我运行第三个查询时，它会提示输入开始日期和结束日期并且总计另外两个。这部分工作正常，虽然我可能花了很长时间来做这件事。
谢谢，
尼克
留下它缺口。我会在一段时间内完成它。
Hi All, First let me start by saying, I absolutely love this site. I have an access database that records are keyed in including a date field. i.e. 3/4/07, 3/20/07, 4/10/07,4/18/07, 5/4/07... and queried using a parameter query that asks for a Start and End date. i.e. 1/1/2007, 5/1/2007. These results are in the form of number Totals for that period.
This all works well. Now I have been asked to create a bar graph on a report that will show Month to Month progress, i.e Jan - June. Something to the effect of:
Jan Feb Mar Apr May
40 30 28 20 25
Then I can use a bar graph to graph the numbers and use the months as the "X" axis.
Any idea on how to convert date fields to Month names in a parameter query, if the user wants a graph to show a range of months that they choose, i.e 3/4/07, 3/20/07, 3/27/07 to come up as "March"?
I have limited knowledge of SQL, so please be kind and detail your responses.
Thank you very much in advance.
Nick
Hi All, First let me start by saying, I absolutely love this site. I have an access database that records are keyed in including a date field. i.e. 3/4/07, 3/20/07, 4/10/07,4/18/07, 5/4/07... and queried using a parameter query that asks for a Start and End date. i.e. 1/1/2007, 5/1/2007. These results are in the form of number Totals for that period.
This all works well. Now I have been asked to create a bar graph on a report that will show Month to Month progress, i.e Jan - June. Something to the effect of:
Jan Feb Mar Apr May
40 30 28 20 25
Then I can use a bar graph to graph the numbers and use the months as the "X" axis.
Any idea on how to convert date fields to Month names in a parameter query, if the user wants a graph to show a range of months that they choose, i.e 3/4/07, 3/20/07, 3/27/07 to come up as "March"?
I have limited knowledge of SQL, so please be kind and detail your responses.
Thank you very much in advance.
NickHi Nick,
If you post the full SQL of the query you are currently using we can adapt it to show the months using the Month() or MonthName() functions.
Mary
Hi Mary, below are three queries. The first and second I created to sum the totals from two different tables, and then the third gives me a total of the first two. This is the total that I need to query using Months converted from the Date field.
SELECT
Sum([Environmental Rounds].[Bathroom handrails]) AS [SumOfBathroom handrails],
Sum([Environmental Rounds].[Emergency Pull Stations]) AS [SumOfEmergency Pull Stations],
[SumOfBathroom handrails]+[SumOfEmergency Pull Stations] AS Total_A
FROM [Environmental Rounds]
WHERE ((([Environmental Rounds].Date) Between [Enter Start Date] And [Enter End Date]));
SELECT
Sum([Environmental Rounds Medical Equipment].[Equipment in good repair]) AS [SumOfEquipment in good repair],
Sum([Environmental Rounds Medical Equipment].[Staff know how to request equipment repair]) AS [SumOfStaff know how to request equipment repair],
Sum([Environmental Rounds Medical Equipment].[Vertical blinds/window treatments in good repair]) AS [SumOfVertical blinds/window treatments in good repair],
[SumOfEquipment in good repair]+[SumOfStaff know how to request equipment repair]+[SumOfVertical blinds/window treatments in good repair] AS Total_B
FROM [Environmental Rounds Medical Equipment]
WHERE ((([Environmental Rounds Medical Equipment].Date) Between [Enter Start Date] And [Enter End Date]));
SELECT TotalGenRepair_A.Total_A, TotalGenRepair_B.Total_B, TotalGenRepair_A.Total_A+TotalGenRepair_B.Total_B AS GrandTotalGenRepair
FROM TotalGenRepair_A, TotalGenRepair_B;
When I run the third Query, it prompts for the "Start Date", and "End Date" and totals the other two. This part works fine, although I probably took the long way around doing this.
Thanks,
Nick
Leave it with me Nick. I''ll get to it in a while.
这篇关于将日期转换为“月”在查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
