help with sql query

[ifoundgoldbug]

Greetings

I have a table arranged thusly

Reason Status Est Comp Date PMDate Tool#
7/14/06
WFT083
Maintence done 7/16/06 WFT083

what my sql statement MUST do is search by Tool # then find the most
recent PMDate then find if there is a work order with finished
maintence with a complete date AFTER the PMdate. also the returning
record will be a seperate record from the one with the PM date.

thanks for your help.

 

[John Spencer]

Something like the following query ***might*** work for you.

SELECT *
FROM TABLE as T
WHERE PMDate =
(SELECT Max(PMDATE)
FROM TABLE as Temp
WHERE Temp.[Tool#] = T.[Tool#])
OR T.[Est Comp Date] =
(SELECT Min([Est Comp Date])
FROM Table as Temp2
WHERE Status = "Done" AND
Temp2.[Tool#] = T.[Tool#] AND
Temp2.[Est Comp Date] >=
(SELECT Max(PMDATE)
FROM TABLE as Temp3
WHERE Temp3.[Tool#] = Temp2.[Tool#]))

 

[ifoundgoldbug]

Thank you very much for this hunk of code by my PUNY little brain is
getting knotted thinking about it this statement is significant'y
larger than any other that I have done. i am going to try to decipher
it and please point me in the rigth direction where i get off

all the records that I am uering are on table [Work Order] i am also
changing fields from [est comp date] to just [date]


SELECT T.PMDate, T.[Est Comp Date], T.[Tool #], *
FROM [work order] AS T
WHERE (((T.PMDate)=(SELECT Max(PMDATE)

' Selects the record of tool X with the latest PM date

FROM [work order] as Temp
WHERE Temp.[Tool#] = T.[Tool#]))) OR (((T.[Date])=(SELECT Max([Date])

'filters to see of the work order date is after that of the PMdate for
tool x

FROM [work order] as Temp2
WHERE Status = "Done" AND
Temp2.[Tool#] = T.[Tool#] AND
Temp2.[Date] >=
(SELECT Max(PMDATE)
FROM [work order] as Temp3
WHERE Temp3.[Tool#] = Temp2.[Tool#]))));
'further filters to checks if PM is finished.

Here is basically the logic that i am trying for with the sql statement

grab tool x

look at the mad PMDate for tool x

Look at all work orders for tool x whos status is "Done" and who's
reason is "Maintence" and finally whos Date is greater than the
PMDate.

if that makes sense.

Sorry for being so new I have only done very basic sql statements.

thanks again

Gold Bug