Update Query Problem

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

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)


"Nick Bradbury" wrote in message
...
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

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
http://mvp.support.microsoft.com/

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

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


"Nick Bradbury" wrote in message
...
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

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.


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
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
http://mvp.support.microsoft.com/

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

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


"Nick Bradbury" wrote in message
...
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

Hi John

Thanks for that it works fine.

Nick




"John Spencer" wrote in message
...
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)


"Nick Bradbury" wrote in message
...
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

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

Jeff

"John Spencer" wrote in message
...
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.


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
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
http://mvp.support.microsoft.com/

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

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


"Nick Bradbury" wrote in message
...
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