Number of concurrent users

[valbor]

I have a table which provides SessionId, SessionStartDate and SessionEndDate. How to create a query to determine max number of concurrent sessions each day?

[NTC]

Well first you need the date being inspected. Call this the CheckDate. Then you need to compare whether StartDate is <= CheckDate AND EndDate is >= CheckDate

Get this first query to return all the records correctly.

Then make a second query using the first query as its source, and make it an Aggregate Query (epsilon E in toolbar/ribbon) - set to count records.

An Access textbook will have examples of these tasks.

Hope this helps.

[valbor]

Thanks for the reply. This approach will provide me with the total number of sessions per day. What I'm looking for is MAX number of concurrent sessions daily. For example, session A started at 1:03:01 and ended at 1:55:00; session B started at 1:55:03 and ended at 2:05:00; session 3 started at 3:04 and ended at 4:05. Total number of sessions is 3, but max of concurrent sessions is 2 - only 2 sessions (A and B) overlap.

[NTC]

You would have to craft a custom sql statement with a self join defined by overlapping times. Nothing vanilla embedded into Access has this type solution. Too complicated for me to attempt to do off the cuff.

[weekend00]

you can write code to scan the table, count the "online" users, save the max number while scaning. do forget the sessions start before this date and still active.
Sample code:

Public Function Maxconcurrent(dDate As Date)
    Dim rs As dao.Recordset
    Dim maxcount, curcount As Long
    Set rs = CurrentDb.OpenRecordset("SELECT id,sessionTable.sessionStart, 'start' as ev FROM sessionTable where format(sessionstart, 'yyyymmdd')='" & Format(dDate, "yyyymmdd") & "' UNION SELECT id,sessionTable.sessionend, 'end' as ev FROM sessionTable where format(sessionend, 'yyyymmdd')='" & Format(dDate, "yyyymmdd") & "' ORDER BY sessionStart")
    maxcount = 0
    curcount = 0
    Do While Not rs.EOF
        If rs!ev = "start" Then
            curcount = curcount + 1
            If curcount > maxcount Then maxcount = curcount
        Else
            curcount = curcount - 1
        End If
        rs.MoveNext
    Loop
    Set rs = Nothing
    ' plus session start before this date and end in/after this date or null
    Maxconcurrent = maxcount + DCount("*", "sessiontable", "format(sessionstart, 'yyyymmdd') < '" & Format(dDate, "yyyymmdd") & "' and ( format(sessionend, 'yyyymmdd') >= '" & Format(dDate, "yyyymmdd") & "' or (sessionEnd is null))")
End Function