I am trying to do a select query with three tables. The first table is pmtask, which has a child - one to many relationship with the pmaint table. I want to pull all tasks that the WONum column is blank and the PerformHow column = 'Hours' - that part I can do - see below.

My problem is I also need it to get the EquipmentID for that task from the Pmaint Table. So I did a join with the Pmaint Table and then linked the Pmaint table with the EquipHours table by EquipmentID.

Then in the EquipHours table (which I enter weekly hour readings) I need to get the last value entered (Max in the Date column), which would be the last time I entered a value, and then use the EquipHours.Hours value to select only the tasks in pmtask table that the pmtask.NextPerfHours.value is less than or equal to the value from the last entered EquipHours table reading.

I hope this makes sense. I am trying to see if there is a way to do all this in one query.Thanks for any help you can provide.Stacy

Thanks jimf for your response. You are good and the query is very close to what I need. I changed it a little because I was working on two different queries and in this one I didn't need the NextPerfDate because with it using the Hours it doesn't have a nextperfdate yet it only has a nextperfhours . This is what I have right now and it is getting the last entered hours from the equiphours table for each pmtask equipment. But how do I get it to only select the those pmtasks fields when the pmtask.NextPerfHours.Value is greater than or equal to that Equiphours.Hours.value we are getting from the Row_Number() OVER etc...I really appreciate your help with this. StacyP.S. I also learn something everyday that somebody else already knew!

One more question, I know I sound like a dope but I'm trying to learn this stuff. If I wanted to do an update on the records that are selected from this query - Do I have to put these results in a table so I can update the pmtask.NextPerfDate to Today's date?

Thanks again,Stacy

P.S. I think I got this figured out - although it doesn't show the records that are updated so I'm not for sure but would it look something like this?