Read Log and assign Logout time

[rajeevs]

Hi All
I am back with another problem. I have a system log (csv file) which logs the user name, login time and the position name only.I need to read through this file and assign the logout time for each user. The logout time for the previous user will be the next users login time in the same position.
Hope I have explained what I am trying to achieve.
Please help. I have attached a sample Excel file with the expected results for your kind consideration

Regards

[twinnyfo]

rajeevs,

I do see some issues with your Data, but I think if you resolve those issues, the solution below should work fine.

The "Login" data does not look like a true date, so this might cause some problems when we try to compare these values. If they were true Date/Time, comparison should work fine.

So, how I would approach this problem would be to have the user log in. You set a variable with date/time data type and store the value of the time the user logged in. This value is then saved to that user's Login field.

Second, since you know the position of the person, you query all your records for other users at that position, but you use an aggregate query, looking for the Max value in the Login field (which ALSO does not equal the current user who just logged in).

Hopefully this table has an index (which it most definitely should), because you can't just update and aggregate query. Then you update the record you found with the login time you just saved. Skeleton below:

Private Sub UserLogIn()
    Dim - any variables you need

    Establish the time the user logged in

    Save that value to the current User's record

    Search for the most recent user in that position

    Update that user's record
End Sub

This should give you a good framework to begin with, as you continue to work on this, we will be glad to assist with any snags you might run across.

Hope this hepps!

[rajeevs]

Hi twinnyfo
Thank you for the reply.
The time can be converted to date/time because the csv file name will be a date which is the log date. So I can change the login time as date/time field. I doubt which field should be indexed. Do I need to have a sequential number for each record and use that as the index or the position name as index?
You have mentioned that look for the max value for login but what I need is the next login time for the same position but with a different user name and that will be the logout time of the previous user. Please advise

[zmbd]

rajeevs:
+ The CSV file would be appreciated too so that we can see the raw data. Once we have that...

Would be best if you'd just cut and paste this information within a post as a [CODE] block

I understand if there are personal details contained therein; however, one should be able to open the file up in notepad or excel etc.. and change names to JohA Doe, JohB Doe, etc... and SSN or TaxID can be altered to 000-00-0001, (...)-0002, etc...

+ Do you have any code at this point? Would you post it please?

If you haven't start yet, then please take a moment to write down your work flow.
For some reason, the physical act of writing down the work flow helps the human brain resolve the logic needed to accomplish a task.
Next, convert that informal work flow to a programing diagram.... I prefer NS-Charts (PDF File); however, any method is more helpful than winging it IMHO.

+ I Agree with TwinnyFo - you really should have an index/Primary Key field (I hate composite primary keys - my opinion though :) ) I do this with a CSV file I import from my instruments in the lab to the database.
[PK][LogInTime][LogOutTime][FK_User][etc....]

As to how to hold the data pointer, that depends on the raw data stream. I have a case where the instrument sample data export doesn't always write the data out in the same order (same header, just the moisture might be the first record or the third record for a given sample) this is the type of information your raw data file will provide.

sorry, not much more detail here... very limited time today :)

-z

[rajeevs]

Hi zmbd
I will post the sample file with more details and try to write the work flow as you have suggested by tomorrow. At present here it is night and the data is in my office PC. I thank you for the detailed reply and your kind suggestions

Regards

[rajeevs]

Dear zmbd
I was trying something with the data. But I feel that I am not getting the results what I am looking for. The code as below:

Public Function AddDetails()
Dim SRs As Recordset
Dim RRs As Recordset
dim PRs as recordset
Dim strFilter As String
Dim strInputFileName As String
Dim PrevRec As String
'Browse and open Cleaned Excel file to read
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
strInputFileName = ahtCommonFileOpenSave(InitialDir:="PathtoOpen", Filter:=strFilter, FilterIndex:=3, DialogTitle:="Choose an Excel file...")

'Clear old data from tmp table and read in data from selected excel file
If Len(strInputFileName) > 0 Then
   DoCmd.RunSQL "Delete * from tmp"
   DoCmd.TransferSpreadsheet acImport, , "Tmp", strInputFileName, True, "Tmp!"
Else
   MsgBox "Task aborted"
   Exit Function
End If

'Input Date range to process
StartDate = CDate(InputBox("Begin update from Date: ", "Import Data", Format(DateAdd("d", -1, Now()), "dd mmm yyyy")))
EndDate = CDate(InputBox("Update until: ", "Import Data", Format(DateAdd("d", -1, Now()), "dd mmm yyyy")))


Screen.MousePointer = 11

