Need help from GURUs!!!

Hello folks,

I have an MS Access database where I have info regarding the applications used by the users. The info stored in the database is like-

User - Application - StartTime - EndTime
--------------------------------------------------
John - MS Word - 10:11 AM - 11:50 AM
John - WinAmp - 10:40 AM - 11:16 AM
John - MS Excel - 11:13 AM - 3:31 PM
John - Powerpoint - 2:21 PM - 3:33 PM

Sam - MS Excel - 10:02 AM - 2:12 PM

I need to find the 'Effective Usage' (Time for which the PC was actually used) of the machine by the user using a query.

I am able to find the 'Effective Usage' in case the applications are executed in series (that is by finding the summation of the date difference between the StartTime and the EndTime). But, in real world scenario the applications are executed parallel (as shown in exmple above). So how do I implement the solution using the existing data?

Thanx,

Subhendu

Subqueries could be your answer.
Something like this:

Code:

---

SELECT        User AS *****er,
        (SELECT MAX(EndTime)
        FROM *****age
        WHERE User = *****er
        GROUP BY User) -
        (SELECT MIN(StartTime)
        FROM *****age
        WHERE User = *****er
        GROUP BY User) AS Usage
FROM *****age

---

However, since you're using Access, take a look at domain aggregates too (look for DMax and DMin in help); they might be easier...

[You have no Date field in your example, but assuming that you track this too, you'll need to insert that into your query and subqueries...]