comparing withing a recordset

[ifoundgoldbug]

Greetings

I am having an interesting problem.

here is the SQL statement i am using.

SELECT [Work Order].[Tool #], [Work Order].[Est Comp Date], [Work
Order].Reason, [Work Order].Status

FROM [Work Order]

WHERE ((([Work Order].[Est Comp Date])>=[work order].PMDate) AND
(([Work Order].Reason)="Maintain") AND (([Work Order].Status)="Done"));

I want it look grab a tool and look the entirity of the recordset to
see is maintence was performed after the PM date. but I can guarentee
that estimated complete date and the PMDate that it is comparing
agianst will be in different records. any ideas how i can make this
work. I am going to need some joins ect. any help would be
appreciatited

 

[Douglas J. Steele]

You'll have to explain how your data is arranged.

Give an example of the estimated complete date and the PM date being in
different records.

Also, is there a change that either (or both) of the two date fields can be
Null?