'Open data file for selected date range to be imported
Set SRs = CurrentDb.OpenRecordset("Select * from tmp where Time Between #" & Format(StartDate, "mm/dd/yy") & " 00:00:00# and #" & Format(EndDate, "mm/dd/yy") & " 23:59:59# order by Time")
'Table that stores imported data
Set RRs = CurrentDb.OpenRecordset("Select * from Detail order by Date")
'List of positions
Set PRs = CurrentDb.OpenRecordset("Select * from Positions")

'Checks and exits if the date range has been previously imported
If Not RRs.EOF Then
   RRs.MoveLast
   If RRs!Date >= StartDate And RRs!Date <= EndDate Then
      MsgBox "Data until this date imported previously"
      GoTo exitFunction
   End If
End If
PrevRec = SRs!Time & " " & SRs!Position
Do While Not SRs.EOF
   'Going through each record
   
      
   If SRs!Operation = "OPERATOR_EVENT" And Len(Trim(SRs!Position)) > 2 Then 
      'if login entry and position available add a record
      RRs.AddNew
      RRs!Date = Format(SRs!Time, "dd/mm/yy")
      RRs!Staff = Trim(UCase(SRs!User))
      RRs!Position = Trim(SRs!Position)
      RRs!On = SRs!Time
      RRs.Update
      'Added due to missed logout entries
      
      
      RRs.FindFirst "Position = '" & Trim(SRs!Position) & "' and [Off] is Null" 'search that staff's record that does not have a logoff time
FindNull:
      If Not RRs.NoMatch Then
         If RRs!Staff <> SRs!User And SRs!Operation = "OPERATOR_EVENT" Then
            'Update logoff time if does not exist and login person on same position is different
            RRs.Edit
            RRs!Off = SRs!Time
            RRs!Position = RRs!Position 
            RRs.Update
            PRs.FindFirst "Positions = '" & Trim(RRs!Position) & "'" 'Search positions table if exists, if not then add
            If PRs.NoMatch Then
               PRs.AddNew
               PRs!Positions = Trim(RRs!Position)
               PRs.Update
            End If
         Else
            RRs.FindNext "Position = '" & Trim(SRs!Position) & "' and [Off] is Null" 'search that staff's record that does not have a logoff time
            GoTo FindNull
         End If
      End If
      

   
   Else
      'MsgBox "Halt for nothing"
   End If
   
   SRs.MoveNext
Loop

RRs.FindFirst "Date = #" & Format(StartDate, "mm/dd/yy") & "#"

MsgBox "Import Completed", vbInformation + vbOKOnly, "Finish"
exitFunction:
PRs.Close
Set PRs = Nothing
RRs.Close
Set RRs = Nothing
SRs.Close
Set SRs = Nothing
'UpdateDetails
Screen.MousePointer = 0

End Function

I am attaching the sample file also. There are 3 tables in my DB. I Import all the records from the Excel file to tmp and then assigning logout time based on the criteria mentioned earlier. Please let me know am I doing the correct way?

[zmbd]

rajeevs:
Normally, what I would do is use the VBA Standard Text File I/O Statements, then something like Line Input #zFileIndex, zlinein followed by a split and parse the array. I've imported over 500 files at in a single batch using this method... it can take 5 or 10 minutes to import that many files; HOWEVER, in your case I'm thinking that we might look at actually trying to use the CSV as a "RecordSet"

This would have the advantage in that we can filter down the incoming data to just those records that have your "login" and "logout" events.

Now IF you could kindly type in your CSV file in to a post...
Say, just the header row and then say the next 10 lines....
Easy enough, open in Notepad, copy and paste in the post, select the pasted text and format it using the [CODE/] tool.... While in notepad you can easily change names or sensitive bits to something generic.

Once we have the actual RAW data, I can then look at the logic behind the import.

I have some code that will do this; however, it was only proof of concept stuff and I need to tweak it a tad for the ACE provider... (hello connection strings... fiddly little things :) )

Unfortunately, I cannot open your original files here at work for various reasons and we're short-staffed again today, the Gremlins took down another employee... this weather is just playing havoc with the staff this year; thus, this may have to wait until this evening for a bit of review.

NEVER FEAR Z IS HERE!

:)

[rajeevs]

