tricky sql statment

[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.

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

grab tool x

look at the max 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

 

[Guest]

Try this --
SELECT TblGoldBug.[Tool#], Max(TblGoldBug.[Est Comp Date]) AS [MaxOfEst Comp
Date], Max(TblGoldBug_1.PMDate) AS MaxOfPMDate
FROM TblGoldBug INNER JOIN TblGoldBug AS TblGoldBug_1 ON TblGoldBug.[Tool#]
= TblGoldBug_1.[Tool#]
WHERE (((TblGoldBug.Reason)="Maintence") AND ((TblGoldBug.Status)="Done"))
GROUP BY TblGoldBug.[Tool#];