Update query

I am a novice at Access: I have an update query that uses two parameters,
which I get the user to supply using a PARAMETERS statement at the start of
the query. The value from one of these has to be obtained by running another
query (which does a SUM function, so it returns a single row with a single
column, ie just one value) , and this seems a bit naff. The final SQL would
look at bit like:
UPDATE myTable SET field1=[User value], field2=(SELECT SUM(field3) FROM
myOtherTable) WHERE ...
but this does not work with the embedded query. Can I use the anything in
Access to automate this? (Oh, and I still use Access97: it does the job, so
we haven't upgraded to anything more modern)

Peter,

There are more than one approach to this. One way is...
UPDATE myTable SET field1=[User value],
field2=DSum("[field3]","myOtherTable")

--
Steve Schapel, Microsoft Access MVP


peter j wrote:
I am a novice at Access: I have an update query that uses two parameters,
which I get the user to supply using a PARAMETERS statement at the start of
the query. The value from one of these has to be obtained by running another
query (which does a SUM function, so it returns a single row with a single
column, ie just one value) , and this seems a bit naff. The final SQL would
look at bit like:
UPDATE myTable SET field1=[User value], field2=(SELECT SUM(field3) FROM
myOtherTable) WHERE ...
but this does not work with the embedded query. Can I use the anything in
Access to automate this? (Oh, and I still use Access97: it does the job, so
we haven't upgraded to anything more modern)