Dear zmbd
Thank you for the reply and support.
I will paste the records as you mentioned. But I will explain what I am looking for.
The file has header like
User Trainee Operation Position Time
There will be operation like "OPERATOR_EVENT ","LOGON_EVENT" ,"LOGOFF_EVE NT"
In the users list there will be 3 letter initials denotes the users and some generic user initial which end with numeric values.
In the Trainee Field you will find 3 letter initials as trainees but not for all entries.
When there is a 3 letter initial and operation is "OPERATOR_EVENT " that will be considered as a login time for that user in that position. This user will sometime logout the position with the generic user initial(User Field end with numeric value) and that can be considered as the the logout time for the first user. Or if another 3 letter user in the same position "OPERATOR_EVENT " is found then that can be the previous user logout time.The generic user initial is used when there is no other person logging in for the time being. May be after some time another person will login that position.
If the Operation is "LOGON_EVEN T" then that entry will be treated as a separate entry for the user and add that to the same table but there is a field in the table named as "SOLO" which is always set as Yes. For these entries the Field need to update as "No" and the logout for this entry will be the Operation "LOGOFF_EVE NT" with the same user initial.
This is the logic I am looking for when we read through each line in the CSV or XLS file.
I have a temp tbl where I import the XLS file first. Then I need to run the function which will read the entries in Temp Table and add to another table which has a structure like
Date User Trainee SOLO Position Login(On) LogOff(Off)
There could be some entries in the CSV/XLS file which may not have a logoff time towards the end of the day which will be available in the next day's log.
Hope I have explained. If it was unclear please ask me again.
The csv file as below

