Update Query Problem 1

[papic1972]

Hi all,

I have the following code in a select query:

SELECT qryJobswithNoDeliveriesintheLast3months.JobNo, Max(qryJobswithNoDeliveriesintheLast3months.ActualDate) AS MaxOfActualDate, tblJob.JobId, tblJob.Customer, tblJob.ContactName, tblJob.ContactNo1, tblJob.StreetAddress, tblJob.Suburb, tblJob.CompletedJob
FROM tblJob RIGHT JOIN qryJobswithNoDeliveriesintheLast3months ON tblJob.JobId = qryJobswithNoDeliveriesintheLast3months.JobNo
GROUP BY qryJobswithNoDeliveriesintheLast3months.JobNo, tblJob.JobId, tblJob.Customer, tblJob.ContactName, tblJob.ContactNo1, tblJob.StreetAddress, tblJob.Suburb, tblJob.CompletedJob
HAVING (((Max(qryJobswithNoDeliveriesintheLast3months.ActualDate))<#6/30/2004#) AND ((tblJob.CompletedJob)=False));


I need to update the yes/no field "Completed Job" from "False" to "True" based on the criteria in the code. I have tried converting this query to an update query but it does not work properly. Am I doing this the correct way or is there some other way to approach this. Any help would be of benefit.

Thanks in advance.

 

[PHV]

Something like this ?
UPDATE tblJob SET CompletedJob=True
WHERE JobId In (SELECT DISTINCT JobNo FROM [query name of your posted SQL])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[papic1972]

hmmmm.....

I tried creating a new qry using your code PHV & 20 minutes later the qry was still running!!!!!

No luck with that one!

 

[PHV]

So, try a maketable first based on your posted query creating a temporary table named, say, tmpJobtbl and then:
UPDATE tblJob INNER JOIN tmpJobtbl ON tblJob.JobId = tmpJobtbl.JobNo
SET CompletedJob = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[papic1972]

Beautiful!!!!

That worked!

That deserves a star, how do I give you one?

 

[papic1972]

I've worked it out!!