Subquery creating data used in an Update...

  • Thread starter Thread starter RzB
  • Start date Start date
R

RzB

Can I use a subquery to create a row of
data that can be used as data in an UPDATE.

I have a select something like...

SELECT (some expressione) AS Exp1
FROM ..........
WHERE..........

Now I want to use this in an UPDATE such
that I use the value Exp1 ...

UPDATE tblSomeTabe
SET tblSomeTabe.Fld1 = Exp1
WHERE tblSomeTabe.Fld2 = xx

Can I do this? Where does the SELECT go?

Many thanks,
Roy
 
Look up Update query in the help.

Is there a relationship between the table and the query?

If not, does the expression return a single value? And if so, does the subquery
use any aggregate functions. You cannot update based on an aggregate function in
Access (using JET SQL). You can use the aggregate functions to do this.

In other words, we need more detail about what you are doing.
 
John,
Many thanks for your assistance

I have looked in the Help, the Access & SQL books I have,
and back in the archives of this NG, but still can't work
out how to do this.

Below is a link to the Access SQL design window. Hopefully
it will help to explain what I'm trying to do. This SELECT
generates a value Exp1A that I want to use in an UPDATE.

Hopefully this is fairly self explanitory..
The file size is 28K.....

http://pix.gillandroy.com/accessprobs/QueryMulti01.jpg

this generates
-----------
SELECT
((Sum([Month01])-1)*IIf([IsAvailableMonth01],1,0))
*IIf([AllowInWedgeY1],1,0) AS EXP1,
IIf(0>[Exp1],0,[Exp1]) AS Exp1A

FROM tblResources INNER JOIN
(tblProjects INNER JOIN tblPrjLinesTask
ON tblProjects.ID = tblPrjLinesTask.ProjectID)
ON tblResources.ID = tblPrjLinesTask.ResourceID

GROUP BY tblResources.ID, tblResources.AllowInWedgeY1,
tblResources.IsAvailableMonth01,
tblProjects.WedgeProject

HAVING (((tblResources.ID)=441) AND
((tblProjects.WedgeProject)=False));
---------------------

What I want to be able to do is to plug the Exp1A
back into tblPrjLinesTasks.Month01 for a particular
ProjectID / ResourceID..

Something like...
------------
UPDATE tblPrjLinesTasks SET Month01 = Exp1A
WHERE tblPrjLinesTasks.ProjectID=99
AND tblPrjLinesTasks.ResourceID = 204
------------

BTW - Exp1A is there so that any negative values
generated by Exp1 are set to zero. I couldn't
see how to do that in the Exp1 expression...

Many thanks,
Roy
 
John,
Hmm... I've given up with this..
I tried the following...

UPDATE tblSomeTable
SET tblSomeTable.Fld1 = (SELECT ...)
WHERE tblSomeTable.Fld2 = xx

Where the SELECT generated one field.

I got an error saying
"Operation must use an updateable query"

The SELECT is fairly complex and draws
on data from 3 tables and uses a SUM.
So I guess that's what is stopping it from working.

I don't quite understand what you mean by -
"You can use the aggregate functions to do this"
did you mean "can't" or am I missing something ?

What I plan to do is to generate the data
with the SELECT, extract it via a Recordset
in VBA and then run the UPDATE. Pity
because I wanted the best performance for
this function.

Anyway many thanks for your help,
Roy
 
Back
Top