vba在每小时分割持续时间并添加记录 [英] vba to split duration in each hour and add record
问题描述
我有一个访问表(MasterLogs),其中包含Date,UserName,Position,loginTime,logoutTime。(登录和注销字段类型是日期和时间)。我试图读取此表中的每个记录并将记录添加到另一个表(PositionHrs)。该表中的字段是:
日期
用户名
职位
0000-0059
0100-0159
0200-0259
........
..... ...
........
2300-2359
病房第4栏基本上是填补持续时间。
Masterlogs表记录的一个例子是,Date:01-Aug-17,Username:xyz,Position:caller1,login:01-Aug-17 00:05 and logout:01- 8月17日02:45
我正在寻找一个VBA代码,如果在任何24小时内有任何分钟,它可以循环遍历masterlogs和每条记录中的记录用户工作,然后将持续时间插入到positionHrs表中的相应持续时间列。代码处理后的PositionHrs表的示例应为Date:01-Aug-17,Username:xyz,Position:caller1,0000-0059:00:54 0100-0159:01:00,002-0259:00:45 。所有其他持续时间列将为00:00。希望我已经解释得很好。
有谁可以帮我解决这个问题?我有一个vba代码只提供第一个持续时间并转到下一个记录
嗨
不确定这对你是否有很大的帮助,但它听起来很有趣,所以以下是一种在给定的perion中获取分钟的方法展开 < span class =codeDivider> | 选择 | 换行 | 行号
< blockquote>
- 出于某种原因,我发现这是一个相当困难的挑战,但我觉得我已经找到了一个可行的解决方案,产生了以下结果基于您的初始帖子。我得到的结果如下(展开窗口):
展开 | 选择 | Wrap | 行号
您可以使用查询执行此操作你想放弃代码。
你可以:
使用24个iif()调用,如果你有这些调用可能会很笨拙和缓慢很多记录,但如果它一次只有一个它很好
或
使用桥接表有24条记录,每小时1条,在时间范围内加入,然后转动结果
I have an access table (MasterLogs)with Date, UserName, Position, loginTime, logoutTime.(Login and logout field type is date&time). I am trying to read each record in this table and add records to another table (PositionHrs). The field in that table are:
Date
Username
Position
0000-0059
0100-0159
0200-0259
........
........
........
2300-2359
Column 4 on wards are basically to fill time duration.
An example of Masterlogs table record is, Date:01-Aug-17, Username:xyz, Position:caller1, login: 01-Aug-17 00:05 and logout: 01-Aug-17 02:45
I am looking for a VBA code which can loop through the records in masterlogs and for each record if there are any minutes in any of the 24 hour period the user worked, then duration to be inserted to corresponding time duration column in the positionHrs table. An example for the PositionHrs table after the code processed shall be Date:01-Aug-17, Username:xyz, Position:caller1, 0000-0059:00:54 0100-0159:01:00, 0200-0259:00:45. All other time duration columns will have 00:00. Hope I have explained well.
Can anyone please help me to fix this ? I have a vba code which is only giving the first duration and goes to next record
Expand|Select|Wrap|Line Numbers解决方案Hi
Not sure if this is a great help to you but but it sounded interesting, so the following is one method for abtaining the minutes in a given perionExpand|Select|Wrap|Line Numbers
- For some reason, I found this to be a rather difficult challenge, but I do feel as though I have arrived at a viable solution that produces the following results based on your initial Post. The results that I arrived at are as follows (expand the Window):
Expand|Select|Wrap|Line Numbers
You can do this with a query if you want to forgo the code.
You can either:
use 24 iif() calls which could be unwieldy and slow if you have a lot of records but if it''s only one at a time it''s fine
or
use a bridge table that has 24 records, 1 for each hour, join that to on the time range, and then pivot the result
这篇关于vba在每小时分割持续时间并添加记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文登录 关闭
扫码关注1秒登录发送“验证码”获取 | 15天全站免登陆