Time differences - Query

[mralmackay]

Wondered if you could help with the following query that I need to do.

My table (Usage) contains two fields (Time & User)
I want to be able to calculate the difference between the first entry
for ther user in the table and the last entry for that user in the
table.

e.g. the data would appear like this:

User Time on system (This is calculated as
1 00:10:00 Last row where user 1 exists - First row
2 00:15:00 Last row where user 2 exists - First row

Note: The field whereby this data is calculated against is in the
following format HH:MM:SS

Any help on this would be greatly appreciated as I need to get this
done asap.

Many Thanks as always, Al.

 

[Allen Browne]

Use the query below as an example.

It groups by each user, gets the oldest and newest time for that user, and
uses DateDiff() to get the difference in minutes:

SELECT Table8.UserID,
DateDiff("n",Min([Table8].[UserTime]),Max([Table8].[UserTime])) AS Minutes
FROM Table8
GROUP BY Table8.UserID;

 

[mralmackay]

That's brilliant, cheers.

One further question (apologies), is it possible to also exclude any
entries where the result of the DateDiff formula is < 1 (or null)?

Appreciate your help. Thanks, Al.

 

[Allen Browne]

If you want only the records where the Max of the user time is different
than the Min if the user time, a HAVING clause should do that:

SELECT Table8.UserID,
DateDiff("n",Min([Table8].[UserTime]),Max([Table8].[UserTime])) AS Minutes
FROM Table8
GROUP BY Table8.UserID
HAVING Min(Table8.UserTime) <> Max(Table8.UserTime);