I can't figure this out. Sorting/Comparing/TimeDiff

[Monkerz]

Hello all,

I have been working on this for the last 3 days and cannot seem to get the results I am looking for. I am an official access noob.



I have a network monitor that sends emails to a monitor mailbox with a circuit's line status changes. I have linked this mailbox to my newly created database but cannot find out how to query my data to get the results I am looking for. I would like the resulting table to display each site that has changed status, the circuit size of the site, how many times the line went down, and the amount of downtime accrued.

I also have to keep in mind, the notification are only sent out after the circuit has been down for 5 min, so I will need to add the 5 mins to the down time for each occurrence.

The table below shows both the RAW Data I am receiving from Outlook and the results I would like to see.

Can anyone point me in the right direction or tell me if this is even possible?

ModifiedQueue (Table)			
Received	Location	Status	CircuitSize
6/27/11 12:24 PM	Centennial, CO	has gone down	Sensor: VzB (T1)
6/27/11 12:42 PM	Lorton, VA (NOVA)	has gone down	Sensor: VzB (1024k)
6/27/11 12:51 PM	Lorton, VA (NOVA)	has come up	Sensor: VzB (1024k)
6/27/11 12:56 PM	Alexander, AR	has gone down	Sensor: VzB (512k)
6/27/11 12:59 PM	Alexander, AR	has come up	Sensor: VzB (512k)
6/27/11 1:03 PM	Commerce, CA	has gone down	Sensor: VzB (T1)
6/27/11 1:06 PM	Horn Lake, MS	has gone down	Sensor: VzB (T1)
6/27/11 1:37 PM	Centennial, CO	has come up	Sensor: VzB (T1)
6/27/11 1:56 PM	Alexander, AR	has gone down	Sensor: VzB (512k)
6/27/11 2:59 PM	Alexander, AR	has come up	Sensor: VzB (512k)
6/27/11 3:06 PM	Horn Lake, MS	has come up	Sensor: VzB (T1)
6/27/11 4:03 PM	Commerce, CA	has come up	Sensor: VzB (T1)
			
			
Desired Results		
Location	CircuitSize	Occurrences	Downtime (hh:mm:ss)
Alexander, AR	Sensor: VzB (512k)	2	1:16:00
Centennial, CO	Sensor: VzB (T1)	1	1:18:00
Commerce, CA	Sensor: VzB (T1)	1	3:05:00
Horn Lake, MS	Sensor: VzB (T1)	1	2:05:00
Lorton, VA (NOVA)	Sensor: VzB (1024k)	1	0:14:00

Thank you in advance!

-Monk

[ajetrumpet]

I'm pretty sure you can do this with a running total query concept plus a group by concept. But it's probably way too complicated. You really should loop the table and produce and output table based on your input criteria. I'm sure that'll be the easiest way to do this.

[Monkerz]

Would you have any good websites for tutorials on these suggestions? I really am a complete noob when it comes to access. I am a great network engineer, but if anything was my downfall, access would be it.

[ajetrumpet]

there really are no websites for that kind of stuff.

here's the code I would use if I were you (written here and not tested):

dim uniques() as variant 'array - stores unique "location/circuit" size combos 
dim db as dao.database
dim rs as dao.recordset 'table to read
dim rs2 as dao.recordset 'table to write
dim i as long 'array counter
dim downtime as date
dim uptime as date
dim occurrances as long 'occurances of issue
dim totaltime as long 'total downtime of a unique record

set db = currentdb
i = 0
occurrances = 0

docmd.copyobject , "OUTPUT TABLE", actable, "table" 'copy source as an output table
db.execute "DELETE * FROM [OUTPUT TABLE]" 'empty new table for input

set rs = db.openrecordset("select distinct [location], [circuitsize] from table")

rs.movelast
rs.movefirst

'get unique combos of "location/circuit"
  with rs
    do until .eof
      redim preserve uniques(i)
      uniques(i) = ![location] & "|" & ![circuitsize]
      i = i + 1
        .movenext
    loop
  end with

rs.close

set rs = db.openrecordset("select * from table order by [received]") 'read
set rs2 = db.openrecordset("OUTPUT TABLE") 'write

rs.movelast
rs.movefirst

