[AccessD] I need a better way to do this - looping

SELECT T1.IT_Well, IIf(((SELECT IIF(IsNull(Max(IT_Date)),0,Max(IT_Date))
FROM dbo_Inj_Tests WHERE (Lin_Pres-Tbg_Pres)>=100 AND
IT_Well=T1.IT_Well)-(SELECT IIF(IsNull(Max(IT_Date)),(SELECT
Min(IT_Date) FROM dbo_Inj_Tests WHERE
IT_Well=T1.IT_Well)-1,Max(IT_Date)) FROM dbo_Inj_Tests WHERE
(Lin_Pres-Tbg_Pres)<100 AND IT_Well=T1.IT_Well))<0,0,(SELECT
IIF(IsNull(Max(IT_Date)),0,Max(IT_Date)) FROM dbo_Inj_Tests WHERE
(Lin_Pres-Tbg_Pres)>=100 AND IT_Well=T1.IT_Well)-(SELECT
IIF(IsNull(Max(IT_Date)),(SELECT Min(IT_Date) FROM dbo_Inj_Tests WHERE
IT_Well=T1.IT_Well)-1,Max(IT_Date)) FROM dbo_Inj_Tests WHERE
(Lin_Pres-Tbg_Pres)<100 AND IT_Well=T1.IT_Well)) AS NumberOfDaysOver100
FROM dbo_Inj_Tests AS T1
GROUP BY T1.IT_Well;

Ok, this is plain text, so you shouldn't get word wrap issues, but who
knows.  To pull off what you are really looking for, the subquery gets a
little more complex.

I went off the four fields you gave me, with the table name, so this
query should just work by cutting and pasting the SQL into a new query.

Here's the results:

For each IT_Well, if the latest record is less then 100, then it returns
0 for 'NumberOfDaysOver100'.  If the latest record is equal or greater
then 100, then it returns the number of DAYS that the well has been over
100.  Thus, if there IS a date where it was less then 100, then you get
the difference between that date and the existing date.  Ie:

12/29/2007 - 300
12/27/2007 - 95

This set (whether there is a record for the 28th or not (unless of
course there was one under 100) will return 2, which would be the number
of days over 100.  

If there are NO records where it's under 100, then it returns the number
of days between the first and last records, INCLUDING the last day, ie:

12/29/2007 - 300
12/27/2007 - 195

This would return 3, since it was over 100 on the 27th, it includes that
day.

Hope this helps.

Drew