以半小时为间隔而不是1小时报告？ [英] Report in 1/2 hour intervals instead of 1 hour?

问题描述

你好，


需要一些Access 2000查询的帮助。


我的查询是基于一个表，它是一个日志打电话给我的公司。

我使用的是用户输入的日期范围

（[StartDate]和[EndDate]之间的日期）

和CountOfRefNumber（RefNumber是PK）。

目前我使用的是：

LogTimes：格式（TimeSerial（小时（[时间]），0,0），短时间） &安培; " - " &安培;格式（TimeSerial（小时（[时间]），59,0），短时间）

可以按小时缩短通话次数，因此我的查询如下所示：


Date LogTimes CountOfRef Number

14/01/2008 08：00-08：59 1

14/01/2008 09:00 -09：59 3

14/01/2008 10：00-10：59 4

14/01/2008 11：00-11：59 1 >
14/01/2008 13：00-13：59 5

14/01/2008 14：00-14：59 1

15/01 / 2008 08：00-08：59 2

15/01/2008 11：00-11：59 1

15/01/2008 14：00-14： 59 1

16/01/2008 09：00-09：59 1

17/01/2008 10：00-10：59 1


这一切都很好，但现在我需要每半小时分解一次（例如08：00-08：29,08：30-08：59,09：00-09 ：30，等等。）我不知道怎么做。

感谢任何帮助。


非常感谢，

Olly

Hello,

Need some help with an Access 2000 query.

My query is based on a table which is a log of calls to my company.
I am using a date range entered by the user
(Date Between [StartDate] And [EndDate])
and CountOfRefNumber (RefNumber is the PK).
Currently I am using this:
LogTimes: Format(TimeSerial(Hour([Time]),0,0),"Short Time") & "-" & Format(TimeSerial(Hour([Time]),59,0),"Short Time")
to break the number of calls down by hour, so my query looks like this:

Date LogTimes CountOfRef Number
14/01/2008 08:00-08:59 1
14/01/2008 09:00-09:59 3
14/01/2008 10:00-10:59 4
14/01/2008 11:00-11:59 1
14/01/2008 13:00-13:59 5
14/01/2008 14:00-14:59 1
15/01/2008 08:00-08:59 2
15/01/2008 11:00-11:59 1
15/01/2008 14:00-14:59 1
16/01/2008 09:00-09:59 1
17/01/2008 10:00-10:59 1

This is all well and good, but now I need to break it down by 1/2 hour intervals (e.g. 08:00-08:29, 08:30-08:59, 09:00-09:30, etc.) and I don''t know how.
Any help gratefully received.

Many thanks,

Olly

推荐答案

由于我有截止日期，真的需要一些帮助...


我现在还要包括那些没有记录通话的时间间隔，所以它看起来像这样：


25/01/2008

TIME LOGGED

00:00 0

00:30 0

01:00 0

01： 30 1

02:00 0

02:30 0

03:00 0

03:30 0

04:00 0

04:30 0

05:00 1

05:30 1

06:00 0

06:30 0

07:00 0

07:30 0

08:00 0

08:30 0

09:00 1

09:30 1

10:00 5

10:30 0

11:00 1

11:30 1

12:00 0

12:30 0

13:00 0

13:30 3

14： 00 1

14:30 1

15:00 1

15:30 5

16:00 1

16:30 6

17:00 2

17:30 1

18:00 2

18:30 0

19:00 1

19:30 5

20:00 1

20:30 1

21:00 0

21:30 1

22:00 2

22:30 0

23:00 0

23:30 0


请帮忙。

Really need some help with this as I have a deadline...

I would now also like to include those time intervals for which there are no calls logged, so it would look this this:

25/01/2008
TIME LOGGED
00:00 0
00:30 0
01:00 0
01:30 1
02:00 0
02:30 0
03:00 0
03:30 0
04:00 0
04:30 0
05:00 1
05:30 1
06:00 0
06:30 0
07:00 0
07:30 0
08:00 0
08:30 0
09:00 1
09:30 1
10:00 5
10:30 0
11:00 1
11:30 1
12:00 0
12:30 0
13:00 0
13:30 3
14:00 1
14:30 1
15:00 1
15:30 5
16:00 1
16:30 6
17:00 2
17:30 1
18:00 2
18:30 0
19:00 1
19:30 5
20:00 1
20:30 1
21:00 0
21:30 1
22:00 2
22:30 0
23:00 0
23:30 0

Please help.

创建一个表来保存所需的所有时间边界，最少两列。使用您要报告的所有时间填充表格。作为一个快速测试，我创建了一个名为Times with From和To列的表，它们都键入日期时间，格式化为短时间，并尝试了一些测试值：