User	Trainee	Operation	Position	Time
ah3		OPERATOR_EVENT	AMR	01-05-16 01:58:44
nbb		OPERATOR_EVENT	AMR	01-05-16 01:59:26
al2		OPERATOR_EVENT	AMR	01-05-16 02:41:21
man		OPERATOR_EVENT	AMR	01-05-16 03:55:55
man	rhd	LOGON_EVENT	AMR	01-05-16 04:02:14
ah4		OPERATOR_EVENT	AMR	01-05-16 04:48:17
man		LOGOFF_EVENT	AMR	01-05-16 04:57:33
art		OPERATOR_EVENT	AMR	01-05-16 06:15:08
man		OPERATOR_EVENT	AMR	01-05-16 07:27:31
art		OPERATOR_EVENT	AMR	01-05-16 08:29:42
ams		OPERATOR_EVENT	AMR	01-05-16 10:00:07
ams		OPERATOR_EVENT	AMR	01-05-16 10:10:13
al2		OPERATOR_EVENT	AMR	01-05-16 10:10:14
ah4		OPERATOR_EVENT	AMR	01-05-16 10:10:15
art		OPERATOR_EVENT	AMR	01-05-16 11:17:44
ams		OPERATOR_EVENT	AMR	01-05-16 11:49:42
ah3		OPERATOR_EVENT	AMR	01-05-16 12:41:47
ah3		OPERATOR_EVENT	AMR	01-05-16 13:08:40
ams		OPERATOR_EVENT	AMR	01-05-16 13:08:55
ams		OPERATOR_EVENT	AMR	01-05-16 14:01:22
ah3		OPERATOR_EVENT	AMR	01-05-16 14:36:58
ah3		OPERATOR_EVENT	AMR	01-05-16 16:04:56
ams		OPERATOR_EVENT	AMR	01-05-16 16:05:08
ams		OPERATOR_EVENT	AMR	01-05-16 16:56:07
ah3		OPERATOR_EVENT	AMR	01-05-16 17:27:35
are		OPERATOR_EVENT	ARR	01-05-16 00:56:33
van		OPERATOR_EVENT	ARR	01-05-16 01:45:52
moe		OPERATOR_EVENT	ARR	01-05-16 02:27:39
han		OPERATOR_EVENT	ARR	01-05-16 03:27:44
van		OPERATOR_EVENT	ARR	01-05-16 04:59:55
moe		OPERATOR_EVENT	ARR	01-05-16 05:56:46
vuu		OPERATOR_EVENT	ARR	01-05-16 06:51:10
vuu		OPERATOR_EVENT	ARR	01-05-16 07:27:50
van		OPERATOR_EVENT	ARR	01-05-16 07:58:57
vuu		OPERATOR_EVENT	ARR	01-05-16 08:26:25
han		OPERATOR_EVENT	ARR	01-05-16 09:15:41
grt		OPERATOR_EVENT	ARR	01-05-16 09:43:45
dic		OPERATOR_EVENT	ARR	01-05-16 10:58:18
grt		OPERATOR_EVENT	ARR	01-05-16 12:25:51
grt	pai	LOGON_EVENT	ARR	01-05-16 12:26:06
grt		LOGOFF_EVENT	ARR	01-05-16 13:27:45
kgb		OPERATOR_EVENT	ARR	01-05-16 13:29:15
grt		OPERATOR_EVENT	ARR	01-05-16 14:24:20
pai		OPERATOR_EVENT	ARR	01-05-16 14:24:32
grt		OPERATOR_EVENT	ARR	01-05-16 14:24:50
grt	pai	LOGON_EVENT	ARR	01-05-16 14:25:03
zie		OPERATOR_EVENT	COD	01-05-16 15:14:57
dic		OPERATOR_EVENT	COD	01-05-16 15:28:25
grt		LOGOFF_EVENT	ARR	01-05-16 16:00:52
dic		OPERATOR_EVENT	ARR	01-05-16 16:01:06
mcn		OPERATOR_EVENT	ARR	01-05-16 16:25:22
xan		OPERATOR_EVENT	ARR	01-05-16 17:43:29
xan	las	LOGON_EVENT	ARR	01-05-16 17:45:29
las	vhj	OPERATOR_EVENT	ARR	01-05-16 17:47:17
vhj		LOGOFF_EVENT	ARR	01-05-16 18:55:50
vhj	sti	LOGON_EVENT	ARR	01-05-16 18:56:06
sti	xan	OPERATOR_EVENT	ARR	01-05-16 18:56:42
ah4		OPERATOR_EVENT	ARR	01-05-16 18:57:49
sti	xan	OPERATOR_EVENT	ARR	01-05-16 19:06:47
ah4		OPERATOR_EVENT	ARR	01-05-16 19:09:06
al1		OPERATOR_EVENT	ARR	01-05-16 19:12:13
xan		LOGOFF_EVENT	ARR	01-05-16 19:56:13
msl		OPERATOR_EVENT	ARR	01-05-16 19:56:23
hek		OPERATOR_EVENT	ARR	01-05-16 20:29:49
hek	las	LOGON_EVENT	ARR	01-05-16 21:39:40
las	vhj	OPERATOR_EVENT	ARR	01-05-16 21:56:05
las	xan	OPERATOR_EVENT	ARR	01-05-16 22:55:31
are		OPERATOR_EVENT	DIR	01-05-16 00:05:07
fth		OPERATOR_EVENT	DIR	01-05-16 00:55:34
han		OPERATOR_EVENT	DIR	01-05-16 01:43:35
vuu		OPERATOR_EVENT	COD	01-05-16 02:56:31
van		OPERATOR_EVENT	DIR	01-05-16 02:57:14
cr4		OPERATOR_EVENT	DIR	01-05-16 03:46:40
fjp		OPERATOR_EVENT	COD	01-05-16 03:56:41
moe		OPERATOR_EVENT	COD	01-05-16 04:30:17
ree		OPERATOR_EVENT	COD	01-05-16 05:26:40
han		OPERATOR_EVENT	COD	01-05-16 05:56:19
vuu		OPERATOR_EVENT	DIR	01-05-16 06:50:00
vuu		OPERATOR_EVENT	DIR	01-05-16 06:51:31
van		OPERATOR_EVENT	COD	01-05-16 06:56:59
han		OPERATOR_EVENT	DIR	01-05-16 07:27:10
van		OPERATOR_EVENT	DIR	01-05-16 08:58:21
cr4		OPERATOR_EVENT	DIR	01-05-16 09:12:37
dic		OPERATOR_EVENT	DIR	01-05-16 09:42:59
mcn		OPERATOR_EVENT	DIR	01-05-16 10:20:39
grt		OPERATOR_EVENT	DIR	01-05-16 11:24:46
mcn		OPERATOR_EVENT	DIR	01-05-16 11:56:27
cr4		OPERATOR_EVENT	DIR	01-05-16 12:25:05
kgb		OPERATOR_EVENT	DIR	01-05-16 12:28:45
mcn		OPERATOR_EVENT	DIR	01-05-16 13:28:55
dic		OPERATOR_EVENT	DIR	01-05-16 14:25:35
mcn		OPERATOR_EVENT	DIR	01-05-16 14:58:23
kgb		OPERATOR_EVENT	DIR	01-05-16 15:28:03
grt		OPERATOR_EVENT	DIR	01-05-16 16:27:01
grt	pai	LOGON_EVENT	DIR	01-05-16 16:38:49
grt		LOGOFF_EVENT	DIR	01-05-16 17:24:07
dic		OPERATOR_EVENT	DIR	01-05-16 17:24:26
msl		OPERATOR_EVENT	DIR	01-05-16 17:42:04
hek		OPERATOR_EVENT	DIR	01-05-16 18:57:01
hek		OPERATOR_EVENT	DIR	01-05-16 19:05:00
cr5		OPERATOR_EVENT	DIR	01-05-16 19:05:00
hek	sti	LOGON_EVENT	DIR	01-05-16 19:57:56
ah4		OPERATOR_EVENT	DIR	01-05-16 19:57:58
sti	vhj	OPERATOR_EVENT	DIR	01-05-16 19:58:20
cr5		OPERATOR_EVENT	DIR	01-05-16 19:59:58
vhj		LOGOFF_EVENT	DIR	01-05-16 20:55:46
xan		OPERATOR_EVENT	DIR	01-05-16 20:55:54
xan	las	LOGON_EVENT	DIR	01-05-16 20:58:17
xan		LOGOFF_EVENT	DIR	01-05-16 21:37:27
cr4		OPERATOR_EVENT	DIR	01-05-16 21:37:40
cr2		OPERATOR_EVENT	DPN	01-05-16 01:45:25
fjp		OPERATOR_EVENT	DPN	01-05-16 01:45:39
ree		OPERATOR_EVENT	DPN	01-05-16 03:27:23
bry		OPERATOR_EVENT	DPN	01-05-16 04:30:51
fjp		OPERATOR_EVENT	DPN	01-05-16 05:52:24
ree		OPERATOR_EVENT	DPN	01-05-16 07:26:09
nic		OPERATOR_EVENT	DPN	01-05-16 08:59:49
cra		OPERATOR_EVENT	DPN	01-05-16 09:43:55
zie		OPERATOR_EVENT	DPN	01-05-16 11:26:24
cr2		OPERATOR_EVENT	DPN	01-05-16 11:39:39
cr2		OPERATOR_EVENT	DPN	01-05-16 14:00:36
ada		OPERATOR_EVENT	DPN	01-05-16 14:00:43
cra		OPERATOR_EVENT	COD	01-05-16 14:01:02
meh		OPERATOR_EVENT	DPN	01-05-16 14:05:34
zie		OPERATOR_EVENT	DPN	01-05-16 15:29:40
ada		OPERATOR_EVENT	DPN	01-05-16 16:30:00
zie		OPERATOR_EVENT	DPN	01-05-16 16:57:27
kay		OPERATOR_EVENT	DPN	01-05-16 17:42:02
fre		OPERATOR_EVENT	DPN	01-05-16 18:52:41
aar		OPERATOR_EVENT	DPN	01-05-16 19:55:40
cr2		OPERATOR_EVENT	DPN	01-05-16 20:45:22
rmv		OPERATOR_EVENT	DPS	01-05-16 00:00:05
duf		OPERATOR_EVENT	COD	01-05-16 00:04:20
rdb		OPERATOR_EVENT	COD	01-05-16 00:07:26
duf		OPERATOR_EVENT	COD	01-05-16 00:21:06
lau		OPERATOR_EVENT	DPS	01-05-16 00:54:40
bek		OPERATOR_EVENT	DPS	01-05-16 01:19:08
rmv		OPERATOR_EVENT	COD	01-05-16 01:25:41
ree		OPERATOR_EVENT	DPS	01-05-16 01:43:34
nic		OPERATOR_EVENT	COD	01-05-16 01:45:44
nic	nai	LOGON_EVENT	COD	01-05-16 01:46:03
al2		OPERATOR_EVENT	DPS	01-05-16 01:53:50
nic		LOGOFF_EVENT	COD	01-05-16 01:56:04
nai		OPERATOR_EVENT	COD	01-05-16 01:56:49
nic		OPERATOR_EVENT	COD	01-05-16 02:01:12
nic	nai	LOGON_EVENT	COD	01-05-16 02:01:42
ree		OPERATOR_EVENT	DPS	01-05-16 02:02:39
al3		OPERATOR_EVENT	DPS	01-05-16 02:10:12
nai	bry	OPERATOR_EVENT	COD	01-05-16 02:26:42
bry		LOGOFF_EVENT	COD	01-05-16 02:38:12
vuu		OPERATOR_EVENT	COD	01-05-16 02:55:32
ah4		OPERATOR_EVENT	DPS	01-05-16 02:56:17
nic		OPERATOR_EVENT	DPS	01-05-16 02:57:14
nic	nai	LOGON_EVENT	DPS	01-05-16 02:58:00
nic		LOGOFF_EVENT	DPS	01-05-16 04:32:09
ree		OPERATOR_EVENT	DPS	01-05-16 04:32:22
nic		OPERATOR_EVENT	DPS	01-05-16 05:05:43
nic	nai	LOGON_EVENT	DPS	01-05-16 05:07:39
nic		LOGOFF_EVENT	DPS	01-05-16 06:31:05
bry		OPERATOR_EVENT	DPS	01-05-16 06:31:54
van		OPERATOR_EVENT	COD	01-05-16 06:59:43
nic		OPERATOR_EVENT	COD	01-05-16 07:27:57
nic	nai	LOGON_EVENT	COD	01-05-16 07:28:19
nic		LOGOFF_EVENT	COD	01-05-16 07:58:14
fjp		OPERATOR_EVENT	COD	01-05-16 07:58:26
nic		OPERATOR_EVENT	DPS	01-05-16 08:00:16
nic	nai	LOGON_EVENT	DPS	01-05-16 08:01:33
nic		LOGOFF_EVENT	DPS	01-05-16 08:31:57
bry		OPERATOR_EVENT	DPS	01-05-16 08:32:07
ree		OPERATOR_EVENT	COD	01-05-16 09:00:37
fjp		OPERATOR_EVENT	COD	01-05-16 09:24:21
meh		OPERATOR_EVENT	DPS	01-05-16 09:42:57
ada		OPERATOR_EVENT	COD	01-05-16 09:45:13
kgb		OPERATOR_EVENT	COD	01-05-16 10:44:17
ada		OPERATOR_EVENT	DPS	01-05-16 11:24:17
al2		OPERATOR_EVENT	DPS	01-05-16 11:39:46
ada		OPERATOR_EVENT	DPS	01-05-16 11:39:46
kgb		OPERATOR_EVENT	COD	01-05-16 11:39:46
grt		OPERATOR_EVENT	COD	01-05-16 11:58:55
cra		OPERATOR_EVENT	DPS	01-05-16 11:59:26
mcn		OPERATOR_EVENT	COD	01-05-16 12:25:25
mcn		OPERATOR_EVENT	COD	01-05-16 12:41:53
cra		OPERATOR_EVENT	DPS	01-05-16 12:41:53
al2		OPERATOR_EVENT	DPS	01-05-16 12:41:54
al2		OPERATOR_EVENT	DPS	01-05-16 12:42:10
cra		OPERATOR_EVENT	DPS	01-05-16 12:42:11
mcn		OPERATOR_EVENT	COD	01-05-16 12:42:11
zie		OPERATOR_EVENT	COD	01-05-16 12:52:52
meh		OPERATOR_EVENT	DPS	01-05-16 12:59:12
cra		OPERATOR_EVENT	COD	01-05-16 13:29:10
zie		OPERATOR_EVENT	DPS	01-05-16 13:31:13
dic		OPERATOR_EVENT	COD	01-05-16 13:35:05
cra		OPERATOR_EVENT	COD	01-05-16 13:56:40
cra		OPERATOR_EVENT	COD	01-05-16 14:00:59
ada		OPERATOR_EVENT	DPS	01-05-16 14:25:42
al2		OPERATOR_EVENT	DPS	01-05-16 14:37:12
ada		OPERATOR_EVENT	DPS	01-05-16 14:37:12
cra		OPERATOR_EVENT	COD	01-05-16 14:37:12
zie		OPERATOR_EVENT	COD	01-05-16 14:57:38
dic		OPERATOR_EVENT	COD	01-05-16 15:29:06
meh		OPERATOR_EVENT	COD	01-05-16 15:58:14
cra		OPERATOR_EVENT	DPS	01-05-16 15:59:05
meh		OPERATOR_EVENT	COD	01-05-16 16:04:46
al2		OPERATOR_EVENT	DPS	01-05-16 16:04:46
cra		OPERATOR_EVENT	DPS	01-05-16 16:04:46
al2		OPERATOR_EVENT	DPS	01-05-16 16:04:48
meh		OPERATOR_EVENT	COD	01-05-16 16:04:49
cra		OPERATOR_EVENT	DPS	01-05-16 16:04:49
ada		OPERATOR_EVENT	COD	01-05-16 16:59:15
al2		OPERATOR_EVENT	DPS	01-05-16 17:27:53
ada		OPERATOR_EVENT	COD	01-05-16 17:27:54
cra		OPERATOR_EVENT	DPS	01-05-16 17:27:54
al2		OPERATOR_EVENT	DPS	01-05-16 17:30:15
wei		OPERATOR_EVENT	COD	01-05-16 17:41:31
hup		OPERATOR_EVENT	DPS	01-05-16 17:41:49
wei	sti	LOGON_EVENT	COD	01-05-16 17:57:52
sti	xan	OPERATOR_EVENT	COD	01-05-16 17:58:06
xan		LOGOFF_EVENT	COD	01-05-16 18:27:51
wei		OPERATOR_EVENT	COD	01-05-16 18:28:05
wei		OPERATOR_EVENT	COD	01-05-16 19:07:36
aar		OPERATOR_EVENT	COD	01-05-16 19:08:59
acc		OPERATOR_EVENT	COD	01-05-16 19:12:10
wei		OPERATOR_EVENT	COD	01-05-16 19:12:44
kay		OPERATOR_EVENT	DPS	01-05-16 19:25:16
hup		OPERATOR_EVENT	COD	01-05-16 19:54:51
fre		OPERATOR_EVENT	DPS	01-05-16 19:58:06
hup		OPERATOR_EVENT	COD	01-05-16 20:46:41
al2		OPERATOR_EVENT	DPS	01-05-16 20:46:42
fre		OPERATOR_EVENT	DPS	01-05-16 20:46:43
aar		OPERATOR_EVENT	DPS	01-05-16 20:49:02
kay		OPERATOR_EVENT	COD	01-05-16 20:55:58
vhj		OPERATOR_EVENT	COD	01-05-16 20:57:45
fre		OPERATOR_EVENT	COD	01-05-16 21:21:57
kay		OPERATOR_EVENT	DPS	01-05-16 21:26:58
aar		OPERATOR_EVENT	COD	01-05-16 21:40:22
xan		OPERATOR_EVENT	COD	01-05-16 22:27:42
wei		OPERATOR_EVENT	DPS	01-05-16 22:49:59
acc		OPERATOR_EVENT	COD	01-05-16 22:55:19
aar		OPERATOR_EVENT	COD	01-05-16 23:03:52
ah2		OPERATOR_EVENT	MIN	01-05-16 04:01:20
art		OPERATOR_EVENT	MIN	01-05-16 04:01:33
nbb		OPERATOR_EVENT	MIN	01-05-16 04:55:38
nbb	rhd	LOGON_EVENT	MIN	01-05-16 04:58:23
nbb		LOGOFF_EVENT	MIN	01-05-16 06:00:53
mwb		OPERATOR_EVENT	MIN	01-05-16 06:01:06
nbb		OPERATOR_EVENT	MIN	01-05-16 06:58:11
nbb	rhd	LOGON_EVENT	MIN	01-05-16 06:58:29
nbb		LOGOFF_EVENT	MIN	01-05-16 08:00:32
mwb		OPERATOR_EVENT	MIN	01-05-16 08:00:48
nbb		OPERATOR_EVENT	MIN	01-05-16 08:52:10
nbb	rhd	LOGON_EVENT	MIN	01-05-16 08:57:50
nbb		LOGOFF_EVENT	MIN	01-05-16 09:49:46
mwb		OPERATOR_EVENT	MIN	01-05-16 09:50:00
man		OPERATOR_EVENT	MIN	01-05-16 10:01:01
ah2		OPERATOR_EVENT	MIN	01-05-16 10:10:01
ah2		OPERATOR_EVENT	MIN	01-05-16 11:02:22
fis		OPERATOR_EVENT	MIN	01-05-16 11:02:29
ah2		OPERATOR_EVENT	MIN	01-05-16 11:58:30
ah2		OPERATOR_EVENT	MIN	01-05-16 13:08:54
fis		OPERATOR_EVENT	MIN	01-05-16 13:09:08
ah2		OPERATOR_EVENT	MIN	01-05-16 14:00:35
ah2		OPERATOR_EVENT	MIN	01-05-16 16:03:54
fis		OPERATOR_EVENT	MIN	01-05-16 16:04:06
fis		OPERATOR_EVENT	MIN	01-05-16 16:04:49
fis		OPERATOR_EVENT	MIN	01-05-16 16:05:17
ah2		OPERATOR_EVENT	MIN	01-05-16 16:55:52

