UPDATE Query

  • Thread starter Thread starter Darren Line
  • Start date Start date
D

Darren Line

Hi, I was wondering if you could help me with this query.

I have created the following query...

SELECT Sum(SalesDetailActual.QtyAlloc) AS SumOfQtyAlloc,
SalesDetailActual.ProdRecNo
FROM SalesDetailActual INNER JOIN SalesOrderH ON SalesDetailActual.SoRecNo =
SalesOrderH.SoRecNo
WHERE (((SalesOrderH.SoStatus)<>2) AND ((SalesOrderH.SoType)="S"))
GROUP BY SalesDetailActual.Product
HAVING (((Sum(SalesDetailActual.QtyAlloc))<>0));

The result is just a list of product record numbers and a value next to it.

I would like to use these values to update a field in another table.

The field I would like to update is called "Alloc" in the table "Products"
(ProdRecNo is the key in Products table and also in the query above).

I am just unsure if its possible to actually do this in one command, or I
have to write some vbscript to loop around the query and then do individual
updates per prodrecno.

Any assistance would be much appreciated.

TIA

Darren.
 
Darren

One approach would be to "chain" together your queries. First, the query
you already have derives the values you want to use to update with. The
second part would be to write a second query that uses the first query (as
if it were a table) and performs the update.

You'd only need to run the second query directly, as it would run the first
to get its values.
 
Jeff

That was the line I was taking to solve Darren's problem
but when I simulated it, I got the error message 'Operation
must use an Updateable Query'. The same error message
appeared when I ran it with the first query 'embedded' in
the second and agin when I had saved the first query. Any
ideas why?

Gerald Stanley MCSD
 
Gerald

Not clear in which query you got the message.

Is there a chance that the fields included in the second query for updating
are actually the fields from the first query? I could imagine that Access
would look at that situation and say "I can't update those -- they're based
on a GROUP BY query!"
 
Jeff

The query is
UPDATE Products INNER JOIN SumQuery ON Products.prodRecNo =
SumQuery.ProdRecNo SET Products.alloc =
[SumQuery].[SumOfQtyAlloc];

SumQuery is Darren's original SQL saved as a query. Given
that the Products table does not feature in that query, I
find the error message a little strange.

Gerald Stanley MCSD
 
Gerald

It might be (nope, it really is!) a bit of a kludge, but one approach would
be to create a temp table (make table) with the results of the first query,
then update using that temp table. (with apologies to those seeking an
"elegant" solution)
 
Back
Top