Query efficiency

[cheshire_smile]

I'm having tryouble running a query on a single table with 100,000 records. It was taking over an hour to run the query, so I tried indexing all the fields. This droped it down to about half an hour. I need it to run faster. Here is the query:

SELECT UpDn_Analysis.Terminal_Name,
Min(UpDn_Analysis.[Date/Time]) AS Down_Time,
UpDn_Analysis.Next_Up_Time AS Up_Time
 
FROM (
SELECT
[Temp_Table].Terminal_Name,
[Temp_Table].[Date/Time],
(SELECT Min(NxtUp.[Date/Time])
FROM [Temp_Table] NxtUp
WHERE NxtUp.[Terminal_Name] = [Temp_Table].[Terminal_Name]
AND NxtUp.[Date/Time] > [Temp_Table].[Date/Time]
AND NxtUP.[Dn Total] <> 0
AND NxtUP.[Up Total] <> 0) AS Next_Up_Time,
(SELECT Max(PrevDn.[Date/Time])
FROM [Temp_Table] PrevDn
WHERE PrevDn.[Terminal_Name] = [Temp_Table].[Terminal_Name]
AND PrevDn.[Date/Time] = [Temp_Table].[Date/Time]
AND PrevDn.[Dn Total] = 0
AND PrevDn.[Up Total] = 0) AS Previous_Dn_Time
FROM Temp_Table) AS UpDn_Analysis
WHERE (((UpDn_Analysis.[Date/Time])=[Previous_Dn_Time]))
GROUP BY UpDn_Analysis.Terminal_Name, UpDn_Analysis.Next_Up_Time) AS Outage_Times

Sample data:

[ID]-[Terminal_Name] ---[Date/Time]--[Up Total]-- [Dn Total]
1-----Terminal1-------7/1/2011 10:52-----1------------1
2-----Terminal1-------7/1/2011 10:53-----1------------1
3-----Terminal1-------7/1/2011 10:54-----1------------1
4-----Terminal1-------7/1/2011 10:55-----0------------0
5-----Terminal1-------7/1/2011 10:56-----1------------1
6-----Terminal1-------7/1/2011 10:57-----1------------1
7-----Terminal1-------7/1/2011 10:58-----1------------1
8-----Terminal1-------7/1/2011 10:59-----0------------0
9-----Terminal1-------7/1/2011 11:00-----0------------0
10----Terminal1-------7/1/2011 11:01-----0------------0
11----Terminal1-------7/1/2011 11:02-----1------------1
12----Terminal1-------7/1/2011 11:03-----1------------1
13----Terminal1-------7/1/2011 11:04-----1------------1
14----Terminal1-------7/1/2011 11:05-----1------------1
15----Terminal1-------7/1/2011 11:06-----1------------1

The purpose of this Query is to find outages. It provides the terminal name, the first timestamp where it shows 0 for both up and down and the next non-zero timestamp.



... I'm not verry good with VBA, I can read it and modify it, but writing it from scratch is a little difficult.

I don't know if splitting the fields up to several tables and creating 1-many relationships would improve it any.

Any help would very much appreciated.

Thx.

[cheshire_smile]

----------