[zmbd]

YIKES that's quite the logic mapping...

+ Your log creator is sadistic.

+ IMHO, From a business stand point, the log would have a difficult time passing even the most basic of regulatory audits.

It's going to take me a while to digest this information. I think I have it and then re-read your post and the target shifts.

+Another thing, I've had a chance to look at the file in the OP and the description therein more closely at home... the raw data and your subsequent explanation is substantially different than originally described.

+ Finally, what I can tell you:
[Date] and [Time] are reserved words and though they may not give you issues now, they will most likely give you issues down the road...
Problem names and reserved words in Access

[zmbd]

1) This is the distilled logic I've pulled from your explanation
I am using Open and Close as general Classification of event
I am using "Dedicated" to indicate that the same user and position are required
Alpha refers to only alphabetical entry - user specific
AlphaNum refers an entry with both alphabetical and numeric entry - not user specific

[User    ][Trainee][Event ][Position][class          ][last Class      ]
[        ][Alpha  ][OpE   ][same    ][Opens position ][                ]
[AlphaNum][       ][OpE   ][same    ][               ][Closes position ]
[Alpha   ][       ][OpE   ][same    ][               ][Closes position ]
[Alpha   ][       ][LogOn ][same    ][Opens Dedicated][                ]
[Alpha   ][       ][LogOff][same    ][               ][Closes Dedicated]

