operation must use an updatable query: part two

[Guest]

Hello,
few days ago I have posted a message with the same subject. My code was a
bit different from this one and the answer was that I received the err
message "Operation must use an updatable query" because I was using aggregate
function in my subqueries.
I have changed the design of the main query and the code is now:
UPDATE DSPC_PATIENT
SET BLDATE=(SELECT BLDATE FROM [ME DATES] WHERE [ME DATES].ID=1)
WHERE DSPC_PATIENT.ID=1

well... I still receive the err message!!!!!!!!!!! Why?
In [ME DATES] there is one and only one record for any ID.

Thanks,
Rocco

 

[Pat Hartman\(MVP\)]

Sounds like [Me DATES] is a query that uses aggregate functions. Even
though Jet could technically perform the requested update, it won't do it if
ANY part of the query is not updatable.

Looks like you are trying to capture some piece of information from a
many-side table so you can store it in a one-side table. This is not
advised. Every time the many-side table changes, you would have to be sure
to update the one-side table or the data in the one-side table would be
invalid. It is good practice to always use a query to obtain this data
rather than duplicating it.

 

[John Spencer]

Access does not care that there is only one record returned. It only knows
that there is the possibility of multiple records being returned. Since it
knows there is the possibility, it refuses to make the attempt.

You will probably have to resort to using the DLookup function.

