Error "Operation must use an updateable query"

[kissybean]

Hi,

I am trying to do an update query and getting the message "Operation must use
an updateable query"

I'm running the following SQL:

UPDATE Quarters2 set [Admits 1000] = (([Admits]*12000)/(select Sum([Members])
from [Member Data] where [Member Data].[LOB2] = [Quarters2].[Product Line
Code] and [Member Data].[Year] = [Quarters2].[Service Year] and [Member Data]
..[State] = [Quarters2].[Employer Situs]))
WHERE Quarters2.[Qtr] = 'Y' ;

From preliminary research I am reading that I can't use aggregate functions
in the update query. Some people say to use the Domain functions like DSum ..
.. can I do that? if so, the last parameter of the function is conditions...
can I have more than one? I have several where & and statements in the
subquery.

Any help how to re-organize this into a working query would be great!

Thanks!

 

[Duane Hookom]

Try something like this which has crlf added for readability and assumes LOB2
and State are text and Year is numeric:

UPDATE Quarters2 set [Admits 1000] =
([Admits]*12000)/
DSum("[Members]", "[Member Data]",
"[LOB2] = '" & [Product Line Code] &
"' and [Year] = " & [Service Year] &
" and [State] = '" & [Employer Situs] & "'")
WHERE Quarters2.[Qtr] = 'Y' ;

 

[kissybean via AccessMonster.com]

Thanks, your syntax is perfect however, it is still not resolving the
variable name. If I manually put say 2005 as the year it works fine... any
ideas why it isn't resolving??

Try something like this which has crlf added for readability and assumes LOB2
and State are text and Year is numeric:

UPDATE Quarters2 set [Admits 1000] =
([Admits]*12000)/
DSum("[Members]", "[Member Data]",
"[LOB2] = '" & [Product Line Code] &
"' and [Year] = " & [Service Year] &
" and [State] = '" & [Employer Situs] & "'")
WHERE Quarters2.[Qtr] = 'Y' ;

Hi,

[quoted text clipped - 18 lines]

Thanks!

 

[Duane Hookom]

Resolve which "variable name"?
--
Duane Hookom
Microsoft Access MVP

Thanks, your syntax is perfect however, it is still not resolving the
variable name. If I manually put say 2005 as the year it works fine... any
ideas why it isn't resolving??

Try something like this which has crlf added for readability and assumes LOB2
and State are text and Year is numeric:

UPDATE Quarters2 set [Admits 1000] =
([Admits]*12000)/
DSum("[Members]", "[Member Data]",
"[LOB2] = '" & [Product Line Code] &
"' and [Year] = " & [Service Year] &
" and [State] = '" & [Employer Situs] & "'")
WHERE Quarters2.[Qtr] = 'Y' ;

Hi,

[quoted text clipped - 18 lines]

Thanks!

 

[kissybean via AccessMonster.com]

Resolve which "variable name"?

Thanks, your syntax is perfect however, it is still not resolving the
variable name. If I manually put say 2005 as the year it works fine... any

[quoted text clipped - 15 lines]

in the conditions part:

"[LOB2] = '" & [Product Line Code] &
"' and [Year] = " & [Service Year] &
" and [State] = '" & [Employer Situs] & "'")

Product Line Code, Service Year and Employer Situs are not resolving to their
respective values.

ie. Service Year is supposed to be 2005.... if I replace [Service Year] with
'2005' it works fine. So I get the parameter box coming up for all three
variables because it doesn't know what their values are.

Any help is much appreciated!

Thanks!

 

[Duane Hookom]

Your original post included " ...[Quarters2].[Product Line Code]... " which
led me to believe these fields were all in the Quarters2 table/query.

Where do you expect the values to come from?

--
Duane Hookom
Microsoft Access MVP

Resolve which "variable name"?

Thanks, your syntax is perfect however, it is still not resolving the
variable name. If I manually put say 2005 as the year it works fine... any

[quoted text clipped - 15 lines]

Thanks!

in the conditions part:

"[LOB2] = '" & [Product Line Code] &
"' and [Year] = " & [Service Year] &
" and [State] = '" & [Employer Situs] & "'")

Product Line Code, Service Year and Employer Situs are not resolving to their
respective values.

ie. Service Year is supposed to be 2005.... if I replace [Service Year] with
'2005' it works fine. So I get the parameter box coming up for all three
variables because it doesn't know what their values are.

Any help is much appreciated!

Thanks!

 

[kissybean via AccessMonster.com]

yes that is correct: the two tables involved are quarters2 (which is the one
being updated) and Member_Data which is what the Dsum is operating off of.

Is it maybe because I'm trying to have conditions come from quarters2 which
is not named in the table section of the dsum function?

i.e. Dsum (field, table, condition) mine is using member_data as the table
but trying to "join" with values coming from quarters2....

I dunno, I have no idea......

Your original post included " ...[Quarters2].[Product Line Code]... " which
led me to believe these fields were all in the Quarters2 table/query.

Where do you expect the values to come from?
[quoted text clipped - 19 lines]

Thanks!

 

[Duane Hookom]

Maybe you should start by explaining your table structures and what you are
attempting to accomplish.
--
Duane Hookom
Microsoft Access MVP

yes that is correct: the two tables involved are quarters2 (which is the one
being updated) and Member_Data which is what the Dsum is operating off of.

Is it maybe because I'm trying to have conditions come from quarters2 which
is not named in the table section of the dsum function?

i.e. Dsum (field, table, condition) mine is using member_data as the table
but trying to "join" with values coming from quarters2....

I dunno, I have no idea......

Your original post included " ...[Quarters2].[Product Line Code]... " which
led me to believe these fields were all in the Quarters2 table/query.

Where do you expect the values to come from?

Resolve which "variable name"?
Thanks, your syntax is perfect however, it is still not resolving the

[quoted text clipped - 19 lines]

Thanks!