So we have five conditions that trigger four classes of events
Two of the four classes of events are dedicated to each other.

Here's the spoiler in the logic:

The generic user initial is used when there is no other person logging in for the time being. May be after some time another person will login that position.

Using the above logic, the "AA#" will close the oldest open non-dedicated position (line 3).

[rajeevs]

Dear zmbd
May I put it in some simple logic because I am confused with the earlier post from you. I will PM you now

[rajeevs]

Hi
The data is actually from an application log.
Imagine it as few physical computers are connected together and running the same application. The PCs are the positions. Some positions (or PCs) run 24 Hrs and some of them not 24 hrs. Whenever a user login to the position (PC) that event is logged by the application in the event log as "Operator_event " with the user's initial, position name and time. But when he leave the position there is no logout recorded by the application. Instead, if it is a 24 Hr position another person will come and login and the first person will be logged out. And that event is logged again as "Operator_event " with new person's initial,positio n and current time. If it is not a 24 hr position then the first user may logout from the position by using the alpha numeric generic (there is a set of alphanumeric initials available)initi al and that event is also logged as "Operator_event " with the same user's initial, position name and current time.
The event name in the log "LOGON_EVEN T" is happening when there is a trainee sitting with the actual person, the application allows multi user login input. At the same time or within few seconds the event log creates "Operator_event " entry also for the instructor.When the trainee and instructor leaves the application, the event log creates the entry named as "LOGOFF_EVE NT" with the Instructor name, trainee name, position and logged out time.
This log closes everyday midnight and saves in a server folder as csv. So there could be some positions which will have next user "Operator_event " happens only the next day.
I am trying to tabulate this data for further analysis in an access table with a structure as DateOfLog, User, Trainee, SOLO(Yes/No), Login, Logout.

