How do I find another row in my same query/table where RowX.text = MyRow.text?

[weeblesue]

I have an Access Database to store my software license usage data and provide reports. The license manager is proprietary and has no reporting features whatsoever.



I import the data from the vendor log file:
User UserGroup UniqueIdentifierCookie Action Module DateTimeStamp
userid region text string of numbers CheckOut or CheckIn ModuleUsed [obvious]

The software assigns a unique identifier (called a cookie, not really a cookie as we know it from web browsers) when the user opens a module and takes a license. This is recorded with which module is used, and of course a date/time stamp. When the user checks in that license, the cookie is repeated with CheckIn as the action and another date/time stamp. So each step gets a separate line in the code.

I load the table as a recordset, and if the action is "checkout" then i read the cookie.

Here's where i get lost. I need to find the corresponding row in this same query where the cookie is the same, and read the timestamp from that row. Only right now, there are over 41,000 entries in this table and i don't want to have to cycle through 40,000 rows looking for whether the cookie matches. The table is only going to grow. The CheckIn data is typically well within 100 rows of the CheckOut data.

Once i read the checkin timestamp, i can manage the delta time.

but here's tricky part #2. i want to put the resultant delta time value in the row with the CheckIn, not the CheckOut.

How do I determine the row number of the CheckIn Data so i can put the delta time there?

I can accomplish this in Excel, but the file is HUGE and takes forever to load, filter, sort, save, etc (and i have calculation on Manual). Access is a much better place to do this, but i can't figure out this action.

I have searched this forum and found code that will loop me through the entire table, row by row, but I get stuck at the "find record" entry. From what I'm reading, FindRecord might not be what i need to use here.

Public Sub CalculateDelta()
Dim sSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.TableDef
Dim f As DAO.Field
On Error Resume Next
Set rst = CurrentDb.OpenRecordset("SELECT Cookie, Action, FinalDateTime, Delta FROM [Users Log Query02] ")
' From Users Log Query02, get Cookie, Action, FinalDateTime.
' This is already sorted by Module.
If Not (rst.EOF And rst.BOF) Then
    rst.MoveFirst
    Do Until rst.EOF = True
    TDelta = rst!Delta
    
    If TDelta Is Nothing Then
    ' Only need to run if there is no delta value already
    
    strAction = rst!Action
    
    If strAction = "CHECKOUT" Then
 
        ' Store Cookie as string
        strCookie = rst!Cookie
    
    'DoCmd.FindNext where Cookie = CookieString and Action = CHECKIN.???
    ' Get FinalDateTime of this record.
    ' DeltaTime = FinalDate2 - FinalDate1
    
    ' Place DeltaTime into blank Delta CHECKIN column.
      
        'Move to the next record.
        rst.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
rst.Close
End Sub

Can someone suggest what direction I should try?

Thanks...
Susan

[pbaldy]

What's the final goal of all this? If every cookie will have two records, a simple totals query can give you the two times. Given your process, the direct answer to your question is that you could open a second recordset inside the loop to find the corresponding check in, or perhaps simpler a DLookup().

[weeblesue]

The goal is to have the amount of time used by that user for that instance. CheckIn time - CheckOut time. But that can't be just a temporary value. I need it placed in a table or query somewhere, because i need to run several types of reports using those values, and i do not want to have to recalculate it each time.
Sample report types: weekly by user, weekly by group/region, weekly by module, monthly by each, user-specified date range by user/group/module, admin vs regular user, etc. The manager needs to be able to click on a report and get a result quickly, and an intermediate result would not provide that.

I will try a totals query. That just may be the shortcut that i have been looking for. Thanks!

[weeblesue]

Nope, a total query isn't going to work. There's no subtraction function for such a query.

I cannot add 45678 and 45789 and get the delta between the 2 values.

[pbaldy]

Really? This doesn't work?

SELECT Cookie, Min(FinalDateTime) As StartTime, Max(FinalDateTime) As EndTime, Max(FinalDateTime)-Min(FinalDateTime) As ElapsedTime
FROM [Users Log Query02]
GROUP BY Cookie

If not, can you provide a sample db with data to play with?

[weeblesue]

Your solution works! I got stuck thinking I couldn't refer to two different rows in the same line of the query. It's all in the way it's grouped!

Thank you, thank you, thank you!!

[pbaldy]

Happy to help!

[Missinglinq]

Two incidental things:

Not really germane, now, but you should know, for future reference

where RowX.text = MyRow.text

The Text Property is only available when a Control on a Form has Focus, and hence is seldom used in Access. The Text Property doesn't necessarily give the value of the given Field, but rather the data that is displayed in the Control when it has Focus.

If, for instance, you go to an existing Record, and

ControlX currently is ABC

then ControlX.Value = ABC.

If you enter ControlX (give it Focus) and enter XYZ, leaving the cursor in the Control, then

ControlX.Value = ABC

but

ControlX.Text = XYZ !

...i need to run several types of reports using those values, and i do not want to have to recalculate it each time...

Re-calculating Fields, when needed, is considered 'best practice' by every experienced Access developer that I'm acquainted with. In addition to the fact that you're unnecessarily storing data, the experts in this sort of thing will tell you this type of re-calculation, done later in a Query, Report or other Form will take less time than retrieving the piece of data! Also, there are conditions where either CheckIn time or CheckOut time could be modified but the calculation not run and stored, so the value would not be correct. Re-calculating, when needed, would avoid this problem.

Linq ;0)>