从Access中的“日期时间"字段中选择时间范围 [英] Selecting Time ranges from Date Time fields in Access

问题描述

我有一个表，其中包含报告及其创建的日期/时间.我想创建一个图表，以统计在两个时间段:8:00 AM-9:00AM和1:00 PM-2:00PM之间的十分钟间隔内创建的报告的数量.

I have a table containing reports and the date/time they were created. I'd like to create a graph to tally the number of reports created during intervals of ten minutes between two time periods: 8:00AM-9:00AM and 1:00PM-2:00PM.

这是我要运行的查询示例:

Here's an example of a query I'd like to run:

SELECT
s.StudyStartDateTime AS "8:00 - 8:10",
s.StudyStartDateTime AS "8:10 - 8:20",
s.StudyStartDateTime AS "8:20 - 8:30",
s.StudyStartDateTime AS "8:30 - 8:40",
s.StudyStartDateTime AS "8:40 - 8:50",
s.StudyStartDateTime AS "8:50 - 9:00",
s.StudyStartDateTime AS "1:00 - 1:10",
s.StudyStartDateTime AS "1:10 - 1:20",
s.StudyStartDateTime AS "1:20 - 1:30",
s.StudyStartDateTime AS "1:30 - 1:40",
s.StudyStartDateTime AS "1:40 - 1:50",
s.StudyStartDateTime AS "1:50 - 2:00"
FROM
    dbo_Study_ViewX211_Rpt AS s
WHERE FORMAT("8:00 - 8:10", 'hh:mm:ss') BETWEEN "08:00:00" AND "08:09:59"
AND FORMAT("8:10 - 8:20", 'hh:mm:ss') BETWEEN "08:10:00" AND "08:19:59"
AND FORMAT("8:20 - 8:30", 'hh:mm:ss') BETWEEN "08:20:00" AND "08:29:59"
AND FORMAT("8:30 - 8:40", 'hh:mm:ss') BETWEEN "08:30:00" AND "08:39:59"
AND FORMAT("8:40 - 8:50", 'hh:mm:ss') BETWEEN "08:40:00" AND "08:49:59"
AND FORMAT("8:50 - 9:00", 'hh:mm:ss') BETWEEN "08:50:00" AND "08:59:59"
AND FORMAT("1:00 - 1:10", 'hh:mm:ss') BETWEEN "01:00:00" AND "01:09:59"
AND FORMAT("1:10 - 1:20", 'hh:mm:ss') BETWEEN "01:10:00" AND "01:19:59"
AND FORMAT("1:20 - 1:30", 'hh:mm:ss') BETWEEN "01:20:00" AND "01:29:59"
AND FORMAT("1:30 - 1:40", 'hh:mm:ss') BETWEEN "01:30:00" AND "01:39:59"
AND FORMAT("1:40 - 1:50", 'hh:mm:ss') BETWEEN "01:40:00" AND "01:49:59"
AND FORMAT("1:50 - 2:00", 'hh:mm:ss') BETWEEN "01:50:00" AND "01:59:59"

不幸的是，似乎Access无法告诉我我想在FORMAT函数中格式化的字段是指查询中同名的字段.在上述情况下，我认为这是因为Access将其视为字符串文字.但是，这也不起作用:

Unfortunately, it seems that Access isn't able to tell that the field I'd like to format within the FORMAT function refers to the field of the same name in the query. In the above case, I assume this is because Access is treating this as a string literal. However, this doesn't work, either:

SELECT
    s.StudyStartDateTime AS first
FROM
    dbo_Study_ViewX211_Rpt AS s
WHERE FORMAT(first, 'hh:mm:ss') BETWEEN "08:00:00" AND "08:09:59"

Access提示我输入"first"的值.

Access is prompting me for the value for 'first'.

我觉得我会以错误的方式开始.谁能指出我正确的方向?

I feel like I'm going about this the wrong way to begin with. Can anyone point me in the right direction?

更多信息:我正在通过ODBC使用Jet引擎，但是数据实际上存储在SQL Server 2005中，因此如果有必要，我想我可以编写一个传递查询.但是，理想情况下，我希望它可以在Access中工作.

More info: I'm using the Jet engine through ODBC, but the data is actually stored in SQL Server 2005, so if necessary I suppose I could write a pass-through query. Ideally, however, I'd like it to work in Access.

要清楚，以下确实起作用:

To be clear, the following DOES work:

SELECT s.StudyStartDateTime AS first
FROM dbo_Study_ViewX211_Rpt AS s
WHERE FORMAT(StudyStartDateTime,'hh:mm:ss') Between "08:00:00" And "08:09:59";

但是，这不允许我在多个条件下多次选择同一字段.

This, however, doesn't allow me to select the same field multiple times under multiple conditions.

第一个示例位于 http://office.microsoft.com /en-us/access/HA012288391033.aspx#examples 建议将字段的别名包装在方括号中是引用记录源的正确方法，但以下内容仍会提示输入'首先":

The first example at http://office.microsoft.com/en-us/access/HA012288391033.aspx#examples suggests that wrapping the alias of the field in square braces is the correct way to reference a record source, but the following still results in a prompt for the value of 'first':

SELECT s.StudyStartDateTime AS first
FROM dbo_Study_ViewX211_Rpt AS s
WHERE FORMAT([first],'hh:mm:ss') Between "08:00:00" And "08:09:59";

编辑II:

一些记录的StudyStartDateTime内容示例:

An example of the contents of StudyStartDateTime of a few records:

29/11/2007 8:06:00 AM
30/11/2007 8:09:01 AM
05/12/2007 8:06:51 AM
06/12/2007 8:07:21 AM

Access将此字段视为日期/时间"数据类型.

Access sees this field as a "Date/Time" datatype.

我的最终目标是为每个间隔绘制统计图.我认为最简单的方法是选择每个日期/时间作为不同的别名.例如:

My end goal is to graph the tally for each interval. I think the easiest way of doing this would be to simply select each date/time as a different alias. For instance:

8:00-8:10   8:10-8:20   8:20:8:30   ...
=======================================
8:01        
8:03
            8:15
                        8:23
                        8:28
                        8:28
...

推荐答案

您遇到的问题是，在同一个查询中，访问没有按字段别名来引用字段.这意味着您不能在select子句中分配别名，而不能在where子句中使用它.

The problem you are having is because access doesn't reference fields by their aliases within the same query. Meaning you can't assign an alias in the select clause and use it in the where clause.

对于问题的第一部分，您可以尝试类似的操作

As for the first part of your question, you can try something like this

    SELECT 
        sum(iif(FORMAT(s.StudyStartDateTime,'hh:mm:ss') 
                BETWEEN "08:00:00" AND "08:09:59",1,0)) as "8:00 - 8:10",
        sum(iif(FORMAT(s.StudyStartDateTime,'hh:mm:ss') 
                BETWEEN "08:10:00" AND "08:19:59",1,0)) as "8:10 - 8:20",
        ...
    FROM dbo_Study_ViewX211_Rpt AS s

这将使您每10分钟获得一次提示.

This will give you a tally for each 10 minute period.

这篇关于从Access中的“日期时间"字段中选择时间范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！