'start looping table 
with rs
    for i = 0 to ubound(uniques()) 'loop array of uniques
          do until .eof
            if uniques(i) = ![location] & "|" & ![circuitsize] then 'our record?
              if ![status] = "has gone down" then 'new occurrance
                downtime = ![received] 'assumes we always start at "has gone down" status
              else 'reached "uptime" record for this occurrance.  record data
                uptime = ![received]
                totaltime = totaltime + DATEDIFF(use "downtime" and "uptime" as args.  format accordingly.)
                occurrances = occurrances + 1
              end if
            end if
                .movenext
          loop

              rs2.addnew 'add data to output table
              rs2!location = left(uniques(i), instr(uniques(i), "|"))
              rs2!circuitsize = mid(uniques(i), instr(uniques(i), "|" + 1, len(uniques(i)) - instr(uniques(i), "|"))
              rs2!occurrances = occurrances 
              rs2!downtime = totaltime
              rs2.update

                .movefirst 'back to top of table
                  'reset cumulative vars
                  occurrances = 0
                  totaltime = 0
    next i
end with

rs.close
rs2.close

set rs = nothing
set rs2 = nothing
set db = nothing

the codes in RED are the ones you will have to change to fit your needs. Obviously the format of the output for DOWNTIME in the output table will have to change. the TOTALTIME variable is also a long. you might have to FORMAT() it everytime it's recorded in the output table. e.g. - you can accumulate it as minutes and format it when it's ready to be written to rs2.

[Monkerz]

Thank you, ajetrumpet for your reply. Before I saw your reply, I had received a call from my brother explaining how to accomplish what I need. He instructed me in creating a few queries and tables, then creating a button and a script that ran when the button was clicked. It appears the script is doing what I need, I just have to verify that the data is correct.

Do this look like a good way to accomplish what I need?

Option Compare Database

Private Sub Command0_Click()
    DoCmd.SetWarnings False
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Query = "SELECT MONK.Location, MONK.Status, MONK.Received, MONK.CircuitSize " & _
            "FROM MONK " & _
            "WHERE (((MONK.Status) = "" is above 65% threshold"")) " & _
            "ORDER BY MONK.Location, MONK.Received;"
    rs.Open Query, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
    Dim rs1 As ADODB.Recordset
    Set rs1 = New ADODB.Recordset
    Query = "DELETE [Monk Report].* FROM [Monk Report];"
    DoCmd.RunSQL Query
    Dim loc, CircuitSize As String
    Dim start_time, end_time As Date
    loc = rs("Location")
    While Not rs.EOF
        loc = rs("Location")
        CircuitSize = rs("CircuitSize")
        start_time = rs("Received")
        rs.MoveNext
        If Not rs.EOF Then
            If loc = rs("Location") Then
                end_time = rs("Received")
                Set rs1 = Nothing
                Set rs1 = New ADODB.Recordset
                Query = "SELECT MONK.Location, MONK.Status, MONK.Received " & _
                        "FROM MONK " & _
                        "WHERE (((MONK.Location)=""" & loc & """) AND ((MONK.Status)="" is below threshold"") AND ((MONK.Received)>#" & start_time & "# And (MONK.Received)<#" & end_time & "#));"
                rs1.Open Query, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
                If Not rs1.EOF Then
                    Query = "INSERT INTO [Monk Report] ( Location, CircuitSize, [Above Threshold], [Below Threshold] ) " & _
                            "SELECT """ & loc & """ AS Expr1, """ & CircuitSize & """ AS Expr2, #" & start_time & "# AS Expr3, #" & DateAdd("n", 10, rs1("received")) & "# AS Expr4;"
                    DoCmd.RunSQL Query
                End If
            Else
                Set rs1 = Nothing
                Set rs1 = New ADODB.Recordset
                Query = "SELECT MONK.Location, MONK.Status, MONK.Received " & _
                        "FROM MONK " & _
                        "WHERE (((MONK.Location)=""" & loc & """) AND ((MONK.Status)="" is below threshold"") AND ((MONK.Received)>#" & start_time & "#));"
                rs1.Open Query, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
                If Not rs1.EOF Then
                    Query = "INSERT INTO [Monk Report] ( Location, CircuitSize, [Above Threshold], [Below Threshold] ) " & _
                            "SELECT """ & loc & """ AS Expr1, """ & CircuitSize & """ AS Expr2, #" & start_time & "# AS Expr3, #" & DateAdd("n", 10, rs1("received")) & "# AS Expr4;"
                    DoCmd.RunSQL Query
                End If
            End If
        Else
            Set rs1 = Nothing
            Set rs1 = New ADODB.Recordset
            Query = "SELECT MONK.Location, MONK.Status, MONK.Received " & _
                    "FROM MONK " & _
                    "WHERE (((MONK.Location)=""" & loc & """) AND ((MONK.Status)="" is below threshold"") AND ((MONK.Received)>#" & start_time & "#));"
            rs1.Open Query, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
            If Not rs1.EOF Then
                Query = "INSERT INTO [Monk Report] ( Location, CircuitSize, [Above Threshold], [Below Threshold] ) " & _
                        "SELECT """ & loc & """ AS Expr1, """ & CircuitSize & """ AS Expr2, #" & start_time & "# AS Expr3, #" & DateAdd("n", 10, rs1("received")) & "# AS Expr4;"
                DoCmd.RunSQL Query
            End If
        End If
    Wend
    DoCmd.OpenQuery "Format for Report", acViewNormal, acEdit
End Sub

Edit: This is the same code used for downtime, but I modified it to work has a "Possible Bandwidth Upgrade" report as well. This access stuff is great.

[ajetrumpet]

if it gets you what you need, obviously it's right. dont ya think?

[Monkerz]

if it gets you what you need, obviously it's right. dont ya think?

I was just going to see if you had suggestions to perfect what I have. Obviously it is working, and I am happy.

[ajetrumpet]

code is never perfect, my friend.

this is marked as solved!