UPDATE DSPC_PATIENT
SET BLDATE= DLookup(("BLDATE","[ME DATES]","ID=1")
WHERE DSPC_PATIENT.ID=1

 

[Guest]

Thanks, but unfortunately you haven’t hit the point: [ME DATE] is a table.
Well, let me better explain all this mess...
The table DSPC_PATIENT should store information about dates on which
patients respond to medical treatment.
Those dates are not stored in any of the tables of the database. You have to
calculate them through a query. Here it is:
SELECT ID, MIN(CDATE)
FROM [DM_TOTHAM17 AT ALL VISITS]
WHERE TOTHAM_17ITEMS<=50*(SELECT TOTHAM17 FROM [DM_HAM AT BAS] WHERE [DM_HAM
AT BAS].ID= [DM_TOTHAM17 AT ALL VISITS].ID)\100
GROUP BY ID;

This query works fine: I have tested it.

Since this query will use aggregate function, it cannot be used as subquery
in the update statement or I get the awful message you know.

So I moved around by creating a table - the [ME DATE] - from the query:
SELECT ID, MIN(CDATE) INTO [ME DATE]
FROM [DM_TOTHAM17 AT ALL VISITS]
WHERE TOTHAM_17ITEMS<=50*(SELECT TOTHAM17 FROM [DM_HAM AT BAS] WHERE [DM_HAM
AT BAS].ID= [DM_TOTHAM17 AT ALL VISITS].ID)\100
GROUP BY ID;

This table will have one record for each patient. And I’m using this table
in my update statement. No aggregate function at all…. although JET is so
smart to *understand* that this table comes from something which will use
one. And I really don’t think it can happen…

Hope things are clearer know.

Sounds like [Me DATES] is a query that uses aggregate functions. Even
though Jet could technically perform the requested update, it won't do it if
ANY part of the query is not updatable.

Looks like you are trying to capture some piece of information from a
many-side table so you can store it in a one-side table. This is not
advised. Every time the many-side table changes, you would have to be sure
to update the one-side table or the data in the one-side table would be
invalid. It is good practice to always use a query to obtain this data
rather than duplicating it.

Hello,
few days ago I have posted a message with the same subject. My code was a
bit different from this one and the answer was that I received the err
message "Operation must use an updatable query" because I was using
aggregate
function in my subqueries.
I have changed the design of the main query and the code is now:
UPDATE DSPC_PATIENT
SET BLDATE=(SELECT BLDATE FROM [ME DATES] WHERE [ME DATES].ID=1)
WHERE DSPC_PATIENT.ID=1

well... I still receive the err message!!!!!!!!!!! Why?
In [ME DATES] there is one and only one record for any ID.

Thanks,
Rocco

 

[[MVP] S.Clark]

It's just one of those Access things. It just cain't do it.

I work around this by writing the data to a table, then base the update
query on the table.

 

[Guest]

I have done this and it doesn't work!?
Please read my reply to Pat Hartman.

It's just one of those Access things. It just cain't do it.

I work around this by writing the data to a table, then base the update
query on the table.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting

Hello,
few days ago I have posted a message with the same subject. My code was a
bit different from this one and the answer was that I received the err
message "Operation must use an updatable query" because I was using
aggregate
function in my subqueries.
I have changed the design of the main query and the code is now:
UPDATE DSPC_PATIENT
SET BLDATE=(SELECT BLDATE FROM [ME DATES] WHERE [ME DATES].ID=1)
WHERE DSPC_PATIENT.ID=1

well... I still receive the err message!!!!!!!!!!! Why?
In [ME DATES] there is one and only one record for any ID.

Thanks,
Rocco

 

[Guest]

thanks,
Are you saying that you can never use a SELECT stament as subquery for an
UPDATE statment?

Access does not care that there is only one record returned. It only knows
that there is the possibility of multiple records being returned. Since it
knows there is the possibility, it refuses to make the attempt.

You will probably have to resort to using the DLookup function.

UPDATE DSPC_PATIENT
SET BLDATE= DLookup(("BLDATE","[ME DATES]","ID=1")
WHERE DSPC_PATIENT.ID=1

Hello,
few days ago I have posted a message with the same subject. My code was a
bit different from this one and the answer was that I received the err
message "Operation must use an updatable query" because I was using
aggregate
function in my subqueries.
I have changed the design of the main query and the code is now:
UPDATE DSPC_PATIENT
SET BLDATE=(SELECT BLDATE FROM [ME DATES] WHERE [ME DATES].ID=1)
WHERE DSPC_PATIENT.ID=1

well... I still receive the err message!!!!!!!!!!! Why?
In [ME DATES] there is one and only one record for any ID.

Thanks,
Rocco

 

[John Spencer]

Yes, that is true in Access as far as I know..

In MS SQL Server, I believe that you can as I seem to recall that MS SQL
will make the attempt and if zero (null) or one record is returned it will
succeed. If multiple records are returned, then it generates an error.

As I said, I think this is the case of MS SQL.

thanks,
Are you saying that you can never use a SELECT stament as subquery for an
UPDATE statment?

Access does not care that there is only one record returned. It only
knows
that there is the possibility of multiple records being returned. Since
it
knows there is the possibility, it refuses to make the attempt.

You will probably have to resort to using the DLookup function.

UPDATE DSPC_PATIENT
SET BLDATE= DLookup(("BLDATE","[ME DATES]","ID=1")
WHERE DSPC_PATIENT.ID=1

Hello,
few days ago I have posted a message with the same subject. My code was
a
bit different from this one and the answer was that I received the err
message "Operation must use an updatable query" because I was using
aggregate
function in my subqueries.
I have changed the design of the main query and the code is now:
UPDATE DSPC_PATIENT
SET BLDATE=(SELECT BLDATE FROM [ME DATES] WHERE [ME DATES].ID=1)
WHERE DSPC_PATIENT.ID=1

well... I still receive the err message!!!!!!!!!!! Why?
In [ME DATES] there is one and only one record for any ID.

Thanks,
Rocco

 

[John Spencer]

By the way, reading a little further in this thread...

You may be able to do what you want by joining your two tables together and
then updating the BLDate field

UPDATE DSPC_PATIENT INNER JOIN [ME DATES]
ON DSPC_PATIENT.ID = [ME DATES].ID
SET DSPC_PATIENT.BLDate=[ME Dates].BLDate
WHERE << enter your criteria here if needed >>

thanks,
Are you saying that you can never use a SELECT stament as subquery for an
UPDATE statment?

Access does not care that there is only one record returned. It only
knows
that there is the possibility of multiple records being returned. Since
it
knows there is the possibility, it refuses to make the attempt.

You will probably have to resort to using the DLookup function.

UPDATE DSPC_PATIENT
SET BLDATE= DLookup(("BLDATE","[ME DATES]","ID=1")
WHERE DSPC_PATIENT.ID=1

Hello,
few days ago I have posted a message with the same subject. My code was
a
bit different from this one and the answer was that I received the err
message "Operation must use an updatable query" because I was using
aggregate
function in my subqueries.
I have changed the design of the main query and the code is now:
UPDATE DSPC_PATIENT
SET BLDATE=(SELECT BLDATE FROM [ME DATES] WHERE [ME DATES].ID=1)
WHERE DSPC_PATIENT.ID=1

well... I still receive the err message!!!!!!!!!!! Why?
In [ME DATES] there is one and only one record for any ID.

Thanks,
Rocco

 

[Guest]

this works!!!!!!
were did you find it?

there is always something new!!

thanks!!!

By the way, reading a little further in this thread...

You may be able to do what you want by joining your two tables together and
then updating the BLDate field

UPDATE DSPC_PATIENT INNER JOIN [ME DATES]
ON DSPC_PATIENT.ID = [ME DATES].ID
SET DSPC_PATIENT.BLDate=[ME Dates].BLDate
WHERE << enter your criteria here if needed >>

thanks,
Are you saying that you can never use a SELECT stament as subquery for an
UPDATE statment?

Access does not care that there is only one record returned. It only
knows
that there is the possibility of multiple records being returned. Since
it
knows there is the possibility, it refuses to make the attempt.

You will probably have to resort to using the DLookup function.

UPDATE DSPC_PATIENT
SET BLDATE= DLookup(("BLDATE","[ME DATES]","ID=1")
WHERE DSPC_PATIENT.ID=1

Hello,
few days ago I have posted a message with the same subject. My code was
a
bit different from this one and the answer was that I received the err
message "Operation must use an updatable query" because I was using
aggregate
function in my subqueries.
I have changed the design of the main query and the code is now:
UPDATE DSPC_PATIENT
SET BLDATE=(SELECT BLDATE FROM [ME DATES] WHERE [ME DATES].ID=1)
WHERE DSPC_PATIENT.ID=1

well... I still receive the err message!!!!!!!!!!! Why?
In [ME DATES] there is one and only one record for any ID.

Thanks,
Rocco

 

[John Spencer]

You are welcome.

I have no idea where I found it. Experience, I guess.

Probably, I had constructed a Select query (with the query grid) to show me
the values I wanted in and then converted that to an update query using the
menu and then set the fields to update. I looked at the SQL created by
Access and was able to determine the structure it generated.

Access's query generator (using the query grid) can often do what I need the
first time. I've used it to save typing and as a learning tool. It is a
bit obsessive about parentheses and brackets and fully qualified fields
([TableName].[FieldName]), but after a while you learn what is needed and
what is not needed and why.

this works!!!!!!
were did you find it?

there is always something new!!

thanks!!!

By the way, reading a little further in this thread...

You may be able to do what you want by joining your two tables together
and
then updating the BLDate field

UPDATE DSPC_PATIENT INNER JOIN [ME DATES]
ON DSPC_PATIENT.ID = [ME DATES].ID
SET DSPC_PATIENT.BLDate=[ME Dates].BLDate
WHERE << enter your criteria here if needed >>

thanks,
Are you saying that you can never use a SELECT stament as subquery for
an
UPDATE statment?


:

Access does not care that there is only one record returned. It only
knows
that there is the possibility of multiple records being returned.
Since
it
knows there is the possibility, it refuses to make the attempt.

You will probably have to resort to using the DLookup function.

UPDATE DSPC_PATIENT
SET BLDATE= DLookup(("BLDATE","[ME DATES]","ID=1")
WHERE DSPC_PATIENT.ID=1

Hello,
few days ago I have posted a message with the same subject. My code
was
a
bit different from this one and the answer was that I received the
err
message "Operation must use an updatable query" because I was using
aggregate
function in my subqueries.
I have changed the design of the main query and the code is now:
UPDATE DSPC_PATIENT
SET BLDATE=(SELECT BLDATE FROM [ME DATES] WHERE [ME DATES].ID=1)
WHERE DSPC_PATIENT.ID=1

well... I still receive the err message!!!!!!!!!!! Why?
In [ME DATES] there is one and only one record for any ID.

Thanks,
Rocco