Please excuse me if this was a long and detailed one. I thought this way I can express my logic
Thank you for all the support

[zmbd]

Ok,
Your table structure
[DateOfLog][User][Trainee][SOLO][LogIN][LogOUT]

Would you consider adding [PC_Seat]? This way in the records, you can search for both the User and the Position. This may provide something unique for your events so that you can properly fill in the [LogOUT] times. Even it is nothing more than locating the first record
WHERE ([PC_Seat]=[POSTITION]) AND (LogOUT is NULL))
for those cases where the user is entering "AA#" for their initial. You also need this information so that you can find which pc to logout from and to login to along with how to slot the [Trainee] to the [User] for the [Position]

From there I think it's a nested select-case. The outer to determine the event. The LOGON/LOGOFF might be able to be handled within the outer conditional. The inner conditional for the Operator_event will have to determine if the Initials are alphanumeric or just alpha in nature, trainee, etc... and act appropriately.

(I tend to avoid nested if-then logic :) )

The following is pseudo/logic-code
SELECT CASE (EVENTNAME)
   CASE "LOGON_EVENT"
      'Logic to handle creating the new record
      'is there a trainee to deal with
   CASE "LOGOUT_EVENT"
      'Logic to find the PC_Seat and User and update 
record
      'is there a trainee to deal with
   CASE "Operator_Event"
      >Function to check for numeric value in userintils 
      SELECT CASE (FNC(userintils))
         CASE alphanumeric
            'logic to find the first PC_Seat record that matches and doesn't have a logout time and update
            'logic to create the new record for the user and PC_Seat
            'is there a trainee to deal with
         CASE Alpha
            'logic to find the first matching PC_Seat without a logout time. Prompt the User to logout the prior user using your alphanumeric
            'logic to create new record for the User and PC_Seat
            'is there a trainee to deal with
      end select (userintils)
