Update Query Problem

[Nick Bradbury]

Hi Everyone

I am having difficulty in creating a query that will update the status of
an order on completion of all the order details, I have pasted the SQL
below. What I am trying to achieve is that the status of the LabOrder is
changed from "Received" to "Testing Complete" once all the LabSamples in a
particular LabOrder are completed. I have made the query below to display
the LabOrders that have all the LabSamples completed but I am unable to use
this to perform an Update query, is there something else I should be doing.



SELECT tblLabSamples.LabNo, tblLabOrders.NoOfSamples, tblLabSamples.Status,
Count(tblLabSamples.Status) AS SumComp
FROM tblLabOrders INNER JOIN tblLabSamples ON tblLabOrders.LabNo =
tblLabSamples.LabNo
GROUP BY tblLabSamples.LabNo, tblLabOrders.NoOfSamples, tblLabSamples.Status
HAVING (((tblLabSamples.Status)="Completed"));


Many Thanks

Nick

 

[John Spencer]

You can do this all in one query or in three separate queries

One complex query using two subqueries
UPDATE tblLabOrders
SET tblLabOrders.Status = "Testing Complete"
WHERE tblLabOrders.LabNo IN
(SELECT X.LabNo
FROM tblLabSamples as X LEFT JOIN

(SELECT Tmp.LabNo
FROM TblLabSamples as Tmp
WHERE tmp.Status <> "Completed") as Y

ON X.LabNo = Y.LabNo
WHERE Y.LabNO is Null)

Three separate queries.
First query saved as qIncomplete. Get any labSample record that is NOT
complete
SELECT LabNo
FROM TblLabSamples
WHERE Status <> "Completed"

Second query saved as qAllComplete. Identify Laborders that are all
complete (no status other than completed)
SELECT tblLabOrders.LabNo
FROM tblLabOrders LEFT JOIN qIncomplete
ON tblLabOrders.LabNo = qIncomplete.LabNo
WHERE qIncomplete.LabNo is Null

Third query
UPDATE tblLabOrders
SET tblLabOrders.Status = "Testing Complete"
WHERE tblLabOrders.LabNo IN
(SELECT LabNo FROM qAllComplete)

 

[Jeff Boyce]

Nick

What happens if you start a new query, based on the query you use to find
"Completed"? I'm imagining a query that joins the underlying table to the
first query on the LabNo field, then updates the table's status...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John Spencer]

Jeff,

Since the OP's original query was an aggregate query it can't be used in an
update query (except in a subquery in the where clause). If it is used
anywhere else the query becomes not updatable.

 

[Nick Bradbury]

Hi John

Thanks for that it works fine.

Nick

You can do this all in one query or in three separate queries

One complex query using two subqueries
UPDATE tblLabOrders
SET tblLabOrders.Status = "Testing Complete"
WHERE tblLabOrders.LabNo IN
(SELECT X.LabNo
FROM tblLabSamples as X LEFT JOIN

(SELECT Tmp.LabNo
FROM TblLabSamples as Tmp
WHERE tmp.Status <> "Completed") as Y

ON X.LabNo = Y.LabNo
WHERE Y.LabNO is Null)

Three separate queries.
First query saved as qIncomplete. Get any labSample record that is NOT
complete
SELECT LabNo
FROM TblLabSamples
WHERE Status <> "Completed"

Second query saved as qAllComplete. Identify Laborders that are all
complete (no status other than completed)
SELECT tblLabOrders.LabNo
FROM tblLabOrders LEFT JOIN qIncomplete
ON tblLabOrders.LabNo = qIncomplete.LabNo
WHERE qIncomplete.LabNo is Null

Third query
UPDATE tblLabOrders
SET tblLabOrders.Status = "Testing Complete"
WHERE tblLabOrders.LabNo IN
(SELECT LabNo FROM qAllComplete)

 

[Jeff Boyce]

Oh! That explains why I've run into the non-updateable in that
circumstance! Thanks!

Jeff