按时间段对记录进行分组 [英] Grouping records by time period

问题描述

大家好，


我有一张桌子，上面有日期/时间和名字。如果我有一个记录，例如约翰作为名称，时间是12:00，我可以在下一个小时用N / A替换任何进一步的John实例吗？我需要的只是下一个小时的每个名字的第一个实例。然后，我可以通过排除N / A来缩小以缩小查询中的记录数量。


12:00 John

12:01 Simon

12:03约翰（需要用N / A代替）

12:04约翰（需要用N / A代替）

12 ：05杰克

13:03约翰（自上一个名字实例以来可以保持超过1小时）

13:05约翰（需要被替换为N / A从13:03开始不到1小时）


上面的查询将是4条记录，而不是7条。


我到目前为止所做的一切都是能够过滤后来的名字，但如果中间有任何记录则不能过滤


MyName：IIf（DLookUp（" [ Name1]"，" Name_Table"，" [ID] ="& [ID] -1）= [Name1]，N / A，[Name1]）


这样可行，但显然不会过滤我需要的所有记录。如果有人有任何建议，我将不胜感激。


提前致谢。


Gareth

Hi all,

I have a table with a date/time and a name. If I have a record for example with John as the name and the time is 12:00, can I replace any further instances of John with N/A for the next hour? All I need is the 1st instance of each name for the following hour. I can then filter by excluding N/A to shrink the amount of records in the query.

12:00 John
12:01 Simon
12:03 John (needs to be replaced by N/A)
12:04 John (needs to be replaced by N/A)
12:05 Jack
13:03 John (This can remain as more than 1 hour since the previous instance of the name)
13:05 John (needs to be replaced by N/A as less than 1 hour since the one at 13:03)

The above query would then be 4 records in total instead of 7.

All I have done so far is being able to filter names that are immediately afterwards but not if there any record in the middle

MyName: IIf(DLookUp("[Name1]","Name_Table","[ID]=" & [ID]-1)=[Name1],"N/A",[Name1])

This works ok but it obviously does not filter all the records I need. Would appreciate if anyone has any suggestions if this is possible.

Thanks in advance.

Gareth

推荐答案

Gareth，


如果我们理解你在更大的计划中想要做什么，也许会有所帮助。我认为有办法做你想做的事情，但如果我们理解你为什么需要这样的分组，可能会有更好的方法。除了将名称更改为N / A之外，可能有更好的方法来执行您要执行的操作。

Gareth,

It perhaps would be helpful if we understood what you are trying to do in the bigger scheme of things. I think there are ways of doing what you want to do, but there may be better ways of doing this if we understood why you needed such a grouping. There may be better ways of doing what you are trying to do, besides changing the names to "N/A".

感谢您的回复。


它基本上与Overtime有关。如果有人被要求解决问题，则将其归类为标注。如果他们在这个初始呼叫的一小时内得到几个呼叫，那么它被归类为相同的问题/标注。如果在一小时后收到任何进一步的呼叫，则该过程再次开始，并且这些新呼叫中的第一个被归类为标注。


小时数字不是一成不变的，但是一次功能有效，这个数字总是可以改变的。


谢谢

Thanks for replying.

Its essentially to do with Overtime. If someone has been called for a problem, it is classed as a callout. If they get a few more calls within an hour of this initial call, then it is classed as the same problem/callout. If any further calls are received after an hour, the process starts again and the first of these new calls is classed as a callout.

The hour figure is not set in stone, however once the functionality works, this figure can always be changed.

Thanks

处理这种情况的一种方法，可能是检查状态技术人员最近的电话，看看是否已超过一个小时。如果没有，那么就不要添加新记录。但是，从管理的角度来看，我认为你会想要跟踪通话次数以及实际时间。


我对如何工作有一些想法，但我还需要知道如何使用这些信息，例如，它是否会打印在报告上。您 需要 了解多个电话，但只是根据时间跨度计算薪酬吗？我问的原因是，在表中维护技术人员的名称可能是有价值的，但是以不同的方式识别您想要工作的记录。请记住，通过将名称更改为N / A，在您的示例中，它们都是John，但也可以是Jack或Simon。


原谅我提出更多问题，但我想确保我们引导您走 好 路径，而不仅仅是让您找到解决方案的路径。

One method for dealing with this situation, might be to check the status of the technician''s most recent call out and see if it has been more than an hour since. If not, then don''t add a new record. However, from a management standpoint, I would think you would want to keep track of the number of calls, as well as the actual time.

I have some ideas on how to work this, but I would also need to know how this information is going to be used, such as, is it just going to be printed on a report. Do you need to know about the multiple calls, but just calcluate pay based on the time span? The reason I ask, is that there may be value in maintaining the name of the technician in your Table, but identifying the records you want to work from in a different way. Remember, by changing the name to "N/A", in your example, they are all "John", but could just as well be Jack or Simon.

Forgive me for asking more questions, but I want to make sure we guide you down a good path, not just one that will get you to a solution.

这篇关于按时间段对记录进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！