Query Design

[Mathew]

Hi~

Below is a sample of my table structure and data:

GroupName Dt_time Score
Group1 04/01/2004 12:00PM 61
Group1 04/01/2004 1:00PM 49
Group1 04/01/2004 2:00PM 37
Group1 04/01/2004 4:00PM 58
Group1 04/01/2004 6:00PM 62
Group2 04/01/2004 12:00PM 69
Group2 04/01/2004 1:00PM 71
Group2 04/01/2004 3:00PM 49
Group2 04/01/2004 7:00PM 61
Group2 04/01/2004 10:00PM 68
Group3 04/01/2004 4:00PM 69
Group3 04/01/2004 5:00PM 73
Group3 04/01/2004 11:00PM 81

I want to run a query that will tell me the total amount
of time that a particular group runs a score lower than
60. The results would look like below:

Group1 5 Hours (or 300 minutes)
Group2 4 Hours (or 240 minutes)

(Group3 would not show up because they do not have a score
below 60)

Each group may have as many as 70-100 entries that spans
over a period of days ... Does anyone have any suggestions
as to how to approach this?

Any help would be much appreciated.

Mathew

 

[John Spencer (MVP)]

This is not fully tested, but with the data you gave us, you could try an SQL
statement that looks like the following.

SELECT A.[Group], Sum(DateDiff("h",.[Dt_time],[A].[DT_Time])) AS DiffHours
FROM Source AS A LEFT JOIN Source AS B ON A.Group = B.Group
WHERE A.DT_Time=(Select Min(Dt_Time)
FROM Source
WHERE Dt_Time > B.Dt_Time AND [Group] = B.[Group])
AND B.Score<60
GROUP BY A.Group

Replace "Source" with the name of your table.