Query from VBA: sum of matching record pairs

[Onno]

Hi, I hope you can help me.

I have a 'logging' database with records like this
<Time> <Some Event> <Param1> <Param2> <Param3>, etc.

Different instances of an application wrote 'start' and 'end' events
to the database and used the param fields to indentify themselves (by
IP address and other items), so the table looks something like this:
2008-09-02 11:58:41, App Started, 199.144.248.152,,
2008-09-02 12:58:41, App Started, 199.144.248.155,,
2008-09-02 13:00:00, App Ended, 199.144.248.152,,
2008-09-02 13:01:11, App Ended, 199.144.248.155,,

In VBA, I want to calculate how long the application was executed (so
the sum of the time fields of all the "App Started-App Ended" record
pairs that belong together based on Param1.

What is the best approach?

I was opening the database using a recordset and walking through all
records like the code below, but got stuck. There must be a better
way. Any better ideas using some smart queries?

Orignal approach:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset([query that only shows the App Started and
Ended events)
Do While Not rst.EOF
If rst!ActionType = 1 Then ' App started
' Now what
ElseIf rst!ActionType = 2 Then ' TAF app closed
' must find matching start action, then add the time
diffs to a sum
End If
rst.MoveNext
Loop

btw: I am not a heavy SQL / Access expert

TIA

 

[KARL DEWEY]

Try using these two queries ---
Onno_Sort --
SELECT Q.Param1, Q.Time, Q.[Some Event], (SELECT COUNT(*) FROM Onno Q1
WHERE Q1.[Param1] = Q.[Param1]
AND Q1.[Time] <= Q.[Time]) AS Rank
FROM Onno AS Q
ORDER BY Q.Param1, Q.Time;

SELECT Onno_Sort.Param1, Onno_Sort.[Some Event], Onno_Sort.Time,
Onno_Sort_1.[Some Event], Onno_Sort_1.Time,
[Onno_Sort_1].[Time]-[Onno_Sort].[Time] AS Time_Diff
FROM Onno_Sort INNER JOIN Onno_Sort AS Onno_Sort_1 ON Onno_Sort.Param1 =
Onno_Sort_1.Param1
WHERE (((Onno_Sort_1.Rank)=[Onno_Sort].[Rank]+1));

 

[Onno]

Wow, that's some serious SQL stuff! I took me a while to figure out
how that works, and I'm still not 100% sure.

- Can it handle missing start or end events? What will happen?
- The resulting Time_Diff shows small fractions. What are those? How
can I format them in time differences in hours, minutes and seconds
(mind you, can't use a hh:mm:ss format on it, it resets after 23
hours, 60 mins, etc).