From To

08:00 08:29

08:30 08:59

09:00 09:29

09： 30 09:59

将此表添加到您的基本查询（在计算之前具有调用日志的查询）不在任何字段上加入 。将From和To列添加到基本查询中，然后在[from]和[to]''之间添加''作为原始查询的日志时间字段中的条件。在我使用的测试数据集中，您可以看到以下结果：


年份参考值时间从到期

2008 1首先09:28:00 09:00 09:29

2008 2 Second 09:40:00 09:30 09:59

2008 4 Fourth 08:56:00 08:30 08:59 >
2008 5 Fifth 08:35:00 08:30 08:59

SELECT Test.Year，Test.Ref，Test.Val，Test.Time，Times。来自，Times.To

来自测试，时间

WHERE（（（Test.Time）在[From]和[To]之间）;


现在可以使用它来计算每个时间段的计数。在我的测试版中，这给了


来自N

08:30 2

09:00 1

09:30 1

SELECT [测试1]。从，计数（[测试1]。起）AS N

来自[测试1]

GROUP BY [测试1]。来自


由于您要报告所有时间段，最终查询会将计数查询连接回时间，这次使用从时间表到计数查询的左连接，以包括所有时间值，但仅包括存在这些值的计数。这将为时间段中的无计数提供空值，因此为简单起见，我使用内联IIF来测试null并返回0：


来自List Val

08:00 0

08:30 2

09:00 1

09:30 1

SELECT Times.From，IIf（IsNull（[N]），0，[N]）AS [List Val]

FROM Times LEFT JOIN [Test 2] ON Times.From = [测试2]。来自


描述比做的时间更长，并且具有如下优势：如果您需要在将来某个时间更改时间间隔在Times表中更改它们，所有相关的查询和报告都会跟进。


希望这会有所帮助


问候

Stewart

Create a table to hold all the time boundaries you require, minimum two columns. Populate the table with all of the times you wish to report. As a quick test I created a table called Times with From and To columns, both type date-time, formatted as short time, and tried a few test values:

From To
08:00 08:29
08:30 08:59
09:00 09:29
09:30 09:59

Add this table to your base query (the one with the log of calls before you do the count) without joining it on any field. Add the From and To columns to your base query, then add ''between [from] and [to]'' as the criteria in the log time field of your original query. In the test dataset I used you can see the result below:

Year Ref Val Time From To
2008 1 First 09:28:00 09:00 09:29
2008 2 Second 09:40:00 09:30 09:59
2008 4 Fourth 08:56:00 08:30 08:59
2008 5 Fifth 08:35:00 08:30 08:59

SELECT Test.Year, Test.Ref, Test.Val, Test.Time, Times.From, Times.To
FROM Test, Times
WHERE (((Test.Time) Between [From] And [To]));

You can now use this to compute counts for each time period. In my test version this gives

From N
08:30 2
09:00 1
09:30 1

SELECT [Test 1].From, Count([Test 1].From) AS N
FROM [Test 1]
GROUP BY [Test 1].From;

As you want to report all time periods, a final query joins the counts query back to the times, this time using a left join from the times table to the counts query to include all the time values but only the counts where these are present. This will give nulls for no counts in the time period, so for simplicity I use an inline IIF to test for null and return a 0 instead:

From List Val
08:00 0
08:30 2
09:00 1
09:30 1

SELECT Times.From, IIf(IsNull([N]),0,[N]) AS[List Val]
FROM Times LEFT JOIN [Test 2] ON Times.From = [Test 2].From;

It takes longer to describe than to do, and has the advantage that if you need to change the time intervals at some time in the future you just change them in the Times table and all dependent queries and reports follow suit.

Hope this helps

Regards

Stewart

忘记提及 - 对于最终的加入，您需要包括日期和时间（而不仅仅是时间），否则会有每个时间段的N行而不是1，其中N是报表中的日期数 - 汽车两张表的tesian产品。如果您可以提供您使用的计数查询的副本，我可以使用它来查询日期，然后将其加入到时间查询中，以提供符合每个日期和时间段1行的需求的日期时间查询。


干杯


斯图尔特

Forgot to mention - for the final join you will need to include the date along with the times (and not just the times on their own) otherwise there will be N rows for each time period instead of 1, where N is the number of dates in your report - the cartesian product of the two tables. If you can provide a copy of the count query you use I can use this to query the dates then join this to the times query to provide a date-time query that fits the need for 1 row per date and time period.

Cheers

Stewart

这篇关于以半小时为间隔而不是1小时报告？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！