END select (EVENTNAME)

I still think that using the ADODB method to connect the CSV/XLS file is the better way to go... I believe one can also use the SQL IN Clause for DAO; however, I haven't tried that method on either type of file. I may try that later tonight depending on the kids :)

I also use a variation of NeoPAs Select a File or Folder using the FileDialog Object that I've used since, well, a very long time, I use a little simpler version I pulled out of a textbook so many years ago; however, because one can use the for each method with the .selecteditems property of the object with a multi select option set to true, one can step thru the selected files without additional code and return the whole path with the file's name, which can be split for the ADODB connection string or maybe better yet for the IN clause.

-Z

[rajeevs]

Dear zmbd
Thank you for the reply and code. I think I mentioned already that the table has a field "POSITION". I will try the logic and let you know the result, even I am not so good in coding.
There is one piece I forgot to mention that the during the time period between on and off of a person, he should not have another position in his time.
eg:
if AAA is on position DPS from 01:00 to 02:30 then there should not be a different position(eg. AMR) for AAA in between those 01:00 to 02:30.
If you use my sample data and try to import using the code I have posted earlier this is what is happening.
I am waiting for your reply

[zmbd]

You would simply add the check:
WHERE ([USER]=(operator loging) AND (LogOUT is NULL)) to the logic. More than likely this check would have to be in the LOGON_EVENT and in the OPERATOR_EVENT of the outer conditional.

In the Inner conditional of the CASE Operator_Event > Case ALPHA, is where one would put the logic to check for that user having an unclosed session. I would think that this restriction will play havoc with tracking your PC_Seats that have 24Hour sessions!