operation must use updatable query (?!)

[rocco]

Hello I need to update a filed in a table with values retrived from a
calculation made on values from others queries. Here is the code:
UPDATE DSPC_PATIENT
SET DSPC_PATIENT.RESPDATE=(SELECT MIN(CDATE)FROM [DM_TOTHAM17 AT ALL VISITS]
WHERE ID= 1 AND TOTHAM_17ITEMS<=50*(SELECT TOTHAM17 FROM [DM_HAM AT BAS]
WHERE [DM_HAM AT BAS].ID=1)\100)
WHERE DSPC_PATIENT.ID=1

The subquery (SELECT MIN(CDATE)FROM [DM_TOTHAM17 AT ALL VISITS] WHERE ID= 1
AND TOTHAM_17ITEMS<=50*(SELECT TOTHAM17 FROM [DM_HAM AT BAS] WHERE [DM_HAM AT
BAS].ID=1)\100) works fine: it gives back the right value. I have tested it
alone.

The filed DSPC_PATIENT.RESPDATE is not the main field in a one-to-many
relation. It isn't in any relation. it a very basic field: just a date.

Why do I receive the err message posted in subject once I run the code above ?

Thanks
Rocco

 

[Michel Walsh]

Hi,


Your UPDATE query implies an aggregate, from the subquery. Generally, that
is updateable, but JET goes by excess (of ... feature...) and marks the
query as not updateable.


Hoping it may help,
Vanderghast, Access MVP

 

[rocco]

nice...
do you know if it will be the same MSDE?

Hi,


Your UPDATE query implies an aggregate, from the subquery. Generally, that
is updateable, but JET goes by excess (of ... feature...) and marks the
query as not updateable.


Hoping it may help,
Vanderghast, Access MVP

Hello I need to update a filed in a table with values retrived from a
calculation made on values from others queries. Here is the code:
UPDATE DSPC_PATIENT
SET DSPC_PATIENT.RESPDATE=(SELECT MIN(CDATE)FROM [DM_TOTHAM17 AT ALL
VISITS]
WHERE ID= 1 AND TOTHAM_17ITEMS<=50*(SELECT TOTHAM17 FROM [DM_HAM AT BAS]
WHERE [DM_HAM AT BAS].ID=1)\100)
WHERE DSPC_PATIENT.ID=1

The subquery (SELECT MIN(CDATE)FROM [DM_TOTHAM17 AT ALL VISITS] WHERE ID=
1
AND TOTHAM_17ITEMS<=50*(SELECT TOTHAM17 FROM [DM_HAM AT BAS] WHERE [DM_HAM
AT
BAS].ID=1)\100) works fine: it gives back the right value. I have tested
it
alone.

The filed DSPC_PATIENT.RESPDATE is not the main field in a one-to-many
relation. It isn't in any relation. it a very basic field: just a date.

Why do I receive the err message posted in subject once I run the code
above ?

Thanks
Rocco

 

[Michel Walsh]

Hi,



Should work in MS SQL Server.


In Jet, a workaround is to use DMIN().



Hoping it may help,
Vanderghast, Access MVP

nice...
do you know if it will be the same MSDE?

Hi,


Your UPDATE query implies an aggregate, from the subquery. Generally,
that
is updateable, but JET goes by excess (of ... feature...) and marks the
query as not updateable.


Hoping it may help,
Vanderghast, Access MVP

Hello I need to update a filed in a table with values retrived from a
calculation made on values from others queries. Here is the code:
UPDATE DSPC_PATIENT
SET DSPC_PATIENT.RESPDATE=(SELECT MIN(CDATE)FROM [DM_TOTHAM17 AT ALL
VISITS]
WHERE ID= 1 AND TOTHAM_17ITEMS<=50*(SELECT TOTHAM17 FROM [DM_HAM AT
BAS]
WHERE [DM_HAM AT BAS].ID=1)\100)
WHERE DSPC_PATIENT.ID=1

The subquery (SELECT MIN(CDATE)FROM [DM_TOTHAM17 AT ALL VISITS] WHERE
ID=
1
AND TOTHAM_17ITEMS<=50*(SELECT TOTHAM17 FROM [DM_HAM AT BAS] WHERE
[DM_HAM
AT
BAS].ID=1)\100) works fine: it gives back the right value. I have
tested
it
alone.

The filed DSPC_PATIENT.RESPDATE is not the main field in a one-to-many
relation. It isn't in any relation. it a very basic field: just a
date.

Why do I receive the err message posted in subject once I run the code
above ?

Thanks
Rocco