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 - Sub HourlyDuration()
-
Dim rs1 As Recordset
-
Dim rs2 As Recordset
-
Set rs1 = CurrentDb.OpenRecordset("MasterLogs")
-
Set rs2 = CurrentDb.OpenRecordset("PositionHrs")
-
-
Do While Not rs1.EOF
-
'below for adding minutes in rs2![0000-0059] "hour1"
-
-
If Format(rs1![On], "hh:nn") >= #12:00:00 AM# And Format(rs1![On], "hh:nn") <= #12:59:59 AM# Then
-
-
rs2.AddNew
-
rs2![Date] = rs1![Date]
-
rs2![0000-0059] = Format(TimeValue("01:00:00") - TimeValue(RoundTime(rs1!On, 60)), "hh:nn")
-
-
''roundtime is a function to round time to the next minute
-
-
rs2!Position = rs1!Position
-
rs2!Staff = rs1!Staff
-
rs2.Update
-
End If
-
-
'for next duration column rs2![0100-0159] "hour2"
-
-
If Format(rs1![On], "hh:nn") >= #1:00:00 AM# And Format(rs1![On], "hh:nn") <= #1:59:59 AM# Then
-
-
rs2.AddNew
-
rs2![Date] = rs1![Date]
-
rs2![0100-0159] = Format(TimeValue("02:00:00") - TimeValue(RoundTime(rs1!On, 60)), "hh:nn")
-
rs2!Position = rs1!Position
-
'rs2!Staff = rs1!Staff
-
rs2.Update
-
End If
-
' same conditions repeated for all hours, and then
-
-
rs1.MoveNext
-
-
Loop
-
-
-
MsgBox ("Finished")
-
-
-
rs1.Close
-
Set rs1 = Nothing
-
rs2.Close
-
Set rs2 = Nothing
-
End sub
I know this is totally wrong. Someone told me that I have to split the 24 hours into MOD and then add to the duration columns which I am unable to do. Please help
Changed the overall Logic with no Code Duplication and added a Log Date Field which would indicate spanning a Day. I feel as though we are very close now. P.S. - See entries for Babe Ruth and Barney Rubble.
29 3219
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 perion - Option Explicit
-
Dim StartTime As Date
-
Dim EndTime As Date
-
-
Sub AssignTimeToPeriods()
-
Dim i As Integer
-
Dim ThisPeriodStart As Date
-
Dim ThisPeriodEnd As Date
-
Dim ThisPeriodTime As Single
-
Dim ThisPeriodFieldName As String
-
-
'SET START AND END DATE/TIME (THESE VALUES WOULD BE OBTAINED/ASSIGNED FROM A FORM ETC.?)
-
StartTime = #8/1/2017# + 5 / 24 / 60
-
EndTime = #8/1/2017# + 2 / 24 + 45 / 24 / 60
-
-
'CYCLE THROUGH 24 HOUR PERIODS
-
For i = 0 To 23
-
'SET PERIOD START AND AND DATE/TIME
-
ThisPeriodStart = Int(StartTime) + i / 24
-
ThisPeriodEnd = Int(StartTime) + (i + 1) / 24
-
-
'SET FIELD NAME VARIABLE FOR THIS PERIOD
-
ThisPeriodFieldName = Format(ThisPeriodStart - Int(ThisPeriodStart), "hhmm") & "-" & Format(ThisPeriodEnd - Int(ThisPeriodEnd) - 1 / 24 / 60, "hhmm")
-
-
'DETERMINE IF TIME PERIOD IS INCLUDED IN WHICH THIS PERIOD
-
If StartTime <= ThisPeriodEnd And EndTime > ThisPeriodStart Then
-
-
'AND CALULATE THE TIME IN EACH PERIOD ACCORDINGLY IF IT IS
-
If StartTime > ThisPeriodStart Then
-
If EndTime < ThisPeriod Then
-
'START AND END BOTH WITHIN THIS PERIOD
-
ThisPeriodTime = EndTime - StartTime
-
Else
-
'START WITHIN THIS PERIOD BUT END AFTERWARDS
-
ThisPeriodTime = ThisPeriodEnd - StartTime
-
End If
-
ElseIf EndTime >= ThisPeriodEnd Then
-
'START ON OR BEFORE THIS PERIOD AND ENDS AFTERWARDS
-
ThisPeriodTime = ThisPeriodEnd - ThisPeriodStart
-
Else
-
'START ON OR BEFORE THIS PERIOD AND ENDS WITHIN THE PERIOD
-
ThisPeriodTime = EndTime - ThisPeriodStart
-
End If
-
-
'Change 'PERIOD' FROM DAYS TO MINUTES (OR AS REQUIRED)
-
ThisPeriodTime = ThisPeriodTime * 24 * 60
-
-
'USE UPDATE QUERY TO UDATE RELEVANT RECORD PREVIOUSELY CREATED USING THE FIELD NAME VARABLE AND TIME VALUE
-
Debug.Print ThisPeriodFieldName & " = " & ThisPeriodTime
-
End If
-
-
Next i
-
End Sub
I have used the time values and periods indicated in the post.
Does that help?
There could be a simpler way that I don't know!!
MTB
- 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):
- Date User Name Position Start Finish Duration
-
--------------------------------------------------------------------------------
-
8/1/2017 xyz Caller1 00:00 00:59 00:55
-
8/1/2017 xyz Caller1 01:00 01:59 00:60
-
8/1/2017 xyz Caller1 02:00 02:59 00:46
-
8/1/2017 xyz Caller1 03:00 03:59 00:00
-
8/1/2017 xyz Caller1 04:00 04:59 00:00
-
8/1/2017 xyz Caller1 05:00 05:59 00:00
-
8/1/2017 xyz Caller1 06:00 06:59 00:00
-
8/1/2017 xyz Caller1 07:00 07:59 00:00
-
8/1/2017 xyz Caller1 08:00 08:59 00:00
-
8/1/2017 xyz Caller1 09:00 09:05 00:00
-
8/1/2017 xyz Caller1 10:00 10:59 00:00
-
8/1/2017 xyz Caller1 11:00 11:59 00:00
-
8/1/2017 xyz Caller1 12:00 12:59 00:00
-
8/1/2017 xyz Caller1 13:00 13:59 00:00
-
8/1/2017 xyz Caller1 14:00 14:59 00:00
-
8/1/2017 xyz Caller1 15:00 15:59 00:00
-
8/1/2017 xyz Caller1 16:00 16:59 00:00
-
8/1/2017 xyz Caller1 17:00 17:59 00:00
-
8/1/2017 xyz Caller1 18:00 18:59 00:00
-
8/1/2017 xyz Caller1 19:00 19:59 00:00
-
8/1/2017 xyz Caller1 20:00 20:59 00:00
-
8/1/2017 xyz Caller1 21:00 21:59 00:00
-
8/1/2017 xyz Caller1 22:00 22:59 00:00
-
8/1/2017 xyz Caller1 23:00 23:59 00:00
-
--------------------------------------------------------------------------------
-
--------------------------------------------------------------------------------
- Rather then go into a long-winded explanation of the Logic, I'll simply Upload the Demo and let you decide if it is worthwhile. I am sure that there has to be a better solution, but it simply eludes me at this time.
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
use a bridge table that has 24 records, 1 for each hour, join that to on the time range, and then pivot the result
Out of curiosity, Rabbit, how would you Join these Tables, and on what Fields? For my Demo, I used a Reference Table depicted below. Would your Bridge Table be the same or similar. Thanks in advance for your explanation. -
ID Start Finish
-
1 0:00 0:59
-
2 1:00 1:59
-
3 2:00 2:59
-
4 3:00 3:59
-
5 4:00 4:59
-
6 5:00 5:59
-
7 6:00 6:59
-
8 7:00 7:59
-
9 8:00 8:59
-
10 9:00 9:05
-
11 10:00 10:59
-
12 11:00 11:59
-
13 12:00 12:59
-
14 13:00 13:59
-
15 14:00 14:59
-
16 15:00 15:59
-
17 16:00 16:59
-
18 17:00 17:59
-
19 18:00 18:59
-
20 19:00 19:59
-
21 20:00 20:59
-
22 21:00 21:59
-
23 22:00 22:59
-
24 23:00 23:59
-
P.S. - The [Start] and [Finish] Fields are Date/Time.
Dear ADezii
Thank you for the demo and a positive approach. This looks like I am getting very close to the solution what I am looking for.
I am trying with the demo db but on this line "Debug.Print String(80, "-")" i was getting a vb error and when checked in vba references it shows missing outlook 14.0 and excel 14.0 object missing.
I have selected those objects in vba reference and on the form button click it prints 24 lines for each user.How can I print only the rows with if there is a value in duration (I dont want to see the time periods when there is no duration).
And, instead of printing (debug.print) I would like to store this in another table. Please guide me
One Error is resulting from unresolved References in the Demo, the Debug.Print Statement can be rewritten (not sure why it is throwing an Error), and showing only positive Durations will be an easy fix. Storing the Results in another Table will be a little difficult but can also be done. I will work on it today and Upload a Revision when complete.
Dear ADezii
Thank you very much for your kindness and support. This is a great help I appreciate much. Both errors are resolved after I selected the references for Outlook and Excel. I will wait for your kind update
Here is the Revised Code along with the Revised Demo. I never meant my solution to be this complex, but it sort of developed a life of it's own and grew. I would patiently wait and see if Rabbit can demonstrate a more efficient and simpler SQL-based approach. He is infinitely more qualified in this area than I am. I also added 2 additional Records in the MasterLogs Table in different Time Slots to verify that the Logic is indeed sound. In any event, download the Revised Demo, and let me know what you think. - Private Sub cmdTest_Click()
-
On Error GoTo Err_cmdTest_Click
-
Dim MyDB As DAO.Database
-
Dim rst1 As DAO.Recordset 'MasterLogs
-
Dim rst2 As DAO.Recordset 'tblTimeSlots
-
Dim rst3 As DAO.Recordset 'PositionHrs
-
Dim intCtr As Integer
-
Dim intMinBld As Integer 'Keep Running Count for Time Slots
-
-
'Clear the PositionHrs Table
-
CurrentDb.Execute "DELETE * FROM PositionHrs", dbFailOnError
-
-
Set MyDB = CurrentDb
-
Set rst1 = MyDB.OpenRecordset("MasterLogs", dbOpenForwardOnly)
-
Set rst2 = MyDB.OpenRecordset("SELECT * FROM tblTimeSlots ORDER BY ID", dbOpenSnapshot)
-
Set rst3 = MyDB.OpenRecordset("PositionHrs", dbOpenDynaset)
-
-
With rst1
-
Do While Not .EOF
-
Do While Not rst2.EOF
-
For intCtr = 0 To DateDiff("n", TimeValue(![LogIn]), TimeValue(![LogOut]))
-
If TimeValue(DateAdd("n", intCtr, ![LogIn])) >= rst2![Start] And _
-
TimeValue(DateAdd("n", intCtr, ![LogIn])) <= rst2![Finish] Then
-
intMinBld = intMinBld + 1
-
End If
-
Next
-
If intMinBld > 0 Then
-
rst3.AddNew
-
rst3![Date] = ![Date]
-
rst3![User Name] = ![UserName]
-
rst3![Position] = ![Position]
-
rst3![Start] = Format(rst2![Start], "hh:nn")
-
rst3![Finish] = Format(rst2![Finish], "hh:nn")
-
rst3![Duration] = Format$(intMinBld, "00:00")
-
rst3.Update
-
End If
-
intMinBld = 0
-
rst2.MoveNext
-
Loop
-
.MoveNext
-
rst2.MoveFirst
-
Loop
-
End With
-
-
rst1.Close
-
rst2.Close
-
rst3.Close
-
Set rst1 = Nothing
-
Set rst1 = Nothing
-
Set rst3 = Nothing
-
-
With DoCmd
-
.OpenTable "PositionHrs", acViewNormal, acReadOnly
-
.Maximize
-
End With
-
-
Exit_cmdTest_Click:
-
Exit Sub
-
-
Err_cmdTest_Click:
-
MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
-
Resume Exit_cmdTest_Click
-
End Sub
P.S. - I am pretty sure that the Code Logic can be improved upon. Addressing every minute within the LogIn/LogOut Range to see what Time Slot it falls into is time consuming and not very efficient. The problem is that I simply do not have the time to spend on the improvements.
Dear ADezii
Thank you for the time you spent to support me. It is a great help.
I was trying with few sample entries, and noticed that when the logout extends to next day the code is not processing that record. Example, if login 01-Aug-17 22:58 and logout 02-Aug-17 00:35, there is no records written to the PositionHrs table. In reality there will be entries for each date with login same date but logout after midnight.
Hope you can adjust the code to include those entries as well.
Thank you for the support and patience you have shown towards resolving this
and noticed that when the logout extends to next day the code is not processing that record
You are correct, the Logic does not allow for days being spanned (wasn't aware that this was a requirement). When I get a chance, I'll see what I can do to compensate for this. Got a couple of minutes, so try Revision 2, specifically looking at Caller17 (Babe Ruth) who has LogIn and LogOut span 2 days (9/1/2017 and 9/2/2017).
@Adezii, you would join on overlapping time between the two tables. To do so, one side would need to be converted to the same date as the other.
But given the new requirement that the timestamps can span multiple days, my original suggestion would no longer work.
Thanks Rabbit. I am under the assumption, that under the given circumstances, there is no purely SQL-based solution. Do you consider that an accurate statement?
There is, but it would be even more convoluted. It would require a dummy table of a bunch of rows representing hour ranges from midnight spanning multiple days. It wouldn't be foolproof because you don't know how many rows you would need to cover the range of days between the start and end time in the target table. It could cause an unwieldy join between the two tables.
Dear ADezii
Thank you for the willingness and patience to resolve my issue. I have tried the second demo also. The Date field in the "PositionHrs" table can be from the date part of login entry. I have a humble suggestion for a different approach which I feel may workout, but my little knowledge cannot put it in a code. It is like, First, read each record from MasterLogs table, then add a record in the "MasterLogs" table if the logout is in next date. and the logout of the record which has a logout nextdate can be modified as 23:59:59. The new record added in the "MasterLogs" table will have nextdate(date from the logout) as date, user same as user, position same as position, login will be with nextdate and 00:00:00 and logout will be same as the base record. Then we don't need to worry about the complications of getting entries to the "PositionHrs" table for logout next date. In my records a logout will stretch to next date maximum not beyond that. Once all the records in the "masterLogs" where logout is nextdate are split and added to the masterlogs table, your code from first demo can start the other process and add records to the "PositionHrs" table. Hope this is not a foolish approach
We arrive at what I feel is a workable solution, or something relatively close, and you now wish to change your strategy. I really do not understand this logic.
Dear ADezii
Sorry, you misunderstood. I thought the demo revised 2 DB still incomplete and you are trying again with some options. That is why I suggested an alternate approach. If the revised2 is workable, I can start using it. Please let me know
If the revised2 is workable, I can start using it.
Only you will know if it is 'workable' or not.
Attached is an example of how this would be done without VBA code. It only accounts for time ranges spanning 2 days. You would have to add more records to the time slots table to account for more days.
Dear ADezii
Sorry for the delayed reply. I was busy with some personal travelling. The solution is workable but only one issue I am having. When it capture "Start" and Finish" for the logout in next date, that particular entry "00:00" 00:59 or any time in tha next date, the Date should show the logout date. Because "Start" and "Finish" time is in the next date. Hope my explanation is clear. Please help me to resolve that.
I am a little hazy on this aspect. Please provide some Sample Date showing what the Output should look like for day spans. Hopefully a visual display will clarify matters for me.
Dear ADezii
Thank you for the kind reply. I am attaching the same DB you have provided, but with manually adjusted expected results in the"PositionHrs" table. As you can see from the "MasterLogs" table, record 4 spans to 2 dates and in the expected results "PositionHrs" table the expected results are in record number 12 to 15. Hope that will give you a clear picture about what I am looking for. The login and logout fields doesnt need to be written to the "PositionHrs" table.
I will have another look when I get a chance.
Right now, I absolutely see no easy method to accomplish what you have requested since it would involve 2 different Methods of displaying the Data, one for Time Slots in a single Day, and another for spanning Multiple Days. What I have done is to essentially run the same block of Code twice, catching first the single Day Time Slots and then Multiple Day Time Slots. This will display the Records in PositionHrs first by Single Day, then by Multiple Day and will not reflect the natural order of Records in MasterLogs. This is not really desirable and neither is the duplication of Code, but it is all that I can come up with at this point in time. As soon as I get more time to look at it, I'll give it another try. In any event, take a look at the Revised Demo and see what you think.
Dear ADezii
Thank you for the effort and patience you have shown. Instead of repeating the code block, is there any way we can while adding the record which has next day logout to have the date captured from the logout. But not for the time period 2300-2359 - That should have date as login date.
Other wise the date will show previous date for hours in next date.
Display only the Logout and not Login Date in PositionHrs?
Dear ADezii
The date is based on login and logout. for each hour in a login date the duration need to be found. and if the login and logout is different dates, then upto time 2359 the duration will fall into login date. any duration after 2359 of the login date will be recorded for the logout date. an example is if login 01-Aug-17 2350 and logout 02-Aug-17 0015, then there will be 2 entries need to be added to PositionHrs. 1 entry for 01-Aug-17 2300-2359 with a duration of 10 minutes and 2nd entry for 02-Aug-17 for time period 0000-0059 with a duration of 15 minutes. Hope my explanation is not confusing you. Please help
Changed the overall Logic with no Code Duplication and added a Log Date Field which would indicate spanning a Day. I feel as though we are very close now. P.S. - See entries for Babe Ruth and Barney Rubble.
Dear ADezii
Awesome. Even you mentioned that we are very close, but I feel that we have done. That is my first impression after a quick look. I will run with more records and let you know the out come. Thank you so much
You are quite welcome, Good Luck with your Project!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dmitry |
last post by:
Hello everyone,
I have a really simple question here:
I have a plain space delimited file that I want to read with WHILE
loop 1 line at the time and process each input record as an array of...
|
by: Warren Wright |
last post by:
Hello,
We maintain a 175 million record database table for our customer.
This is an extract of some data collected for them by a third party
vendor, who sends us regular updates to that data...
|
by: Phil |
last post by:
I am looking to set up a hyperlink control on a form to retrieve letters
that correspond to a record on a form. That is, there may be 100 form
records, and I would like each of those form records...
|
by: A |
last post by:
Hello,
I try to make a Windows Service that doing some action at each begin of
hour.
I cannot use the task schedule.
I have try to use thread, timer ... But nothing is correct.
Do you have...
|
by: mcdonaghandy |
last post by:
Hello,
I have been having a tough time writing the follow requirement for a
query.
On a table that the primary key is a tagId and an hourly timestamp, I
would like to find out for every hour...
|
by: KiwiGenie |
last post by:
I have a form which lists selected records, there could be anything from 1 record to all records selected. I want to output a report for each record as HTML. I want each file to take its name from a...
|
by: khaled1988 |
last post by:
hi
i'm stack in query problem i need sql query that can sum(orders columns)for each hour in day
the result is like this
date/time | order | doors | center
11/02/2008...
|
by: captainB |
last post by:
hi and thanks for reading.
I am working on a web page that will display records from a database. Each record has a total of 8 columns. One column may be as long as 500 characters, and the other 7...
|
by: bullfrog83 |
last post by:
I have a form with two textboxes: txtSortOrder and txtCourse. I want the txtSortOrder's default value to increment by 10 for each new record (this is to save data entry time). So, if I type in 10 for...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |